Post reply

Convert IIF Query to SQL

  • 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

  • October 30, 2003 at 8:17 am

    #480324

    This one might help you

    http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=16662

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • October 31, 2003 at 8:30 am

    #480463

    select case when CustomerType='ENT' then [ENT Ter] & 'Total' else 'Total' end as TerNew, ...

    group by (case when CustomerType='ENT' then [ENT Ter] & 'Total' else 'Total' ...

  • November 3, 2003 at 6:44 am

    #480631

    hi!

    check CASE in BOL! BOL = "Books OnLine" of SQL Server, a very valid source of very *basic* and advanced information!

    regards,

    chris.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply