IF statement with NULL and subquery help

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

SQL Server 2000 Forums

Transact-SQL (2000)

IF statement with NULL and subquery help

Author

Topic

fredong
Yak Posting Veteran

80 Posts

Posted - 2006-05-05 : 12:19:28

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

k

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-05 : 12:51:04

Is this in a SQL server or some other ?

In T-SQL
u cannot use "IF", like u use.
u may need to use Case -- When -- Then ..

Srinika

fredong
Yak Posting Veteran

80 Posts

Posted - 2006-05-05 : 12:57:47

Do you know how to rewrite the statement using Case? Thanks.

k

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-05 : 14:12:36

Is this Microsoft SQL server ? Thanks

Srinika

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 02:48:22

Somthing like this ..


SELECT

RptBilling.ItemType,
Patient.PatientLN
Programs.ProgramDescription,
ARBatches.ARBatch,
Patient.PatientFN,
RptBilling.BillPatientID,
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,

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

Just make sure that this Sub query returns one records per row... for the parent query

(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
)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

fredong
Yak Posting Veteran

80 Posts

Posted - 2006-05-07 : 15:59:43

Thanks.

k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources