I have this SQL statement and within it is an IIF, but it fails. (IIf(PO_Header.Status='Open',0,1) AS AS Status) Im not sure how to write it for SQL.



sql = "SELECT " & oCmn.sSQLFmtDistinct(sDBType) & " Job.Job, Packlist_Header.Customer_Vendor, " _
                            & "Job_Operation.Sequence, Job_Operation.Operation_Service, Job_Operation.Description AS Job_Op_Description, " _
                            & "Job.Part_Number, Job.Description, Job.Rev, Job.Order_Unit, C.ContactName, A.Phone, " _
                            & "Packlist_Detail.Packlist, Packlist_Detail.PO_Number, 0 AS Status, Packlist_Detail.Tracking_Nbr, " _
                            & "(Packlist_Detail.Packlist_Detail) as XKey, Packlist_Header.Packlist_Date as XDate, Packlist_Detail.Quantity " _
                    & "FROM (((((Job INNER JOIN Job_Operation ON Job.Job = Job_Operation.Job) " _
                            & "INNER JOIN Packlist_Detail ON Job_Operation.Job_Operation = Packlist_Detail.Job_Operation) " _
                            & "INNER JOIN Packlist_Header ON Packlist_Detail.Packlist = Packlist_Header.Packlist) " _
                            & "INNER JOIN Vendor ON Packlist_Header.Customer_Vendor = Vendor.Vendor) " _
                            & "LEFT JOIN (SELECT Vendor, MIN(Contact_Name) AS ContactName FROM Contact WHERE Contact_Name is not null GROUP BY Vendor) AS C ON Vendor.Vendor = C.Vendor) " _
                            & "LEFT JOIN (SELECT Vendor, Phone FROM Address WHERE Type like '1%') AS A ON Vendor.Vendor = A.Vendor " _
                    & sWhere &" " _
                    & "UNION SELECT Job.Job, Packlist_Header.Customer_Vendor, " _
                            & "Job_Operation.Sequence, Job_Operation.Operation_Service, Job_Operation.Description AS Job_Op_Description, " _
                            & "Job.Part_Number, Job.Description, Job.Rev, Job.Order_Unit, '' AS Contact_Name, '' AS Phone, " _
                            & "'zzzzz' as Packlist, PO_Header.PO AS PO_Number, IIf(PO_Header.Status='Open',0,1) AS AS Status, null as Tracking_Nbr, " _
                            & "(Material_Trans.Material_Trans) as XKey, Material_Trans.Material_Trans_Date as XDate, (Material_Trans.Quantity * -1) as Quantity " _
                    & "FROM ((((((Job INNER JOIN Job_Operation ON Job.Job = Job_Operation.Job) " _
                            & "INNER JOIN Packlist_Detail ON Job_Operation.Job_Operation = Packlist_Detail.Job_Operation) " _
                            & "INNER JOIN Packlist_Header ON Packlist_Detail.Packlist = Packlist_Header.Packlist) " _
                            & "LEFT JOIN Source ON Job_Operation.Job_Operation = Source.Job_Operation) " _
                            & "LEFT JOIN PO_Detail ON Source.PO_Detail = PO_Detail.PO_Detail) " _
                            & "LEFT JOIN PO_Header ON PO_Detail.PO = PO_Header.PO) " _
                            & "LEFT JOIN Material_Trans ON Source.Source = Material_Trans.Source " _
                    & sWhere2
 

IIf(PO_Header.Status='Open',0,1)

in T-SQL is

case
when PO_Header.Status='Open'
     then 0
else 1
end as Status

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

In SQL 2012 there is IIF function.

PluralSight Learning Library