can u help me debugging this - 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

General SQL Server Forums

New to SQL Server Programming

can u help me debugging this

Author

Topic

bentong
Starting Member

5 Posts

Posted - 2007-08-13 : 06:39:03

select

if(acr_application.appStatus = 'AOO' then acr_application.appDt,
if(acr_application.appStatus = 'A01' then acr_application.recvdDt,
if(acr_application.appStatus = 'A02' then
(select A.payDt from acr_payment A, acr_reference_no B
where A.refNo = B.acrReferenceNoId and B.refType = 'ACF' and B.appNo =
acr_application.appNo),
if(acr_application.appStatus = ('A03', 'A08'),
(select C.updatedBy from acr_insp_sched C
where C.appNo = acr_application.appNo and C.seqNo =
(select max (D.seqNo) from acr_insp_sched D where D.appNo = C.appNo)),
if (acr_application.appStatus = ('A04', 'A05', 'A06', 'A15'),
(select E.updatedBy from acr_inspection E
where E.appNo = acr_application.appNo and E.seqNo =
(select max (F.seqNo) from acr_inspection F where F.appNo = E.appNo)),
if(acr_application.appStatus = 'A07',
(select G.payDt from acr_payment G, acr_reference_no H
where G.refNo = H.refId and H.refType = 'REI' and H.appNo =
acr_application.appNo),
if(acr_application.appStatus = ('A10', 'A11'), acr_application.approvedDt,
if(acr_application.appStatus = 'A13', acr_application.updatedBy,
if(acr_application.appStatus = 'A14',
(select H.payDt from acr_payment H, acr_reference_no J
where H.refNo = J.refId and J.refType = 'CSB' and J.appNo = acr_application.appNo), null)))))))))

from acr_application

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 08:53:57

where is the error and what is the error.

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 09:02:27

Try this

select

case when acr_application.appStatus = 'AOO' then acr_application.appDt
else
case when acr_application.appStatus = 'A01' then acr_application.recvdDt
end
end

from acr_application

You can try to complete the rest on your own. Also look for help for CASE on books online and you should be able to do it.

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 09:13:08

Its not exactly clear to me what result you are trying to get but this is what i have finally, hope it would help you to get where you want.

select

case when acr_application.appStatus = 'AOO' then acr_application.appDt
else
case when acr_application.appStatus = 'A01' then acr_application.recvdDt
end
end

,

case when acr_application.appStatus = 'A02' then

(select A.payDt from acr_payment A, acr_reference_no B
where A.refNo = B.acrReferenceNoId and B.refType = 'ACF' and B.appNo =
acr_application.appNo) end ,

case when acr_application.appStatus IN ('A03', 'A08')
then (select C.updatedBy from acr_insp_sched C
where C.appNo = acr_application.appNo and C.seqNo =
(select max (D.seqNo) from acr_insp_sched D where D.appNo = C.appNo))

ELSE

Case WHEN
acr_application.appStatus IN ('A04', 'A05', 'A06', 'A15')
THEN (select E.updatedBy from acr_inspection E
where E.appNo = acr_application.appNo and E.seqNo =
(select max (F.seqNo) from acr_inspection F where F.appNo = E.appNo))

END
END,

CASE WHEN acr_application.appStatus = 'A07' THEN
(select G.payDt from acr_payment G, acr_reference_no H
where G.refNo = H.refId and H.refType = 'REI' and H.appNo =
acr_application.appNo)END ,
CASE WHEN acr_application.appStatus IN ('A10', 'A11') THEN acr_application.approvedDt END,

case when acr_application.appStatus = 'A13' THEN acr_application.updatedBy END,
CASE WHEN acr_application.appStatus = 'A14' THEN

(select H.payDt from acr_payment H, acr_reference_no J
where H.refNo = J.refId and J.refType = 'CSB' and J.appNo = acr_application.appNo)

ELSE NULL

END

from acr_application

Ashley Rhodes

bentong
Starting Member

5 Posts

Posted - 2007-08-13 : 23:00:37

I've tried it and i do a little debug and it works but subquery returns morethan 1 row..... I appreciate the help n thank you very much

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources