IF statement with NULL and subquery help

[fredong]

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

 

[PHV]

In ANSI SQL you have to use the CASE WHEN ... THEN ... END expression.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredong]

Can you show me how to write the Case statement? Thanks.

 

[PHV]

Perhaps like this ?
...
CASE WHEN BillFormats.BillFormatDescription IS NULL THEN (Select BillFormats1.BillFormatDescription
...
) ELSE BillFormats.BillFormatDescription END Description,
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredong]

Thanks