nested select - 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 2005 Forums

Transact-SQL (2005)

nested select

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-23 : 23:39:42

Hi guys, is the following possible to achive by 1 select statement instead of nested select statement?

SELECT	*
FROM	(
	SELECT	CASE WHEN ttype = 'PO' then (CASE WHEN in_date IS NULL then do_date else in_date) else do_date END date
	FROM	#Tempfun
	)a
WHERE	date > '20090101'

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-23 : 23:55:28

[code]
SELECT CASE WHEN ttype = 'PO' then (CASE WHEN in_date IS NULL then do_date else in_date) else do_date END date
FROM #Tempfun
WHERE CASE WHEN ttype = 'PO' then (CASE WHEN in_date IS NULL then do_date else in_date) else do_date END > '20090101'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 00:10:38

sifu...cache plan size bigger = query more slow?

Hope can help...but advise to wait pros with confirmation...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-24 : 00:12:23

[code]
SELECT CASE WHEN ttype = 'PO' then Coalesce(in_date,do_date) else do_date END date
FROM #Tempfun
WHERE CASE WHEN ttype = 'PO' then Coalesce(in_date,do_date) else do_date END > '20090101'
[/code]

[/quote]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 00:26:49

hmmm, i not sure where i read about this....nested select is slower than 1 select...but it seems to be nestes select faster than 1 select??

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 05:47:47

quote:
Originally posted by waterduck

Hi guys, is the following possible to achive by 1 select statement instead of nested select statement?
SELECT *
FROM (
SELECT CASE WHEN ttype = 'PO' then (CASE WHEN in_date IS NULL then do_date else in_date) else do_date END date
FROM #Tempfun
)a
WHERE date > '20090101'

Hope can help...but advise to wait pros with confirmation...

Use this method. This is simple and maintainable. You dont need to change the expression all the places it is used

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources