| Author |
 Topic  |
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
 Posted - 10/11/2012 : 10:57:40
|
Hi All
Getting 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 statement
can someone point out what the problem is from the below query
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))
Thanks
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/11/2012 : 11:21:43
|
What's the error?
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47139 Posts |
Posted - 10/11/2012 : 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 follows
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
)
)
)
)t
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 10/12/2012 : 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 SP
Thanks again
CREATE PROCEDURE [dbo].[AttestConfirm]
(
@RQID int
)
AS
Select Requestid =
CASE
When Cnt = 0 then 'N'
else 'Y'END
FROM (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
))))t
WHERE (requestid = @RQID))t1
Thanks again
quote:
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 follows
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
)
)
)
)t
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47139 Posts |
Posted - 10/12/2012 : 12:50:24
|
it looks like you've redundant checks nested
at 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
jim_jim
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 10/15/2012 : 08:26:53
|
| Thanks for catching that.I will have it removed |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47139 Posts |
Posted - 10/15/2012 : 10:33:37
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Stored Procedure help
