Still may need sub query

Hi Harrzack,


If I understand this correctly, you're happy with the query so far, and just need to add ITCSPK to each line.  (Any computations using ITEMQTY and ITCSPK is trivial once the data is associated with the row.)

Several different SQL structures/formats will do this.  Perhaps the easiest one is to join WITMB into the current result set, presumably on the SKU.  You've already done that, so it should be just a simple matter of including that value on the list of items selected.

It's interesting that you group by t2.IPORDN, but don't actually select the value of IPORDN.  You'll need to add ITCSPK to the GROUP BY clause.

Try the query below and see if that doesn't come close.  Then just modify it to compute the value that you need.


Good Luck,
Kent

SELECT MAX(t3.POORDN) AS ORDNUM,
MAX(t3.POORTY) AS ORDTYP,
MAX(t3.POOPRI) AS ORDPRI,
DECIMAL(COUNT(t2.ipordn),7,0) AS LINES,
SUM(t2.IPORDQ) AS EACHQTY,
t1.ITCSPK as ITEMSPERCASE,
MAX(t3.POGWGT) AS GROSWT,
MAX(t3.POGVOL) AS GROVOL,
MAX(t3.POGVOL)/61440 AS ESTPAL,
from hbcprodfl.witmb as t1
JOIN hbcprodfl.wopib AS t2 on T2.IPSKU = T1.ITSKU
JOIN HBCPRODFL.WOPHB AS t3 ON t2.IPORDN = t3.POORDN
WHERE t1.ITCSPK > 0
GROUP BY t2.IPORDN, t1.ITCSPK
ORDER BY t2.ipordn

Select allOpen in new window

Hmm - I knew this would be a tough one to explain.

Lets say an order has 3 items, and each SKU has a different items-per-case reference value.

Item1  QTYORD = 150   Items per case = 9
Item 2 QTYORD = 250   Items per case = 12
Item 3 QTYORD - 100   Items per case = 8

I have to show the number of full cases needed, and then the reminder which are 'non-case-lot' numbers and are picked separately.  So for this order I need to see:

TOTQTY = 500
TOTCASES = 48  /*(INT(150/9) = 16) + (INT(250/12 = 20) + (INT(100/8 = 12) */
TOTEACH = 20  /* 150-(16*9) = 6) + (250-(20*12) = 10) + (100-(12*8) = 4 */

For each item in the order, these thee values must be produced - as a column each. The report is one record per order-number.

I've left out a number of columns from the sample code - but all the basic elements are there.

Ok.  Still not tough, but apparently my understanding is.  :)

I believe that EACHQTY is the total number of any SKU being ordered.  If so, the query below should be pretty close....


Kent

SELECT MAX(t3.POORDN) AS ORDNUM,
MAX(t3.POORTY) AS ORDTYP,
MAX(t3.POOPRI) AS ORDPRI,
DECIMAL(COUNT(t2.ipordn),7,0) AS LINES,
SUM(t2.IPORDQ) AS EACHQTY,
--  Adding these three lines to your query
t1.ITCSPK as ITEMSPERCASE,
SUM(t2.IPORDQ) / t1.TICSPK as FULLCASES,
mod(SUM(t2.IPORDQ), T1.TICKSPK) as NONCASECOUNT,
--
MAX(t3.POGWGT) AS GROSWT,
MAX(t3.POGVOL) AS GROVOL,
MAX(t3.POGVOL)/61440 AS ESTPAL,
from hbcprodfl.witmb as t1
JOIN hbcprodfl.wopib AS t2 on T2.IPSKU = T1.ITSKU
JOIN HBCPRODFL.WOPHB AS t3 ON t2.IPORDN = t3.POORDN
WHERE t1.ITCSPK > 0
GROUP BY t2.IPORDN,
-- Adding these three lines to complete the GROUP BY requirement
t1.ITCSPK,
SUM(t2.IPORDQ) / t1.TICSPK,
mod(SUM(t2.IPORDQ), T1.TICKSPK)
--
ORDER BY t2.ipordn

Select allOpen in new window

Looks good!  - i wasn't  sure how to handle the GROUP-BY stuff - I'll try your code in the morning (EDT).
GROUP-BY still has me a bit flummoxed, but getting there!  :-)

Tks - I'll be back!

It might make more "sense" to structure the query like shown below.  The explain plan will look cleaner, but it probably takes longer to execute.  If your original query runs fairly quickly, give it a try.


Kent

