Why does this work??? - SQL Server Forums

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)

Why does this work???

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-22 : 07:40:54

Mattias writes "Thanks for an excellent site!
I usually don't do to much sql myself but I made this for a chart component. Anyway it works as intended, but how is it that you can have a reference to the temp table in the sub select ("AND CONVERT(VARCHAR(7), CreatedDate, 120) = #NewDocsStats.YearAndMonth"). I would rather join the tables or something but didn't know how to do that.


UPDATE 	#NewDocsStats 
SET	NumOfDocs = (	SELECT COUNT(*) FROM Information
			WHERE DATEDIFF( MONTH, CreatedDate, GETDATE() ) <= 36 
			AND CONVERT(VARCHAR(7), CreatedDate, 120) = #NewDocsStats.YearAndMonth )

..."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-22 : 07:55:07

A sub-query runs in the same process so it is within the scope of the temp table. Just as local variables and input parameters are available to the sub-query.

try this statement:
select CONVERT(VARCHAR(7), getdate(), 120)
it returns: 2005-02

if YearAndMonth column is in this format then you can have matching values.

Be One with the Optimizer
TG

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-22 : 11:26:55

It's called a corellated subquery.
If you want a join then

UPDATE #NewDocsStats
SET NumOfDocs = t2.cnt
from #NewDocsStats t
join
(select dte = CONVERT(VARCHAR(7), CreatedDate, 120), cnt = COUNT(*)
FROM Information
WHERE DATEDIFF( MONTH, CreatedDate, GETDATE() ) <= 36
group by CONVERT(VARCHAR(7), CreatedDate, 120)
) t2
on t2.dte = t.YearAndMonth

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-22 : 11:48:21

quote:
Originally posted by AskSQLTeam

Mattias writes "Thanks for an excellent site!
I usually don't do to much sql myself but I made this for a chart component. Anyway it works as intended, but how is it that you can have a reference to the temp table in the sub select ("AND CONVERT(VARCHAR(7), CreatedDate, 120) = #NewDocsStats.YearAndMonth"). I would rather join the tables or something but didn't know how to do that.

UPDATE #NewDocsStats
SET NumOfDocs = ( SELECT COUNT(*) FROM Information
WHERE DATEDIFF( MONTH, CreatedDate, GETDATE() ) <= 36
AND CONVERT(VARCHAR(7), CreatedDate, 120) = #NewDocsStats.YearAndMonth )

..."

But why would you want to complicate things with a join? Why "fix" what already works

?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources