Convert to MSSQL Query 1

[NetworkGhost]

I am trying to convert this query from access to MSSql I am having problems with this statement here:

IIf(([ORDER_CUSTOMERDETAIL]![LN_STA]="9" Or [ORDER_CUSTOMERDETAIL]![LN_STA]<"4"),"H"," ") AS HOLD, [ORDER_CUSTOMERDETAIL]![SRC_TYPE] AS HoldStatus, [ORDER_CUSTOMERDETAIL]![RQST_SHIP] AS RQST_DLVRY, [ORDER_CUSTOMERDETAIL]![REQD_DATE] AS PROM_DLVRY,
IIf(IsNull([CATALOG]![CAT_ITEM]),[ORDER_CUSTOMERDETAIL]![ITEM],[CATALOG]![CAT_ITEM]) AS [Inv Id], [ORDER_CUSTOMERDETAIL]![ORDER_QTY]-[ORDER_CUSTOMERDETAIL]![TOT_SHPPED] AS [Rem Qty], ORDER_CUSTOMERDETAIL.ORDER_QTY, [ORDER_CUSTOMERDETAIL]![ORDER_QTY]*[ORDER_CUSTOMERDETAIL]![UNIT_PRICE] AS [Book Value], ([ORDER_CUSTOMERDETAIL]![ORDER_QTY]-[ORDER_CUSTOMERDETAIL]![TOT_SHPPED])*[ORDER_CUSTOMERDETAIL]![UNIT_PRICE] AS [Total Value], Left([BODY],18) AS Rep, CATALOG.R_CLASS, [ITEM_ITEMCOST]![RL_MAT_CST]+[ITEM_ITEMCOST]![RL_LAB_CST]+[ITEM_ITEMCOST]![RL_VR_OVHD]+[ITEM_ITEMCOST]![RL_FX_OVHD]+[ITEM_ITEMCOST]![RL_OUT_CST] AS [Unit Cost], ORDER_CUSTOMERDETAIL.LN_STA

Im not to sure how to turn that part into a MSSQL friendly query. Im not really sure what the original person was accomplishing by it. Any help is appreciated.It is the IIF Statements and the fields separated by a ! that I really dont get.

The Whole Query below:


SELECT DISTINCT ORDER_CUSTOMERDETAIL.CO_NUMBER, ORDER_CUSTOMERDETAIL.CO_LN_NO, ORDER_CUSTOMERHEADER.CUST_NAME, Year([ORDER_CUSTOMERDETAIL]![REQD_DATE]) AS Year, Month([ORDER_CUSTOMERDETAIL]![REQD_DATE]) AS Month, ORDER_CUSTOMERHEADER.CUST_PO_NO, ORDER_CUSTOMERDETAIL.UNIT_PRICE,

IIf(([ORDER_CUSTOMERDETAIL]![LN_STA]="9" Or [ORDER_CUSTOMERDETAIL]![LN_STA]<"4"),"H"," ") AS HOLD, [ORDER_CUSTOMERDETAIL]![SRC_TYPE] AS HoldStatus, [ORDER_CUSTOMERDETAIL]![RQST_SHIP] AS RQST_DLVRY, [ORDER_CUSTOMERDETAIL]![REQD_DATE] AS PROM_DLVRY,
IIf(IsNull([CATALOG]![CAT_ITEM]),[ORDER_CUSTOMERDETAIL]![ITEM],[CATALOG]![CAT_ITEM]) AS [Inv Id], [ORDER_CUSTOMERDETAIL]![ORDER_QTY]-[ORDER_CUSTOMERDETAIL]![TOT_SHPPED] AS [Rem Qty], ORDER_CUSTOMERDETAIL.ORDER_QTY, [ORDER_CUSTOMERDETAIL]![ORDER_QTY]*[ORDER_CUSTOMERDETAIL]![UNIT_PRICE] AS [Book Value], ([ORDER_CUSTOMERDETAIL]![ORDER_QTY]-[ORDER_CUSTOMERDETAIL]![TOT_SHPPED])*[ORDER_CUSTOMERDETAIL]![UNIT_PRICE] AS [Total Value], Left([BODY],18) AS Rep, CATALOG.R_CLASS, [ITEM_ITEMCOST]![RL_MAT_CST]+[ITEM_ITEMCOST]![RL_LAB_CST]+[ITEM_ITEMCOST]![RL_VR_OVHD]+[ITEM_ITEMCOST]![RL_FX_OVHD]+[ITEM_ITEMCOST]![RL_OUT_CST] AS [Unit Cost], ORDER_CUSTOMERDETAIL.LN_STA