WITH T (ORDNUM, ORDTYP, ORDPRI, LINES, EACHQTY, ITEMSPERCASE, GROSWT, GROVOL, ESTPAL)
AS
(
SELECT MAX(t3.POORDN) AS ORDNUM,
  MAX(t3.POORTY) AS ORDTYP,
  MAX(t3.POOPRI) AS ORDPRI,
  DECIMAL(COUNT(t2.ipordn),7,0) AS LINES,
  SUM(t2.IPORDQ) AS EACHQTY,
  t1.ITCSPK as ITEMSPERCASE,
  MAX(t3.POGWGT) AS GROSWT,
  MAX(t3.POGVOL) AS GROVOL,
  MAX(t3.POGVOL)/61440 AS ESTPAL,
FROM hbcprodfl.witmb as t1
JOIN hbcprodfl.wopib AS t2 
  ON T2.IPSKU = T1.ITSKU
JOIN HBCPRODFL.WOPHB AS t3 
  ON t2.IPORDN = t3.POORDN
WHERE t1.ITCSPK > 0
GROUP BY t2.IPORDN
)
SELECT *, EACHQTY / ITEMSPERCASE as FULLCASES, MOD (EACHQTY, ITEMSPERCASE) as NONCASECOUNT
FROM T0
ORDER BY LINES

Select allOpen in new window

Thanks for the alternative solution. I'll be trying them shortly.

I seems that DB2 on the AS400 has been kinda picky about synonyms and alias  - it complains they are "not in table xxxx"

That's a quirk of DB2.  Synonyms don't apply within a subquery, but are carried out to the next level of query.

 
  SELECT id AS key_value
  FROM mytable
  ORDER BY key_value;

The query above is illegal.  *key_value* will appear on the column_header of the client because it is carried out of the query, but it is not legal within the query because the subquery uses the true name.

  SELECT id AS key_value
  FROM mytable
  ORDER BY id;

That query is legal, though not necessarily intuitive.


Kent

Ok - we are getting there, but now the hard part starts!   :-)

Your added code has produced the right values, but it is creating a line for each line in the order. This report has only ONE line per order. Each of these three calculations are combined for a single value for ITEMS,CASES, and EACHES.

The code snippet shows how I edited it to run - with the SUM in the group-by, DB2 complained.
 I'm searching on 1 order for testing. The intermediate query from Query/400 shows, for this order, 5 orderlines, 466 items, 59 cases and 12 'eaches'

I'm trying to recreate the query so it can be ran from an Access 2003 app, (via ODBC) and the end user can run his own reports as needed. But I HAVE to match the existing results.

I'm testing with Universal Query Editor via an ODBC connection.

SELECT MAX(t3.POORDN) AS ORDNUM,
MAX(t3.POORTY) AS ORDTYP,
MAX(t3.POOPRI) AS ORDPRI,
DECIMAL(COUNT(t2.ipordn),7,0) AS LINES,
SUM(t2.IPORDQ) AS EACHQTY,
 
SUM(t1.ITCSPK) as ITEMSPERCASE,
sum(SUM(t2.IPORDQ) / t1.ITCSPK) as FULLCASES,
SUM(mod(SUM(t2.IPORDQ), T1.ITCSPK)) as EACHCOUNT
 
from hbcprodfl.witmb as t1
JOIN hbcprodfl.wopib AS t2 on T2.IPSKU = T1.ITSKU
JOIN HBCPRODFL.WOPHB AS t3 ON t2.IPORDN = t3.POORDN
WHERE t1.ITCSPK > 0 AND t2.IPORDN = '7460816701'
GROUP BY t2.IPORDN,t2.IPORDQ,t1.ITCSPK

Select allOpen in new window

Ok.  How about showing a few lines from what the query displays, and an example of what you want?

Ok - here is how it stands now:

Result of current version:

ORDNUM     ORDTYP ORDPRI     LINES         EACHQTY ITEMSPERCASE      FULLCASES EACHCOUNT
---------- ------ ------ --------- ----------------------------------------- ------------ ----------------------------------- ---------
7460816701 STD    50             2         16            1             8       0
7460816701 STD    50             1         150           9            16       6
7460816701 STD    50             1         150           10           15       0
7460816701 STD    50             1         150           12           12       6
---------------------------------------------------------------------
The query =should= produce one line per order:

7460816701 STD    50             5         422           n/a           51      12

Also - the first line produced by this query is combing the first line - there are actually 5 line-items in the sample order.

It "feels" like, somehow, each line-item on an order has to be iterated thru and a total returned - would that be a correlated sub-query?

We can combine them.  :)  It's just a matter of doing another sum/group by of these results.  :)  Sometimes SQL gets to looking rather ugly and recursive, and we may be venturing into that arena.

Was this produced from the last SQL that you posted?  If not, please post the updated SQL.


Kent

