Temp Table Query not returning results to page

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Temp Table Query not returning results to page

Author

Topic

rlull
Starting Member

39 Posts

Posted - 2005-11-14 : 15:03:18

I am using the following query from an ASP page:

select Orders.CustID
into #TempTable
from Orders
join Customers
on Orders.CustID = Customers.CustID
group by Orders.CustID
having count(Orders.CustID) = 1

select count(*) as NewCustomers
from #TempTable t
join Orders o
on t.CustID = o.CustID
where cast(convert(char(10), OrderDateTime, 112) as smalldatetime) = cast(convert(char(10), '11/10/2005', 112) as smalldatetime)

drop table #TempTable

When I run this in Query Analyzer it works fine and returns a column called "New Customers". However, when I run it from the ASP page or even as a stored procedure, the column is not available. Can anyone help me with this? Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-14 : 15:09:12

Why are you using a temp table in the first place?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rlull
Starting Member

39 Posts

Posted - 2005-11-14 : 15:11:41

I was unable to get a single query to accomplish this same task. I'm sure there's a better way to simplify it and would appreciate your suggestions on that as well.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-14 : 15:52:30

I don't know the answer to you asp/temp table issue but will this do it for you?


select	count(*) as newCustomers
from	orders
where	datediff(day,OrderDateTime,'11/10/2005') = 0
and	custid in 
		(select	custid
		from	orders
		group by custid
		having count(*) = 1)

Be One with the Optimizer
TG

rlull
Starting Member

39 Posts

Posted - 2005-11-14 : 16:06:59

Yes! Thank you very much. Optimizing the query like that allowed me to retrieve the data properly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources