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

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  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]

• bnordberg

  SSCertifiable

  Points: 5480

  More actions

  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' ...

• cneuhold

  Hall of Fame

  Points: 3375

  More actions

  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.