Ok.  I studied this a bit more and wrapping the query should suffice.  Because we need the intermediate results to produce numbers by line and then manipulating them, we can't use one simple query.

The code below should work fine.  Note that if there are otherwise duplicate rows with different values for ORDTYP or ORDPRI, the report will have multiple lines.  You can get around that by selecting MIN() or MAX() of either of these in the outer query and removing the item from the GROUP BY clause.


Kent

--
SELECT
  ordnum,
  ordtyp,
  ordpri,
  SUM (lines),
  SUM (eachqty),
  'n/a',
  sum (fullcases),
  sum (eachcount)
FROM
(
--  Insert your query here:
  SELECT
    MAX(t3.POORDN) AS ORDNUM,
    MAX(t3.POORTY) AS ORDTYP,
    MAX(t3.POOPRI) AS ORDPRI,
    DECIMAL(COUNT(t2.ipordn),7,0) AS LINES,
    SUM(t2.IPORDQ) AS EACHQTY,
 
    SUM(t1.ITCSPK) as ITEMSPERCASE,
    sum(SUM(t2.IPORDQ) / t1.ITCSPK) as FULLCASES,
    SUM(mod(SUM(t2.IPORDQ), T1.ITCSPK)) as EACHCOUNT
 
  from hbcprodfl.witmb as t1
  JOIN hbcprodfl.wopib AS t2
    on T2.IPSKU = T1.ITSKU
  JOIN HBCPRODFL.WOPHB AS t3
    ON t2.IPORDN = t3.POORDN
  WHERE t1.ITCSPK > 0
    AND t2.IPORDN = '7460816701'
  GROUP BY t2.IPORDN,t2.IPORDQ,t1.ITCSPK
--
) t0
GROUP BY
  ordnum,
  ordtyp,
  ordpri

Select allOpen in new window

Yes - my example was from the last of your suggestions. I'll try this one now.
Tks for all the help!

I'm checking my understanding of this:

I see an "outer query" and an "inner query". Is the inner query - the SELECT in the FROM - doing the iteration - like 'for each item of order' ? A goodly number of the columns in this query are single- value, and can be pulled out with a MAX function. Is the idea to have all these in the outer query, and the iterated, calculated values in the inner query?

=Alan R.

Ok - things are starting to look GOOD!

This query returns a total of 29 columns. Only 5 are 'calculated' - the majority are pulled from the order header - name, address, etc.  It appears the entire query is included in the "inner" query, with their synonyms listed in the outer query, and in GROUP BY clause.

What is so confusing about GB - is just what is getting grouped!  Mainly the query is to be grouped by the Order number. I understand everything in the query has to be in the GB statement at the end. I have the order-number as the first item in the GB clause, and it is working. What happens to the other items? I'm assuming I'll have to have all 29 columns in the final GB statement/clause

Kent  - Mission accomplished!   It looks like I'm getting all values back, in the format expected.

As you say, it makes for some 'wordy' SQL, but it IS much faster than my early attempts. I pulling in about 3500 records (out of about 18,000 or so) in around 5 secs.

I'll save your last over view, but this job is "IN THE CAN" - it's a wrap - lets go home!

Thanks for hanging in with me - I've been beating on this thing for well over a month. Now to get it working in Access 2003... but that is a story for another day.  :-)

=Alan R.

And to think that I'm doing all this for points.  I should be holding out for beer.   ;)


Glad that it's working!  Instead of getting it working in Access I'd suggest convincing management that using Access is a mistake.  A real DBMS is soooooo much nicer....

Well - my fav 4GL system is one you've prob never heard of: Omnis Studio. Now that package ROCKS! Sad to say, while it is still alive and well, it never 'caught on' and has been beaten out by more well known pgms like Access, Filemaker (UGH!), and way back - Powerbuilder.

Nobody wants to spend any money here - I'm in an operators position with a LOT of free time, and Acess 2003 is available, so I'm trying to learn it and ADO (All pretty ham-fisted if you ask me), and maybe show the folks here just what is possible.  Maybe if I hit a few home runs with Access, they will take note and it will open the door for something better.

I'd be glad to mail you a beer if I could - come to think of it - I may have to stop off for a couple on the way home to celebrate!    :-)

There's a group in California with a product called 'Aster Data' (I think) that is supposed to be phenomenally faster than anything else on the market today on large datasets.  I'm keeping an eye on them, but haven't heard much in a while.

The thing that I'm most curious about is a concept of storing data by column, not by row.  It's a radical approach to data storage (we're taught from our very first program that everything is a line, record, or row) but apparently has some pretty big performance advantages over conventional databases.  It's likely not applicable in your environment.  Still, if you see an article on it, give it a read.  It should be fun.


kent