Need help with IF subquery in a Select statement

Question

Post reply

Need help with IF subquery in a Select statement

pcq0125

SSCrazy

Points: 2890
More actions
May 5, 2006 at 11:05 am

#172321

I have my script below and error found on the IF statement in the subquery and I don't what syntax went worng Can sombody help? Thanks.
SELECT
RptBilling.ItemType,
Patient.PatientLN
Programs.ProgramDescription,
ARBatches.ARBatch,
Patient.PatientFN,
RptBilling.BillPatientID,
IF BillFormats.BillFormatDescription IS NULL Then(Select BillFormats1.BillFormatDescription
FROM RptBilling RptBilling1 INNER JOIN BillPatients BillPatients1
ON RptBilling1.BillPatientID = BillPatients1.BillPatientID
INNER JOIN BillPatients BillPatients ON BillPatients.BillPatientID = BillPatients1.BillPatientID
INNER JOIN BTI.PatFundSource PatFundSource1
ON BillPatients1.PatFundSourceID = PatFundSource1.PatFundSourceID
INNER JOIN BTI.ReimbPlans ReimbPlans1
ON PatFundSource1.ReimbPlansID = ReimbPlans1.ReimbPlansID
INNER JOIN BTI.BillFormats BillFormats1 ON ReimbPlans1.BillFormatID = BillFormats1.BillFormatID
Where BillPatients.BillPatientID = BillPatients1.BillPatientID AND BillFormats.BillFormatDescription IS NULL
)END,
ElecFormats.ElecFormatDescription
FROM (((BTI.RptBilling RptBilling INNER JOIN BTI.ARBatches ARBatches ON RptBilling.ARBatchID=ARBatches.ARBatchID) INNER JOIN BTI.PatAdmissions PatAdmissions
ON RptBilling.AdmissionID=PatAdmissions.AdmissionID) INNER JOIN BTI.Patient Patient ON PatAdmissions.PatientID=Patient.PatientID) INNER JOIN
BTI.Programs Programs ON PatAdmissions.ProgramID=Programs.ProgramID INNER JOIN BTI.BillPatients BillPatients ON RptBilling.BillPatientID = BillPatients.BillPatientID
LEFT OUTER JOIN BTI.ReimbplanPaperFormats ReimbplanPaperFormats ON BillPatients.ReimbPlanPaperFormatID = ReimbPlanPaperFormats.ReimbPlanPaperFormatID LEFT OUTER JOIN
BTI.BillFormats BillFormats ON ReimbPlanPaperFormats.BillFormatID = BillFormats.BillFormatID LEFT OUTER JOIN BTI.ReimbplanElecFormats ReimbplanElecFormats
ON BillPatients.ReimbPlanElecFormatID = ReimbPlanElecFormats.ReimbPlanElecFormatID LEFT OUTER JOIN
BTI.ElecFormats ElecFormats ON ReimbPlanElecFormats.ElecFormatID = ElecFormats.ElecFormatID

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 1

IF statements in SQL typically look like this

IF (some condition)

Begin

(some code)

End

No THEN.

David McFarland SSChampion Points: 11815 More actions · Answer 2

You can't use the IF inline like that. Replace that line with:

CASE WHEN BillFormats.BillFormatDescription IS NULL THEN (Select BillFormats1.BillFormatDescription

FROM RptBilling RptBilling1 INNER JOIN BillPatients BillPatients1

ON RptBilling1.BillPatientID = BillPatients1.BillPatientID

INNER JOIN BillPatients BillPatients ON BillPatients.BillPatientID = BillPatients1.BillPatientID

INNER JOIN BTI.PatFundSource PatFundSource1

ON BillPatients1.PatFundSourceID = PatFundSource1.PatFundSourceID

INNER JOIN BTI.ReimbPlans ReimbPlans1

ON PatFundSource1.ReimbPlansID = ReimbPlans1.ReimbPlansID

INNER JOIN BTI.BillFormats BillFormats1 ON ReimbPlans1.BillFormatID = BillFormats1.BillFormatID

Where BillPatients.BillPatientID = BillPatients1.BillPatientID AND BillFormats.BillFormatDescription IS NULL

) END,

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 3

Good point, David. My eyes went straight to the IF and caught the first problem I saw, and I didn't really look around it.

pcq0125 SSCrazy Points: 2890 More actions · Answer 4

I ran the query with the case statement and it returned a "multiple rows error" How can I return the single BillFormats1.BillFormatDescription row value where the (subquery) BillPatients1.BillllpatientID = (Mainquery) BillPatients.BillpatientID ? Please advise.Thanks.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

It'd be really difficult to figure out why you're getting multiple rows unless you post sample data from each of the tables etc..

You'd trace this quicker if you just ran your select in QA - use a process of elimination and see what results you get each time you join to a table - also, it looks like you have a redundant join on your BillPatients table...

**ASCII stupid question, get a stupid ANSI !!!**

Matthew Borgers Grasshopper Points: 13 More actions · Answer 6

Usually, if you need to limit results in a subselect, you either want to use a "DISTINCT" or a "TOP 1" in your subquery. Your statement might look like:

Select DISTINCT BillFormats1.BillFormatDescription