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.
Author |
Topic |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-10-11 : 10:57:40
|
Hi AllGetting an error with the below Select statement and not sure how to fix it.I wanted to use this select to write a Stored procedure with a case statementcan someone point out what the problem is from the below querySELECT COUNT(*) AS Cnt FROM (SELECT RequestId from report_request where requestid in (select requestid from report_request where requestid in (Select requestid from selected_report where uwcontent = 'A' )and requestid in (SELECT distinct o.requestidFROM [dbo].[triage_check_list_item] oINNER JOIN (SELECT requestid FROM [dbo].[triage_check_list_item] WHERE triageitemid IN (1,2,3,4,5,7,8,11,15,16,19) GROUP BY requestid HAVING SUM(CASE WHEN needdt <> '1/1/1900' THEN 1 ELSE 0 END) =0 )rON r.requestid = o.requestid)and reportfrequency = 'Recurring'and msgn is not null))Thanks |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-10-11 : 11:21:43
|
What's the error?JimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-11 : 15:36:59
|
the code is a real mess! I dont know you're trying to achieve with so many levels of nesting but it should be as followsSELECT COUNT(*) AS Cnt FROM ( SELECT RequestId from report_request where requestid in ( select requestid from report_request where requestid in ( Select requestid from selected_report where uwcontent = 'A' and requestid in ( SELECT distinct o.requestid FROM [dbo].[triage_check_list_item] o INNER JOIN (SELECT requestid FROM [dbo].[triage_check_list_item] WHERE triageitemid IN (1,2,3,4,5,7,8,11,15,16,19) GROUP BY requestid HAVING SUM(CASE WHEN needdt <> '1/1/1900' THEN 1 ELSE 0 END) =0 )r ON r.requestid = o.requestid and reportfrequency = 'Recurring' and msgn is not null ) ) ) )t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-10-12 : 08:56:50
|
Thank You Visakh for the help.what iam trying to acheive is to check if there is a record that exists in the database from a webform.If it does exist then i would populate few form feilds.if it does not then i will not populate.Below is my SPThanks againCREATE PROCEDURE [dbo].[AttestConfirm](@RQID int)ASSelect Requestid = CASEWhen Cnt = 0 then 'N'else 'Y'ENDFROM (SELECT COUNT(*) AS Cnt FROM (SELECT RequestId from report_request where requestid in ( select requestid from report_request where requestid in ( Select requestid from selected_report where uwcontent = 'A' and requestid in ( SELECT distinct o.requestid FROM [dbo].[triage_check_list_item] o INNER JOIN (SELECT requestid FROM [dbo].[triage_check_list_item] WHERE triageitemid IN (1,2,3,4,5,7,8,11,15,16,19) GROUP BY requestid HAVING SUM(CASE WHEN needdt <> '1/1/1900' THEN 1 ELSE 0 END) =0 )r ON r.requestid = o.requestid and reportfrequency = 'Recurring' and msgn is not null ))))tWHERE (requestid = @RQID))t1Thanks againquote: Originally posted by visakh16 the code is a real mess! I dont know you're trying to achieve with so many levels of nesting but it should be as followsSELECT COUNT(*) AS Cnt FROM ( SELECT RequestId from report_request where requestid in ( select requestid from report_request where requestid in ( Select requestid from selected_report where uwcontent = 'A' and requestid in ( SELECT distinct o.requestid FROM [dbo].[triage_check_list_item] o INNER JOIN (SELECT requestid FROM [dbo].[triage_check_list_item] WHERE triageitemid IN (1,2,3,4,5,7,8,11,15,16,19) GROUP BY requestid HAVING SUM(CASE WHEN needdt <> '1/1/1900' THEN 1 ELSE 0 END) =0 )r ON r.requestid = o.requestid and reportfrequency = 'Recurring' and msgn is not null ) ) ) )t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-12 : 12:50:24
|
it looks like you've redundant checks nestedat least the below two..SELECT RequestId from report_request where requestid in (select requestid from report_request where requestid in (...looks like same check to me------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2012-10-15 : 08:26:53
|
Thanks for catching that.I will have it removed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-15 : 10:33:37
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|