If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Getting rid of a Having clause
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... -- As always, any and all help appreciated! |
#2
|
|||
|
|||
Getting rid of a Having clause
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 |
Thread Tools | |
Display Modes | |
|
|