Getting rid of a Having clause

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Hello

I appologize ahead of time...

this is going to be nasty...

SELECT
dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustID,
dbo_CUSTOMER.NAME, dbo_CUSTOMER_ORDER.ID,
dbo_CUSTOMER.COUNTRY,
dbo_CUSTOMER_ORDER.CUSTOMER_PO_REF AS CustPO,
dbo_CUSTOMER_ORDER.ORDER_DATE AS CODate,
dbo_CUSTOMER_ORDER.SELL_RATE,
dbo_CUSTOMER_ORDER.CURRENCY_ID,
IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency MD","Machine Down"
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Stock","Stock",[dbo_CUSTOMER_ORDER].[USER_10]))))) AS Priority,
DateDiff("d",[ORDER_DATE],Now()) AS Age,
dbo_CUSTOMER_ORDER.DESIRED_SHIP_DATE AS DShipDate,
DateDiff("d",Now(),[dbo_CUSTOMER_ORDER].[DESIRED_SHIP_DATE]) AS DaysDue,
dbo_CUSTOMER_ORDER.PROMISE_DATE AS PromDate,
DateDiff("d",Now(),[dbo_CUSTOMER_ORDER].[PROMISE_DATE]) AS PDaysDue,
dbo_CUSTOMER_ORDER.STATUS AS COStatus,

Sum(IIf([TRADE_DISC_PERCENT]>=1,0,IIf([ORDER_QTY]-[TOTAL_SHIPPED_QTY]>0,(([ORDER_QTY]-[TOTAL_SHIPPED_QTY])*IIf([dbo_CUSTOMER_ORDER]![CURRENCY_ID]<>"CDN",[UNIT_PRICE]*[SELL_RATE],[UNIT_PRICE])),0)))
AS OpenValue,

Sum(IIf([TRADE_DISC_PERCENT]>=1,0,IIf([ORDER_QTY]-[TOTAL_SHIPPED_QTY]<=0,([ORDER_QTY]*IIf([dbo_CUSTOMER_ORDER]![CURRENCY_ID]<>"CDN",[UNIT_PRICE]*[SELL_RATE],[UNIT_PRICE])),0))) AS ClosedValue,

