Convert IIF Query to SQL

Question

Post reply

Convert IIF Query to SQL

mrissi

Newbie

Points: 3
More actions
October 30, 2003 at 8:14 am

#65563

Help!

I am trying to convert an access IIF query to SQL. I have not been able to figure how to do this.

Any help would be greatly appreciated!

Here are the queries:

Booked:

SELECT IIf([CustomerType]="ENT",[ENT Ter] & " Total",[Ter] & " Total") AS TerNew, Sum([TaxableAmount]+[NonTaxableAmount]) AS Total

FROM (SO_03SOHistoryHeader INNER JOIN AR1_CustomerMaster ON (SO_03SOHistoryHeader.CustomerNumber = AR1_CustomerMaster.CustomerNumber) AND (SO_03SOHistoryHeader.DivisionNumber = AR1_CustomerMaster.Division)) LEFT JOIN [NewMAS90TerLookup August 2003] ON SO_03SOHistoryHeader.SalespersonCode = [NewMAS90TerLookup August 2003].Code

WHERE (((SO_03SOHistoryHeader.OrderDate)>=#10/1/2003# And (SO_03SOHistoryHeader.OrderDate)<=#10/31/2003#) AND ((SO_03SOHistoryHeader.OrderStatus)="A" Or (SO_03SOHistoryHeader.OrderStatus)="C" Or (SO_03SOHistoryHeader.OrderStatus)="N"))

GROUP BY IIf([CustomerType]="ENT",[ENT Ter] & " Total",[Ter] & " Total")

ORDER BY IIf([CustomerType]="ENT",[ENT Ter] & " Total",[Ter] & " Total");

Backlog:

SELECT IIf([CustomerType]="ENT",[ENT Ter],[Ter]) AS TerNew, SO1_SOEntryHeader.SalespersonCode, SO1_SOEntryHeader.SalesOrderDate, SO1_SOEntryHeader.ShipExpireDate, SO1_SOEntryHeader.SalesOrderNumber, SO1_SOEntryHeader.Division, SO1_SOEntryHeader.CustomerNumber, SO1_SOEntryHeader.BillToName, SO1_SOEntryHeader.CustomerPONumber, [NonTaxableAmount]+[TaxableAmount] AS [Total Sales], SO1_SOEntryHeader.SalesOrderType

FROM (SO1_SOEntryHeader INNER JOIN AR1_CustomerMaster ON (SO1_SOEntryHeader.CustomerNumber = AR1_CustomerMaster.CustomerNumber) AND (SO1_SOEntryHeader.Division = AR1_CustomerMaster.Division)) LEFT JOIN [NewMAS90TerLookup August 2003] ON SO1_SOEntryHeader.SalespersonCode = [NewMAS90TerLookup August 2003].Code

WHERE (((SO1_SOEntryHeader.SalesOrderDate)>=#7/1/2003#) AND ((SO1_SOEntryHeader.SalesOrderNumber) Not Like "R*") AND ((SO1_SOEntryHeader.SalesOrderType)<>"R"));

Shipped:

SELECT IIf([CustomerType]="ENT",[ENT Ter],[Ter]) AS TerNew, [NEO ARN_InvHistoryHeader].SOSlspersonCode, AR1_CustomerMaster.CustomerType, [NEO ARN_InvHistoryHeader].InvoiceNumber, [NEO ARN_InvHistoryHeader].InvoiceType, [NEO ARN_InvHistoryHeader].InvoiceDate, [NEO ARN_InvHistoryHeader].SOShipDate, [NEO ARN_InvHistoryHeader].Division, [NEO ARN_InvHistoryHeader].CustomerNumber, [NEO ARN_InvHistoryHeader].SOBillToName, [SOTaxableSalesAmt]+[SONonTaxableSalesAmt] AS Revenue, [NEO ARN_InvHistoryHeader].SOTransDate

FROM ([NEO ARN_InvHistoryHeader] INNER JOIN AR1_CustomerMaster ON ([NEO ARN_InvHistoryHeader].CustomerNumber = AR1_CustomerMaster.CustomerNumber) AND ([NEO ARN_InvHistoryHeader].Division = AR1_CustomerMaster.Division)) LEFT JOIN [NewMAS90TerLookup August 2003] ON [NEO ARN_InvHistoryHeader].SOSlspersonCode = [NewMAS90TerLookup August 2003].Code

WHERE ((([NEO ARN_InvHistoryHeader].InvoiceType)<>"X") AND (([NEO ARN_InvHistoryHeader].InvoiceDate)>=#10/1/2003# And ([NEO ARN_InvHistoryHeader].InvoiceDate)<=#10/31/2003#));

Thanks!

Mike