FROM (((ORDER_CUSTOMERDETAIL LEFT JOIN ORDER_CUSTOMERHEADER ON ORDER_CUSTOMERDETAIL.CO_NUMBER = ORDER_CUSTOMERHEADER.CO_NUMBER) LEFT JOIN [COMMISSION CODE TABLE] ON ORDER_CUSTOMERDETAIL.COM_CODE = [COMMISSION CODE TABLE].ID) LEFT JOIN CATALOG ON ORDER_CUSTOMERDETAIL.ITEM = CATALOG.ITEM) LEFT JOIN ITEM_ITEMCOST ON ORDER_CUSTOMERDETAIL.ITEM = ITEM_ITEMCOST.ITEM


WHERE ((Not (ORDER_CUSTOMERHEADER.CUST_NAME) Is Null) AND ((ITEM_ITEMCOST.COST_TYPE)="0") AND ((ORDER_CUSTOMERDETAIL.ORDER_TYPE)="C") AND ((ITEM_ITEMCOST.ITEM_DESC) Not Like "DO NOT *"));

 

[lespaul]

change the iif statements to a CASE statements:

Case WHEN ([ORDER_CUSTOMERDETAIL]![LN_STA]="9" Or [ORDER_CUSTOMERDETAIL]![LN_STA]<"4") THEN "H" ELSE " " END AS HOLD

CASE WHEN [CATALOG]![CAT_ITEM] Is Null THEN ORDER_CUSTOMERDETAIL]![ITEM] ELSE [CATALOG]![CAT_ITEM] END AS [Inv Id],

everything after that is just a field selection or calculation and should work correctly.

HTH

leslie

 

[NetworkGhost]

Cool. Thanks. Now I am getting a Syntax error on LN_STA

Case WHEN ([ORDER_CUSTOMERDETAIL]![LN_STA]="9" Or [ORDER_CUSTOMERDETAIL]![LN_STA]<"4") THEN "H" ELSE " " END AS HOLD

 

[NetworkGhost]

Also why an ! and not a period?

 

[NetworkGhost]

Ok This is what I changed it to but I am getting this error now:

Invalid column Name 'H'

Here is what passed the analzyer

SELECT DISTINCT
dbRDER_CUSTOMERDETAIL.CO_NUMBER, dbRDER_CUSTOMERHEADER.CUST_ID, dbRDER_CUSTOMERDETAIL.ITEM,
dbRDER_CUSTOMERHEADER.CUST_NAME, dbRDER_CUSTOMERDETAIL.CO_LN_NO, YEAR(dbRDER_CUSTOMERDETAIL.REQD_DATE)
AS [Year], MONTH(dbRDER_CUSTOMERDETAIL.REQD_DATE) AS [Month], dbRDER_CUSTOMERHEADER.CUST_PO_NO,
dbRDER_CUSTOMERDETAIL.UNIT_PRICE, Case WHEN (ORDER_CUSTOMERDETAIL.LN_STA="9" Or ORDER_CUSTOMERDETAIL.LN_STA<"4") THEN "H" ELSE " " END AS HOLD
FROM dbRDER_CUSTOMERDETAIL LEFT OUTER JOIN
dbRDER_CUSTOMERHEADER ON
dbRDER_CUSTOMERDETAIL.CO_NUMBER = dbRDER_CUSTOMERHEADER.CO_NUMBER LEFT OUTER JOIN
dbo.[COMMISSION CODE TABLE] ON dbRDER_CUSTOMERDETAIL.COM_CODE = dbo.[COMMISSION CODE TABLE].ID LEFT OUTER JOIN
dbo.[CATALOG] ON dbRDER_CUSTOMERDETAIL.ITEM = dbo.[CATALOG].ITEM LEFT OUTER JOIN
dbo.ITEM_ITEMCOST ON dbRDER_CUSTOMERDETAIL.ITEM = dbo.ITEM_ITEMCOST.ITEM
WHERE (NOT (dbRDER_CUSTOMERHEADER.CUST_NAME IS NULL)) AND (dbRDER_CUSTOMERDETAIL.LN_STA = '[9]') OR
(dbRDER_CUSTOMERDETAIL.LN_STA < '[4]')

 

[DotNetGnat]

should be

Case WHEN (ORDER_CUSTOMERDETAIL.LN_STA="9" Or ORDER_CUSTOMERDETAIL.LN_STA<"4" THEN "H" ELSE " " END) AS HOLD

you have brackets in the wrong place...

-DNG

 

[NetworkGhost]

I gues the main purpose of this statement is to place a H in the value of the field or leave it null. Thanks for the help so far. Not sure how to fix the column error

Case WHEN (ORDER_CUSTOMERDETAIL.LN_STA="9" Or ORDER_CUSTOMERDETAIL.LN_STA<"4") THEN "H" ELSE " " END AS HOLD

 

[NetworkGhost]

Here is the fix

CASE WHEN (ORDER_CUSTOMERDETAIL.LN_STA = '9' OR
ORDER_CUSTOMERDETAIL.LN_STA < '4') THEN 'H' ELSE ' ' END AS HOLD


Thanks DotNetGnat

 

[DotNetGnat]

no problem...glad to be of help..

-DNG