Sum(IIf([TRADE_DISC_PERCENT]>=1,0,[ORDER_QTY]*IIf([dbo_CUSTOMER_ORDER]![CURRENCY_ID]<>"CDN",[UNIT_PRICE]*[SELL_RATE],[UNIT_PRICE]))) AS TotalOrderValue,
dbo_CUST_ORDER_LINE.GL_REVENUE_ACCT_ID AS GLID,
dbo_CUST_ORDER_LINE.PRODUCT_CODE AS Pcode,
dbo_CUST_ORDER_LINE.TRADE_DISC_PERCENT AS Disc,
IIf([dbo_CUST_ORDER_BINARY].[BITS] Is
Null,"",AlanByteArrayToString([dbo_CUST_ORDER_BINARY]![BITS])) AS OrderSpecs,
IIf([AftermarketSales]=-1,"True","False") AS VSPF,
IIf([VisualOrderName] Is
Null,[dbo_CUSTOMER_ORDER].[SALESREP_ID],[VisualOrderName]) AS VOSP,
IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency MD","Machine Down"
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Stock","Stock",[dbo_CUSTOMER_ORDER].[USER_10]))))) AS PriFilter
FROM
qryASPOrderTracker
RIGHT JOIN (((dbo_CUSTOMER
RIGHT JOIN dbo_CUSTOMER_ORDER
ON dbo_CUSTOMER.ID = dbo_CUSTOMER_ORDER.CUSTOMER_ID)
LEFT JOIN dbo_CUST_ORDER_LINE
ON dbo_CUSTOMER_ORDER.ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_CUST_ORDER_BINARY
ON dbo_CUSTOMER_ORDER.ID = dbo_CUST_ORDER_BINARY.CUST_ORDER_ID)
ON qryASPOrderTracker.VisualOrderName = dbo_CUSTOMER_ORDER.SALESREP_ID
WHERE
(((IIf([AftermarketSales]=-1,"True","False")) In
((IIf([Forms]![frmOrderTracker]![tglAftermarket] Is Null,"False",
(IIf([Forms]![frmOrderTracker]![tglAftermarket]=-1,"True","False")))),
(IIf([Forms]![frmOrderTracker]![tglAftermarket] Is Null,"True",
(IIf([Forms]![frmOrderTracker]![tglAftermarket]=0,"False","True"))))))
AND
((Year([ORDER_DATE]))>=IIf([Forms]![frmOrderTracker]![cboFromYear]="All",1900,[Forms]![frmOrderTracker]![cboFromYear]))
AND ((dbo_CUSTOMER_ORDER.STATUS)
In(IIf([Forms]![frmOrderTracker]![tglFirmed]=False,"f",""),
IIf([Forms]![frmOrderTracker]![tglReleased]=False,"r",""),
IIf([Forms]![frmOrderTracker]![tglOnHold]=False,"h",""),
IIf([Forms]![frmOrderTracker]![tglClosed]=False,"c",""))))
GROUP BY
dbo_CUSTOMER_ORDER.CUSTOMER_ID,
dbo_CUSTOMER.NAME,
dbo_CUSTOMER_ORDER.ID,
dbo_CUSTOMER.COUNTRY,
dbo_CUSTOMER_ORDER.CUSTOMER_PO_REF,
dbo_CUSTOMER_ORDER.ORDER_DATE,
dbo_CUSTOMER_ORDER.SELL_RATE,
dbo_CUSTOMER_ORDER.CURRENCY_ID,
IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency MD","Machine Down"
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Stock","Stock",[dbo_CUSTOMER_ORDER].[USER_10]))))),
DateDiff("d",[ORDER_DATE],Now()),
dbo_CUSTOMER_ORDER.DESIRED_SHIP_DATE,
DateDiff("d",Now(),[dbo_CUSTOMER_ORDER].[DESIRED_SHIP_DATE]),
dbo_CUSTOMER_ORDER.PROMISE_DATE,
DateDiff("d",Now(),[dbo_CUSTOMER_ORDER].[PROMISE_DATE]),
dbo_CUSTOMER_ORDER.STATUS,
dbo_CUST_ORDER_LINE.GL_REVENUE_ACCT_ID,
dbo_CUST_ORDER_LINE.PRODUCT_CODE, dbo_CUST_ORDER_LINE.TRADE_DISC_PERCENT,
IIf([dbo_CUST_ORDER_BINARY].[BITS] Is
Null,"",AlanByteArrayToString([dbo_CUST_ORDER_BINARY]![BITS])),
IIf([AftermarketSales]=-1,"True","False"),
IIf([VisualOrderName] Is
Null,[dbo_CUSTOMER_ORDER].[SALESREP_ID],[VisualOrderName]),
IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency MD","Machine Down",

IIf([dbo_CUSTOMER_ORDER].[USER_10]="Stock","Stock",[dbo_CUSTOMER_ORDER].[USER_10])))))
HAVING
(((IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency MD","Machine Down",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Stock","Stock",[dbo_CUSTOMER_ORDER].[USER_10]))))))
In (IIf([Forms]![frmOrderTracker]![tglStock]=False,"Stock",""),
IIf([Forms]![frmOrderTracker]![tglEmergency]=False,"Emergency",""),
IIf([Forms]![frmOrderTracker]![tglMachineDown]=False,"Machine Down",""),
IIf([Forms]![frmOrderTracker]![tglService]=False,"Service",""),
IIf([Forms]![frmOrderTracker]![tglNoPriority]=False,"No Priority",""))))
ORDER BY
dbo_CUSTOMER_ORDER.ID DESC;

i am pretty sure that the reason the Having is there is the way i am
creating the information in that column, but i am wondering if anyone has any
ideas of how to get rid of the having clause in this query. i was thinking
maybe i can seperate each possible result into a seperate column and put the
criteria on each, but that might just be as messy as the the figuring of the
priority is done by a formula no matter which way i do it.

anyways...
 
Assuming you actually have, symbolically:

....
WHERE x
GROUP BY y
HAVING z


then, modify it to have:

....
WHERE (x) AND (z)
GROUP BY y



ie, add a pair of parenthesis around the content that you have in the WHERE
and in the HAVING clause, that does not hurt the performance, add an AND to
the modified where clause, and bring the content of the now modified having
clause and remove the key word HAVING now left without any content.



Vanderghast, Access MVP
 
Back
Top