dcsimg
Results 1 to 13 of 13
  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    833

    Resolved [RESOLVED] Anyway to accomplish this query without UNION?

    I'm working on redoing our web order system code, which uses MySQL 5.6 on the backend. Currently I have a query like this that works fine:
    SELECT Item.ItemId,Null AS Qty,Item.Description,Item.Brand,Item.Pack,Item.Cost,Item.UOM,PC2,PC3,PC4,PC5,PC6,PC7,Item.CatId,Item .EachQty,Item.ConvFactor,Item.AvgWgt,Rcst,Acst,Fcst,Ocst,Pcst,Liprop,category.description AS catdesc,Item.Code1,Item.Code2,brkitem.itemid AS brkitemid,0.00 AS EachPrice,00 AS Markup,0.00 AS UnitPrice,0.00 AS Price,Null AS NutInfo,Null AS LastOrdered
    FROM Item INNER JOIN category ON Item.CatId = category.catid
    LEFT JOIN brkitem on Item.ItemID = brkitem.ItemID
    WHERE Category.CatId LIKE '%'
    ORDER BY CatId asc , ItemId asc LIMIT 0, 25
    This is used to eventually return a list of items (25 at a time) the user can select to add to the cart. The only truly relevant table here is item.

    I have now been asked to also show the quantity for any items already in the cart, which is held in orderdtl table. I should perhaps note that the reason for returning NULL for Qty before was simply so that field would exist in the table that auto-generates from the AJAX response. Anyway, getting the info is simple, but I was wondering if there was a way to do it efficiently in a single query without having to use a UNION. It seemed like I had done something like this in Oracle but can't seem to manage it in MySQL. This gives me just the 2 rows that match the order:
    SELECT Item.ItemId,orderdtl.qty as Qty,Item.Description,Item.Brand,Item.Pack,Item.Cost,Item.UOM,PC2,PC3,PC4,PC5,PC6,PC7,Item.CatId,Item.Ea chQty,Item.ConvFactor,Item.AvgWgt,Rcst,Acst,Fcst,Ocst,Pcst,Liprop,category.description AS catdesc,Item.Code1,Item.Code2,brkitem.itemid AS brkitemid,0.00 AS EachPrice,00 AS Markup,0.00 AS UnitPrice,0.00 AS Price,Null AS NutInfo,Null AS LastOrdered
    FROM Item INNER JOIN category ON Item.CatId = category.catid
    LEFT JOIN brkitem on Item.ItemID = brkitem.ItemID
    LEFT JOIN orderdtl on Item.itemid = orderdtl.itemid
    WHERE Category.CatId LIKE '%'
    AND orderdtl.orderid = 38239
    ORDER BY CatId asc , ItemId asc LIMIT 0, 25 ;
    I've tried all manor of JOINs on orderdtl but I always get only the 2 records, which makes sense, so I'm thinking I must use a UNION to pull all 25 records with one query. Any other options I've forgotten?

    I can also run a loop in the server side code and manipulate the field there before returning the data, but I'll need to test which gives better performance.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,653

    Re: Anyway to accomplish this query without UNION?

    Why would it increase from 2 rows to 25 rows? Do you have 25 rows for Order Id 38239 in the OrderDtl table?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    833

    Re: Anyway to accomplish this query without UNION?

    Nope, which is why I figured UNION or similar would be required since nearly all items will not be in a given order. I want all items and then the QTY of those that just happen to be in the current order so QTY = 0 for the majority.

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,653

    Re: Anyway to accomplish this query without UNION?

    If ITEM table has all the rows you want - and the OrderDtl only has 2 rows - then you need to remove the WHERE condition that is limiting it to 38239.

    Put that condition on the ON clause that JOINS the tables - that way you only JOIN OrderDtl's for the two rows but still return 25 rows.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    833

    Re: Anyway to accomplish this query without UNION?

    That should do it. I knew I must be having a senior moment!

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    833

    Re: Anyway to accomplish this query without UNION?

    FWIW, the server-side loop was superior in my case to using the updated query, which approximately doubled the time (according to Chrome's Inspector for the ajax call). I wonder if this is because I already needed the loop to run through the complex pricing routine for each item being returned. It added so little time to the ajax call that I'm just going to leave the
    SELECT Qty FROM orderdtl WHERE orderid = #Session.OrderID# AND ItemID='#queryResultTemp.ItemId#'
    inside the loop instead of doing a single query without ItemID outside the loop and checking for any matching results. I'm actually a bit surprised by this.

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,653

    Re: [RESOLVED] Anyway to accomplish this query without UNION?

    Keep in mind that you are working on a web app - the individual speed of a query when run by a single user is not the only metric involved here.

    It is more about scalability in my opinion. Hitting the "database" inside the loop might tank when 10 web users are asking for this at the very same time. Maybe not break until 100 users are hitting it...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: [RESOLVED] Anyway to accomplish this query without UNION?

    Hitting the "database" inside the loop might tank when 10 web users are asking for this at the very same time
    +1 to this. There's an exception to every rule but, as a general principle, you should let the database do what the database does best - query data. A database join should be much faster than a client side loop. Also, it will minimise the network traffic as you won't have to make as many DB calls.


    So the sensible thing to do is to look at why the execution time went up and I suspect the clue might be here:-
    I wonder if this is because I already needed the loop to run through the complex pricing routine for each item being returned
    That sounds like adding the qty to your query hasn't stopped (or reduced) your looping from code, is that right? If so the algorithm can probably still be improved by moving that pricing loop into the DB. Depending on exactly what's going on and the complexity we may be able to build it right into the single query, create a scalar function, create a table valued function or push the whole lot into a sproc. Could you maybe expand on that and/or show us the client side code and the pricing code?

    NB, we'll probably end up with a single call to the DB that's slower than each individual call you currently have but should be quicker than the whole set of current calls and put less burden overall on the DB and network.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    833

    Re: [RESOLVED] Anyway to accomplish this query without UNION?

    Quote Originally Posted by FunkyDexter View Post
    That sounds like adding the qty to your query hasn't stopped (or reduced) your looping from code, is that right? If so the algorithm can probably still be improved by moving that pricing loop into the DB. Depending on exactly what's going on and the complexity we may be able to build it right into the single query, create a scalar function, create a table valued function or push the whole lot into a sproc. Could you maybe expand on that and/or show us the client side code and the pricing code?
    The pricing logic is quite complex. I had thought about moving it to a sproc at some point in the future if I can duplicate the logic with the statements allowed in MySQL, but it's not worth the effort to try at this point. I did do some work to optimize the code so I have removed all the SELECT * to call only those fields actually used and added appropriate indexes on the various tables used. It's much faster than what is running in production now. FWIW, it's less than a second to retrieve a page of results.

    There should be no network traffic as the webserver and database are on the same physical machine. It would also be rare to have more than 5 customers on the server at once.

    Anyway, if you want to have a look at the pricing code, it shouldn't be hard to follow even if you don't know ColdFusion. So it loops through the 25 records of the initial query and uses the component below to calculate the prices before being returned to the client for display.

    Code:
    <cfcomponent>
    	<cffunction name="getPrice" access="public" returntype="array" output="false">
    		<cfargument name="ItemId" type="string" required="true">
    		<cfargument name="CatId" type="string" required="true">
    		<cfargument name="Cost" type="string" required="true">
    		<cfargument name="Uom" type="string" required="true">
    		<cfargument name="EachQty" type="string" required="true">
    		<cfargument name="AvgWgt" type="string" required="true">
    		<cfargument name="PC2" type="string" required="true">
    		<cfargument name="PC3" type="string" required="true">
    		<cfargument name="PC4" type="string" required="true">
    		<cfargument name="PC5" type="string" required="true">
    		<cfargument name="PC6" type="string" required="true">
    		<cfargument name="PC7" type="string" required="true">
    		<cfset PriceArray=ArrayNew(1)>
    
    		<cfset Price = 0>		
    		<cfset Markup = "">
    		<cfset UnitPrice = 0>
    <!--- Get Markup --->		
    		
    <!--- Check for Contract pricing --->
    <cfif #Session.CustCntId# IS NOT 0>  	
    	<cfquery name="rstCtrHdr" datasource=#session.dsndata#>
    	SELECT CntId,EffFrDt,EffToDt
    	FROM CntHdr WHERE CntID = '#Session.CustCntId#'  
    	</cfquery>		
    	<!--- This is a contract customer  -Set Contract Header recordset --->		
       <cfif rstCtrHdr.recordcount EQ 1> 
    	 <!--- Format Date variables --->
    	 <cfset today = DateFormat(Now(),'mm/dd/yy')>	 
    	 <cfset FromDate = Insert("/",#rstCtrHdr.EffFrDt#,2)>	 
    	 <cfset FromDate = Insert("/",#FromDate#,5)>
    	 <cfset ToDate = Insert("/",#rstCtrHdr.EffToDt#,2)>	 
    	 <cfset ToDate = Insert("/",#ToDate#,5)>
    
    	 <!--- check if contract is in effect --->
    	  <cfif #today# GE DateFormat(#FromDate#) AND #today# LE DateFormat(#ToDate#)>
    	  		<!--- contract is in effect -check if item is in Contract --->
    			<cfquery name="rstCtrDtl" datasource=#session.dsndata#>
    			SELECT Price FROM Contract <!--- was SELECT * but only price is used --->
    			WHERE CntId = '#Session.CustCntId#' AND ItemId = '#ItemId#' <!--- added index on itemid --->
    			</cfquery>
    			<cfif rstCtrDtl.recordcount EQ 1>
    				<!--- this item is in the contract --->
    				<cfset markup = "99">
    				<cfset price = rstCtrDtl.Price>
    			<cfelse>
    			    <!--- this item is NOT in the contract --->
    			</cfif>
    	  <cfelse>
    	   		<!--- contract is not in effect --->
    	  </cfif>
    	  </cfif>
    <cfelse>
    		<!--- There is no contract for this customer --->
    		<!--- If no contract pricing, check for Matrix pricing --->		
    </cfif>
    
    <!--- No contract pricing check for Matrix pricing --->
    <cfif #markup# EQ "">
    	<!--- Check at Item Level for a match --->
    	<cfquery name="rstMatrix" datasource=#session.dsndata#>
    		<cfif lcase(Session.dsnctl) NEQ 'ctlmcc'>
    			SELECT Code,CustId,CustSb,ICGid,Mrkup,MrkPct FROM Matrix
    			WHERE CustId = Left('#Session.CustId#',6) AND CustSb = Right('#Session.CustId#',3)
    		<cfelse>
    			SELECT Code,CustId,ICGid,Mrkup,MrkPct FROM Matrix
    			WHERE CustId = '#Session.CustId#'
    		</cfif>
    	 	AND code = 'I' AND icgid = '#ItemId#' <!--- added index on icgid --->
    	</cfquery>
    			
    	<cfif rstMatrix.recordcount EQ 1>
    	    <!--- There is a match at the Item Level -check for Markup Percentage --->
    	    <cfif rstMatrix.MrkPct GT 0>
    	        <cfset price = (((rstMatrix.MrkPct / 100) + 1) * #cost#)>
    		    <cfset markup = "98">
    	    <cfelse>
    	        <cfset markup = rstMatrix.Mrkup>
    	    </cfif>
    	<cfelse>
    		<!--- Check for a match at the Category Level --->
    		<cfquery name="rstMatrix" datasource=#session.dsndata#>
    			<cfif lcase(Session.dsnctl) NEQ 'ctlmcc'>
    				SELECT Code,CustId,CustSb,ICGid,Mrkup,MrkPct FROM Matrix
    				WHERE CustId = Left('#Session.CustId#',6) AND CustSb = Right('#Session.CustId#',3)
    			<cfelse>
    				SELECT Code,CustId,ICGid,Mrkup,MrkPct FROM Matrix
    				WHERE CustId = '#Session.CustId#'
    			</cfif>
    			AND code = 'C' AND icgid = '#CatId#'
    		</cfquery>
    		
    		<cfif rstMatrix.recordcount EQ 1>
    			<!--- There is a match at the Category Level --->
    			<cfset markup = rstMatrix.Mrkup>
    			<script>alert("Hello");</script>
    		<cfelse>
    			<!--- Check for a match at the Group Level --->
    			<cfquery name="rstGrpCat" datasource=#session.dsndata#>
    				SELECT GrpId FROM category WHERE CatId = '#CatId#' <!--- was SELECT * but only GrpId is used --->
    			</cfquery>
    			
    			<cfif rstGrpCat.recordcount EQ 1>			
    				<cfquery name="rstMatrix" datasource=#session.dsndata#>
    					<cfif lcase(Session.dsnctl) NEQ 'ctlmcc'>
    						SELECT Code,CustId,CustSb,ICGid,Mrkup,MrkPct FROM Matrix
    						WHERE CustId = Left('#Session.CustId#',6) AND CustSb = Right('#Session.CustId#',3)
    					<cfelse>
    						SELECT Code,CustId,ICGid,Mrkup,MrkPct FROM Matrix
    						WHERE CustId = '#Session.CustId#'
    					</cfif>
    					AND code = 'G' AND icgid = '#rstGrpCat.GrpId#'
    				</cfquery>
    			</cfif>
    			<cfif rstMatrix.recordcount EQ 1>
    				<!--- There is a match at the Group Level --->
    				<cfset markup = rstMatrix.Mrkup>
    			</cfif>
    		</cfif>
    		
    	</cfif>			
    </cfif>
    
    <!--- No contract or maxtrix pricing, use Customer Markup Code --->
    <cfif #markup# EQ ""> 
    	<!--- Set markup as Customer Default markup ---> 
    	<cfset markup = #Session.Mrkup#>			
    </cfif>
     
    <!--- If price is not determined --->
    <cfif #price# EQ 0>
    	<cfif markup GE 2 AND markup LE 7>
    		<!--- Get pricing from the Item table --->
    		<cfif markup EQ "02">
    			<cfset price = #PC2#>
    			<!--- <cfset price = #rstResults.PC2#> --->
    		</cfif> 
    		
    		<cfif markup EQ "03">
    			<cfset price = #PC3#>
    		</cfif> 
    		
    		<cfif markup EQ "04">
    			<cfset price = #PC4#>
    		</cfif> 
    		
    		<cfif markup EQ "05">
    			<cfset price = #PC5#>
    		</cfif> 
    		
    		<cfif markup EQ "06">
    			<cfset price = #PC6#>
    		</cfif> 
    		
    		<cfif markup EQ "07">
    			<cfset price = #PC7#>
    		</cfif>		
    		 
    	<cfelse>
    	    <cfif markup GE 51 AND markup LE 80>
    			<!--- Calculate price --->
    			<cfquery name="rstMrkup" datasource=#session.dsndata#>
    				SELECT MrkupPct FROM Mrkup WHERE MrkupId = #markup# <!--- was SELECT * but only MrkupPct is used --->
    			</cfquery>
    			
    			<cfif rstMrkup.recordcount EQ 1>				
    			    <cfset price = #cost# * (1 + rstMrkup.MrkupPct)>				
    			</cfif>
    		<cfelse>
    			<!--- Use Markup 02 if not determined --->
    			<cfset markup = "02">
    			<cfset price = #PC2#>
    		</cfif>
    	</cfif>
    </cfif> 
    
    <!--- Set price for a weighted item --->
    <cfif #Uom# EQ "LB">
    	<cfset UnitPrice = #NumberFormat(price, '______.00')#>
    	
    	<cfset price = #price# * #AvgWgt#>
    	
    </cfif>
    	 
    <!--- Get Each Price --->
    <cfset EachPrice = (#price# * 1.3)>
    <cfif #Uom# EQ "CS">
    	<!--- Unit is Case --->
    	<cfif #EachQty# GT 0>
    		<cfset EachPrice =	DecimalFormat((#EachPrice# / #EachQty#))>
    	<cfelse>
    		 <!--- Item is not breakable --->
    		<cfset EachPrice =  0>  <!--- (#EachPrice# / #rstResults.ConvFactor#) --->
    	</cfif> 
    <cfelse>
    	<!--- Unit is Pounds --->
    	<cfif #EachQty# GT 0>
    		<cfset EachPrice =	DecimalFormat((#EachPrice# / #EachQty#))>	
    	<cfelse>
    		<!---  Item is not breakable --->
    		<cfset EachPrice =  0> <!--- (#EachPrice# / #rstResults.ConvFactor#) --->	    
    	</cfif> 
    </cfif>
    		<cfset PriceArray[1] = Round(#Price# * 100) / 100>
    		<cfif NOT #EachPrice# IS 0>
    			<cfset PriceArray[2] = Round(#EachPrice# * 100) / 100>
    		<cfelse>
    			<cfset PriceArray[2] = "&nbsp;">
    		</cfif>
    		<cfif NOT #UnitPrice# IS 0>
    			<cfset PriceArray[3] = Round(#UnitPrice# * 100) / 100>
    		<cfelse>
    			<cfset PriceArray[3] = "&nbsp;">
    		</cfif>
    		<cfset PriceArray[4] = #Markup#>
    		<cfreturn PriceArray>			
    	</cffunction>
    </cfcomponent>

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: [RESOLVED] Anyway to accomplish this query without UNION?

    Honestly, I'm struggling to read that on the forum. I tried cutting and pasting it into notepad but it aint much better. I don't know Cold Fusion at all I'm afraid and I'm also not sure I'm following some of the variables being used (e.g. CustCntId, dsnctl etc) or where their values come from (I suspect they're set outside the function and passed in as parms?).

    At a glance, though, I don't think it should be too difficult to put into SQL though I'm a SQLServer kid rather than MySQL. The two are similar enough so that we should stand a fighting chance if I can follow the algorithm. Ultimately it's just a bunch of conditional selects which we should be able to achieve using case statements.

    Do you reckon you could describe the algorithm in pseudo code and where the values it relies on come from?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    833

    Re: [RESOLVED] Anyway to accomplish this query without UNION?

    All the session variables in this case are set at login. The function parameters passed in are the cfargument lines at the top.

    dsnctl was only used to determine a specific customer of ours so you can ignore it. I discovered when I started testing that one of our clients uses a different table definition so that was my quick fix. Long term I'll make the definition for that table all the same again, but I need to learn why they made it different in the first place. I see I missed some optimization on those queries, too.

    Some pseudo code:
    If cust has a contract, verify contract is in effect (i.e., check start/end dates) and see if this item is included

    If no contract check matrix table at item level first
    If no item level then check category level
    If no category level then check group level

    If no contract or matrix pricing, use the markup code to find price
    If markup >= 2 AND <= 7, this comes from values passed into the function (i.e., PC2-PC7)
    else it pulls value from another table normally.
    If still not found it defaults to PC2

    If item is a weighed item then calculate price using AvgWgt parameter

    Calculate each price (which I can see that I missed an optimization - or made a mistake - as the statements for both cases of that "if... else..." are identical. I'll need to look at that.

    Then round off the values and return.

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: [RESOLVED] Anyway to accomplish this query without UNION?

    OK, here goes:-
    SQL Code:
    1. SELECT  Item.ItemId,
    2.         orderdtl.qty AS Qty,
    3.         Item.Description,
    4.         Item.Brand,
    5.         Item.Pack,
    6.         Item.Cost,
    7.         Item.UOM,
    8.         PC2,
    9.         PC3,
    10.         PC4,
    11.         PC5,
    12.         PC6,
    13.         PC7,
    14.         Item.CatId,
    15.         Item.Ea chQty,
    16.         Item.ConvFactor,
    17.         Item.AvgWgt,
    18.         Rcst,
    19.         Acst,
    20.         Fcst,
    21.         Ocst,
    22.         Pcst,
    23.         Liprop,
    24.         category.description AS catdesc,
    25.         Item.Code1,
    26.         Item.Code2,
    27.         brkitem.itemid AS brkitemid,
    28.         0.00 AS EachPrice,
    29.         Case
    30.             When CntHdr.Price IS NOT NULL then 99
    31.             When Matrix_Item.MrkPct IS NOT NULL then 98
    32.             When Matrix_Category.Mrkup IS NOT NULL then Matrix_Category.Mrkup
    33.             When Matrix_Group.Mrkup IS NOT NULL then Matrix_Group.Mrkup
    34.             else #Session.Mrkup#
    35.         End AS Markup,
    36.         0.00 AS UnitPrice,
    37.         Case
    38.             When CntHdr.Price IS NOT NULL then CntHdr.Price
    39.             When Matrix_Item.MrkPct IS NOT NULL then (((Matrix_Item.MrkPct / 100) + 1) * #cost#)
    40.             When Matrix_Category.Mrkup = 2 then #PC2#
    41.             When Matrix_Category.Mrkup = 3 then #PC3#
    42.             When Matrix_Category.Mrkup = 4 then #PC4#
    43.             When Matrix_Category.Mrkup = 5 then #PC5#
    44.             When Matrix_Category.Mrkup = 6 then #PC6#
    45.             When Matrix_Category.Mrkup = 7 then #PC7#
    46.             When Matrix_Category.Mrkup BETWEEN 51 AND 80 then #cost# * (1 + Mrkup.MrkupPct)
    47.             When Matrix_Group.Mrkup = 2 then #PC2#
    48.             When Matrix_Group.Mrkup = 3 then #PC3#
    49.             When Matrix_Group.Mrkup = 4 then #PC4#
    50.             When Matrix_Group.Mrkup = 5 then #PC5#
    51.             When Matrix_Group.Mrkup = 6 then #PC6#
    52.             When Matrix_Group.Mrkup = 7 then #PC7#
    53.             When Matrix_Group.Mrkup BETWEEN 51 AND 80 then #cost# * (1 + Mrkup.MrkupPct)
    54.         End * Case When #UOM# = 'LB' then #AvgWgt# else 1 end AS Price,
    55.         NULL AS NutInfo,
    56.         NULL AS LastOrdered
    57. FROM Item
    58. INNER JOIN category
    59.     ON Item.CatId = category.catid
    60. LEFT JOIN brkitem
    61.     ON Item.ItemID = brkitem.ItemID
    62. LEFT JOIN orderdtl
    63.     ON Item.itemid = orderdtl.itemid
    64.     AND orderdtl.orderid = 38239
    65.  
    66. --Get the contract header if it's in effect.
    67. --NB This is a slightly odd join becaue it doesn't refer to the
    68. --rest of the tables in the query.  However, since you've got
    69. --'#Session.CustCntId#' we don't need to.
    70. LEFT JOIN CntHdr
    71.     ON cntHdr.CntID = '#Session.CustCntId#'
    72.     AND GetDate() BETWEEN cntHdr.EffFrDt AND cntHdr.EffToDt
    73.  
    74. --Get Matrix at item level if it exists.
    75. --Again, this doesn't join to the rest of the query because
    76. --You've already got the CustId
    77. LEFT JOIN Matrix Matrix_Item
    78.     ON Matrix_Item.CustId = '#Session.CustId#'
    79.     AND Matrix_Item.code = 'I'
    80.     AND Matrix_Item.icgid = Item.ItemId
    81.  
    82. --Get Matrix at Category level if it exists.
    83. --Again, this doesn't join to the rest of the query because
    84. --You've already got the CustId
    85. LEFT JOIN Matrix Matrix_Category
    86.     ON Matrix_Category.CustId = '#Session.CustId#'
    87.     AND Matrix_Category.code = 'C'
    88.     AND Matrix_Category.icgid = Item.CatId
    89.  
    90. --Get Matrix at Group level if it exists.
    91. --Again, this doesn't join to the rest of the query because
    92. --You've already got the CustId.  You're also using the GrpID Variable direct
    93. LEFT JOIN category
    94.     ON category.CatId = '#CatId#'
    95. LEFT JOIN Matrix Matrix_Group
    96.     ON Matrix_Group.CustId = '#Session.CustId#'
    97.     AND Matrix_Group.code = 'G'
    98.     AND Matrix_Group.icgid = category.GrpId
    99.  
    100. --Get the Markup in case we need it.
    101. --NB - again we're joining on a variable but that should be ok
    102. LEFT JOIN Mrkup
    103.     ON MrkupId = #markup#
    104. WHERE Category.CatId LIKE '%'
    105. ORDER BY CatId ASC , ItemId ASC LIMIT 0, 25 ;

    I think I've followed your logic correctly. I didn't tackle UnitPrice and EachPrice yet. These depend on the price calculation which is fairly complex in it's own right and it started to make things look ugly when I was cut and pasting it. I would probably drop the above into a common table expression first so I had the price available and then use a second query to calculate these values. I don't know whether MySQL supports CTEs, though, so I've held off doing it for now.


    The basic principle is that you left join to everything you might need. The first thing that's NOT NULL is the pricing mechanism that will get used. So if it finds a contract price it will use that to calc the price. If it finds an Item Markup in the Matrix it uses that and so on.

    A few points to note:-
    1. This uses TSQL cases etc. I don't know if they convert to MySQL but there will be some equivalent expression I'm sure --edit> Just had a google and it does support cases so this should be fine.
    2. A lot of the time it's joining based on variable you've passed in but ideally I'd like to join to other fields in the database. Ie. I'm sure there's probably some sort of CustomerID field on the order which you should use in place of '#Session.CustId#'. Similarly I wonder if you can get '#Session.CustCntId#' from the order? Note, I've done substitutions for ItemId and CategoryId which should show you what I mean. It's not wrong or inefficient to join on the variables but the fact you're having to implies you've probably retrieved these from other queries so we may be able to remove some more redundant queries.
    Last edited by FunkyDexter; May 30th, 2018 at 03:55 PM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: [RESOLVED] Anyway to accomplish this query without UNION?

    Had a bit of time this morning so I took a look and MySQL does support common table expressions so here's the query with the Unit Price and Each Price calculated:-
    SQL Code:
    1. WITH ctePrePrepare AS
    2. (
    3.     SELECT  Item.ItemId,
    4.             orderdtl.qty AS Qty,
    5.             Item.Description,
    6.             Item.Brand,
    7.             Item.Pack,
    8.             Item.Cost,
    9.             Item.UOM,
    10.             PC2,
    11.             PC3,
    12.             PC4,
    13.             PC5,
    14.             PC6,
    15.             PC7,
    16.             Item.CatId,
    17.             Item.EachQty,
    18.             Item.ConvFactor,
    19.             Item.AvgWgt,
    20.             Rcst,
    21.             Acst,
    22.             Fcst,
    23.             Ocst,
    24.             Pcst,
    25.             Liprop,
    26.             category.description AS catdesc,
    27.             Item.Code1,
    28.             Item.Code2,
    29.             brkitem.itemid AS brkitemid,
    30.             --0.00 AS EachPrice,
    31.             Case
    32.                 When CntHdr.Price IS NOT NULL then 99
    33.                 When Matrix_Item.MrkPct IS NOT NULL then 98
    34.                 When Matrix_Category.Mrkup IS NOT NULL then Matrix_Category.Mrkup
    35.                 When Matrix_Group.Mrkup IS NOT NULL then Matrix_Group.Mrkup
    36.                 else #Session.Mrkup#
    37.             End AS Markup,
    38.             --0.00 AS UnitPrice,
    39.             Case
    40.                 When CntHdr.Price IS NOT NULL then CntHdr.Price
    41.                 When Matrix_Item.MrkPct IS NOT NULL then (((Matrix_Item.MrkPct / 100) + 1) * #cost#)
    42.                 When Matrix_Category.Mrkup = 2 then #PC2#
    43.                 When Matrix_Category.Mrkup = 3 then #PC3#
    44.                 When Matrix_Category.Mrkup = 4 then #PC4#
    45.                 When Matrix_Category.Mrkup = 5 then #PC5#
    46.                 When Matrix_Category.Mrkup = 6 then #PC6#
    47.                 When Matrix_Category.Mrkup = 7 then #PC7#
    48.                 When Matrix_Category.Mrkup BETWEEN 51 AND 80 then #cost# * (1 + Mrkup.MrkupPct)
    49.                 When Matrix_Group.Mrkup = 2 then #PC2#
    50.                 When Matrix_Group.Mrkup = 3 then #PC3#
    51.                 When Matrix_Group.Mrkup = 4 then #PC4#
    52.                 When Matrix_Group.Mrkup = 5 then #PC5#
    53.                 When Matrix_Group.Mrkup = 6 then #PC6#
    54.                 When Matrix_Group.Mrkup = 7 then #PC7#
    55.                 When Matrix_Group.Mrkup BETWEEN 51 AND 80 then #cost# * (1 + Mrkup.MrkupPct)
    56.             End * Case When #UOM# = 'LB' then #AvgWgt# else 1 end AS Price,
    57.             NULL AS NutInfo,
    58.             NULL AS LastOrdered
    59.     FROM Item
    60.     INNER JOIN category
    61.         ON Item.CatId = category.catid
    62.     LEFT JOIN brkitem
    63.         ON Item.ItemID = brkitem.ItemID
    64.     LEFT JOIN orderdtl
    65.         ON Item.itemid = orderdtl.itemid
    66.         AND orderdtl.orderid = 38239
    67.  
    68.     --Get the contract header if it's in effect.
    69.     --NB This is a slightly odd join becaue it doesn't refer to the
    70.     --rest of the tables in the query.  However, since you've got
    71.     --'#Session.CustCntId#' we don't need to.
    72.     LEFT JOIN CntHdr
    73.         ON cntHdr.CntID = '#Session.CustCntId#'
    74.         AND GetDate() BETWEEN cntHdr.EffFrDt AND cntHdr.EffToDt
    75.  
    76.     --Get Matrix at item level if it exists.
    77.     --Again, this doesn't join to the rest of the query because
    78.     --You've already got the CustId
    79.     LEFT JOIN Matrix Matrix_Item
    80.         ON Matrix_Item.CustId = '#Session.CustId#'
    81.         AND Matrix_Item.code = 'I'
    82.         AND Matrix_Item.icgid = Item.ItemId
    83.  
    84.     --Get Matrix at Category level if it exists.
    85.     --Again, this doesn't join to the rest of the query because
    86.     --You've already got the CustId
    87.     LEFT JOIN Matrix Matrix_Category
    88.         ON Matrix_Category.CustId = '#Session.CustId#'
    89.         AND Matrix_Category.code = 'C'
    90.         AND Matrix_Category.icgid = Item.CatId
    91.  
    92.     --Get Matrix at Group level if it exists.
    93.     --Again, this doesn't join to the rest of the query because
    94.     --You've already got the CustId.  You're also using the GrpID Variable direct
    95.     LEFT JOIN category
    96.         ON category.CatId = '#CatId#'
    97.     LEFT JOIN Matrix Matrix_Group
    98.         ON Matrix_Group.CustId = '#Session.CustId#'
    99.         AND Matrix_Group.code = 'G'
    100.         AND Matrix_Group.icgid = category.GrpId
    101.  
    102.     --Get the Markup in case we need it.
    103.     --NB - again we're joining on a variable but that should be ok
    104.     LEFT JOIN Mrkup
    105.         ON MrkupId = #markup#
    106.     WHERE Category.CatId LIKE '%'
    107. )
    108. SELECT  ItemId,
    109.         Qty,
    110.         Description,
    111.         Brand,
    112.         Pack,
    113.         Cost,
    114.         UOM,
    115.         PC2,
    116.         PC3,
    117.         PC4,
    118.         PC5,
    119.         PC6,
    120.         PC7,
    121.         CatId,
    122.         EachQty,
    123.         ConvFactor,
    124.         AvgWgt,
    125.         Rcst,
    126.         Acst,
    127.         Fcst,
    128.         Ocst,
    129.         Pcst,
    130.         Liprop,
    131.         catdesc,
    132.         Code1,
    133.         Code2,
    134.         brkitemid,
    135.         Case
    136.             When EachQty > 0 then FORMAT(Price * 1.3 / EachQty)
    137.             Else 0
    138.         End AS EachPrice,
    139.         Markup,
    140.         Format(Price, 2) AS UnitPrice,
    141.         Price,
    142.         NutInfo,
    143.         LastOrdered
    144. ORDER BY CatId ASC , ItemId ASC LIMIT 0, 25 ;

    I believe I've followed your logic correctly but check it. Also, I don't have MySQL so can't syntax check this. I've tried to follow examples from Google but don't be surprised if a few errors have crept in. I've taken a "quick and dirty" approach here by just embedding your logic but we could probably do a bit to make it ore readable if you wanted. ie we could have a cte that just calculated the prices and then join to it in the main query rather than putting the main query in a cte and then calculating the Unit and Each Prices.

    Other than that I believe this should work as a single query which should be much faster than a client side loop. And I haven't done any performance tuning on it but there's probably some steps we could take to speed it up further if needed.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.