Question about IIF statement 1

[tbonehwd]

I am using an IIF statement to do a calculation based on the order number, in our system any order below 90000000 is an order and any order above 90000000 is a credit this code works perfect for other scenarios however I am now trying to create a report that will list net QTY_SHP for all customers of a specific type and in some cases a customer may not have purchased anything so no order numbers in this scenario this code breaks cause they must have an order either greater than or less 90000000. What I would like this to do in this case is to give me a zero does anyone have any ideas?

Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1))

 

[SkipVought]

Hi,

Children 12 and over can get in with no adult.

Children under 12 must be accompanied by an adult.

Unborn children......

???????? Hmmmmmmmmmmmmm ???????

Ya gotta have an ORDER to do something related to an ORDER, don't you?

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[Golom]

Assuming that you're running this in some sort of join

(IIF(IsNull(PROOLN_M.ORD_NUM), 0, 
Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1))) As [TotalQuantity]

 

[tbonehwd]

I agree but I don't have like it...

I know it is a stupid question but I am trying to create a five year report for customers unfortunately some customers didn't buy each year.

 

[SkipVought]

Then there's nuthin to report.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[PHV]

Hopefully you know what an outer join is.
Sum(IIf(IsNull(PROOLN_M.ORD_NUM),0,(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

Yes I am using outer joins and that didn't help until I realized that this was causing my problem.

Thanks,

Tbonehwd

 

[PHV]

Any chance you could post your actual SQL code, some input samples and actual result vs expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

PHV,

I have 118 accounts with the type of DISTFOR however with this query I am only getting 40 I am sure it is still due to the fact that they do not have orders for this year or better yet they do not have any PROOLN_M.QTY_SHP or PROOLN_M.ITM_NET records.

Here is the code for this query:

"SELECT Last(CDSADR_M.CMP_NME) As COMPANY, CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP " & _
", Sum(IIf(IsNull(PROOLN_M.ORD_NUM),0,(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1)))) AS CURR_YR_UNITS " & _
", Sum(IIf(IsNull(PROOLN_M.ORD_NUM),0,(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,PROOLN_M.ITM_NET*-1)))) AS CURR_YR_DOLLARS " & _
"  FROM ((CDSCTM_M LEFT OUTER JOIN CDSADR_M ON CDSADR_M.CTM_NBR=CDSCTM_M.CTM_NBR) " & _
"  LEFT OUTER JOIN PROORD_M ON CDSCTM_M.CTM_NBR=PROORD_M.CTM_NBR) " & _
"  LEFT OUTER JOIN PROOLN_M ON PROORD_M.ORD_NUM=PROOLN_M.ORD_NUM " & _
"WHERE CDSADR_M.ADR_CDE='STANDARD' AND CDSADR_M.ADR_FLG='0' " & _
"AND CDSCTM_M.CTM_TYP = 'DISTFOR' & _
"AND PROORD_M.ACT_DTE " >= #01/31/2005# & _
"AND PROORD_M.ACT_DTE " <= #11/30/2005# & _
"GROUP BY CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP ;"

 

[PHV]

Any test in the where clause against a field from a table defeats the outer join on tha table.
You may try something like this:
"SELECT Last(A.CMP_NME) As COMPANY, A.CTM_NBR, C.CTM_TYP " & _
", Sum(IIf(IsNull(L.ORD_NUM),0,(IIf(L.ORD_NUM<'90000000',L.QTY_SHP,L.QTY_SHP*-1)))) AS CURR_YR_UNITS " & _
", Sum(IIf(IsNull(L.ORD_NUM),0,(IIf(L.ORD_NUM<'90000000',L.ITM_NET,L.ITM_NET*-1)))) AS CURR_YR_DOLLARS " & _
" FROM ((CDSCTM_M AS C INNER JOIN CDSADR_M AS A ON C.CTM_NBR=A.CTM_NBR) " & _
" LEFT JOIN (SELECT CTM_NBR, ORD_NUM FROM PROORD_M WHERE ACT_DTE Between #2005-01-31# And #2005-11-30#) AS O ON C.CTM_NBR=O.CTM_NBR) " & _
" LEFT JOIN PROOLN_M AS L ON O.ORD_NUM=L.ORD_NUM " & _
"WHERE A.ADR_CDE='STANDARD' AND A.ADR_FLG='0' " & _
"AND C.CTM_TYP = 'DISTFOR' & _
"GROUP BY A.CTM_NBR, C.CTM_TYP"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

PHV,

Thanks so much your example fixed the problem... It works great.

Tbonehwd