Need help with IF subquery in a Select statement
-
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
-
IF statements in SQL typically look like this
IF (some condition)
Begin
(some code)
End
No THEN.
-
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,
-
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.
-
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.
-
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 !!!** -
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
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
The trouble with using a "TOP 1" to limit your data returned is that you may not get the particular value you are looking for. The challenge with a "DISTINCT" is that you may still return multiple rows. Try running the subselect by itself and look at the data it returns to decide how to best approach this problem.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply