Select statement inside a select statement

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)

Select statement inside a select statement

Author

Topic

phoeneous
Starting Member

9 Posts

Posted - 2009-10-21 : 14:01:27

Im having difficulty writing the syntax for this query. I know that I need to write a select statement within a select statement but management studio doesnt like the syntax.

This is the original query that does not have the nested select statement. I need to query all (case_pick.current_ind = 'Y' AND case_pick.group_code = 'CSTATU' AND group_items.description <> 'Dropped') that also meet the criteria of (case_pick.current_ind = 'Y' AND case_pick.group_code = 'JURISD' AND group_items.description = 'Nevada'). Any help is appreciated.


SELECT				vcases.style, vcases.lawtype_code, vcases.atty3_inits 
					,incident.acc_date AS DOL, incident.sol_date AS SOL, vcases.atty2_inits
					, vcases.atty1_inits, group_items.description

FROM				group_items LEFT OUTER JOIN
                    case_pick ON group_items.item_sk = case_pick.item_sk RIGHT OUTER JOIN
                    vcases ON case_pick.case_sk = vcases.case_sk LEFT OUTER JOIN
                    incident ON vcases.case_sk = incident.case_sk

WHERE				(vcases.closed_ind = 'O') AND (vcases.lawtype_code = 'PI'
					OR vcases.lawtype_code = 'PP') AND (vcases.OfficeName = 'Main Office:')
					AND (case_pick.current_ind = 'Y') AND (case_pick.group_code = 'CSTATU')
					AND (group_items.description <> 'Dropped')
                    AND ((DATEDIFF(day, GETDATE(), incident.sol_date) BETWEEN 45 AND 75)
					OR (DATEDIFF(day, GETDATE(), incident.sol_date) BETWEEN 90 AND 120))

ORDER BY			incident.sol_date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 14:11:38

quote:
Originally posted by phoeneous

Im having difficulty writing the syntax for this query. I know that I need to write a select statement within a select statement but management studio doesnt like the syntax.

This is the original query that does not have the nested select statement. I need to query all (case_pick.current_ind = 'Y' AND case_pick.group_code = 'CSTATU' AND group_items.description <> 'Dropped') that also meet the criteria of (case_pick.current_ind = 'Y' AND case_pick.group_code = 'JURISD' AND group_items.description = 'Nevada'). Any help is appreciated.

SELECT vcases.style, vcases.lawtype_code, vcases.atty3_inits
,incident.acc_date AS DOL, incident.sol_date AS SOL, vcases.atty2_inits
, vcases.atty1_inits, group_items.description

FROM group_items LEFT OUTER JOIN
case_pick ON group_items.item_sk = case_pick.item_sk RIGHT OUTER JOIN
vcases ON case_pick.case_sk = vcases.case_sk LEFT OUTER JOIN
incident ON vcases.case_sk = incident.case_sk

WHERE (vcases.closed_ind = 'O') AND (vcases.lawtype_code = 'PI'
OR vcases.lawtype_code = 'PP') AND (vcases.OfficeName = 'Main Office:')
AND (case_pick.current_ind = 'Y') AND
(((case_pick.group_code = 'CSTATU')
AND (group_items.description <> 'Dropped')) OR (((case_pick.group_code = 'JURISD')
AND (group_items.description = 'Nevada')))
AND ((DATEDIFF(day, GETDATE(), incident.sol_date) BETWEEN 45 AND 75)
OR (DATEDIFF(day, GETDATE(), incident.sol_date) BETWEEN 90 AND 120))

ORDER BY incident.sol_date

do you mean above?

phoeneous
Starting Member

9 Posts

Posted - 2009-10-21 : 14:32:16

I tried that but I keep getting Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'ORDER'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources