MS Query problem

[henio]

Hi all,

I'm not sure if this is the correct forum, but as you are all multi-skilled...

I am using MS Query from Excel to extract data from Oracle tables via the MS Oracle ODBC driver. The following (stripped down) query returns the correct records and values. The item units_pakd/std_pack_qty is a calculated field based on two items from the two tables concerned. This is working correctly. However, I only want to return 1 summary record with this calculated item totalled.


SELECT MANH_CARTON_DTL_INQRY.CARTON_NBR, units_pakd/std_pack_qty
FROM PKMS1.ITEM_MASTER ITEM_MASTER, PKMS1.MANH_CARTON_DTL_INQRY MANH_CARTON_DTL_INQRY
WHERE ITEM_MASTER.SKU_ID = MANH_CARTON_DTL_INQRY.SKU_ID AND ((MANH_CARTON_DTL_INQRY.CARTON_NBR='9035443'))

If I add the following:

order by MANH_CARTON_DTL_INQRY.CARTON_NBR
group by MANH_CARTON_DTL_INQRY.CARTON_NBR

I get the following error:

Didn't expect 'group' after the column list in the ORDER BY clause.

What am I doing wrong?

I'd prefer to do this rather than return all the records into a pivottable because the volumes could be very large.

Thanks,
Henio

 

[MakeItSo]

If you group by, you need a HAVING clause instead of a WHERE clause.
Hit F1 and check the help file for "GROUP BY". You'll get the entire syntax there.

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[henio]

Makeitso,

thanks for the quick reply. There's no syntax help in MSQuery (at least in my version). Could you please cut and paste it here?

Cheers,
Henio

 

[MakeItSo]

hi Henio!

MY post was not fully correct.
You can have both, WHERE and HAVING:
e.g. [blue]
SELECT field1, SUM(field2) as Total, COUNT(field3) as Count3
FROM table
WHERE field1 >= 100
GROUP BY field1, field2, field3
HAVING SUM(field2) > 50;
[/blue]

PLUS: an ORDER BY clause is always at the end. So: swapping your Group by and ORDER by should already do the trick.

Greetings,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[henio]

Hi Makeitso,

I got this to work...

SELECT MANH_CARTON_DTL_INQRY.CARTON_NBR, Sum(units_pakd/std_pack_qty)
FROM PKMS1.ITEM_MASTER ITEM_MASTER, PKMS1.MANH_CARTON_DTL_INQRY MANH_CARTON_DTL_INQRY
WHERE ITEM_MASTER.SKU_ID = MANH_CARTON_DTL_INQRY.SKU_ID AND ((MANH_CARTON_DTL_INQRY.CARTON_NBR='9035443'))
GROUP BY MANH_CARTON_DTL_INQRY.CARTON_NBR

I only needed the SUM around my calculated field and the GROUP BY clause.

Thanks for your help.

Henio

 

[MakeItSo]

de nada! ;-)

 

[henio]

OK,

my original posting contained a cut-down query for the sake of clarity. I've merged the responses in with another query and I now get the wrong answer :-(

I pasted this into MSQuery:

SELECT PKT_HDR.STORE_NBR,
PKT_HDR.SHIPTO_NAME,
PKT_HDR.STORE_TYPE,
CARTON_HDR.CARTON_TYPE,
CARTON_HDR.CARTON_NBR,
Sum(PKT_DTL.UNITS_PAKD/ITEM_MASTER.STD_PACK_QTY),
PKT_DTL.IN_STORE_DATE,
LOCN_HDR.DSP_LOCN

FROM CARTON_HDR,
ITEM_MASTER,
LOCN_HDR,
PKT_DTL,
PKT_HDR

WHERE PKT_HDR.PKT_CTRL_NBR = CARTON_HDR.PKT_CTRL_NBR
AND PKT_HDR.PKT_CTRL_NBR = PKT_DTL.PKT_CTRL_NBR
AND CARTON_HDR.CURR_LOCN_ID = LOCN_HDR.LOCN_ID
AND PKT_DTL.SKU_ID = ITEM_MASTER.SKU_ID
AND ((CARTON_HDR.STAT_CODE=20))

GROUP BY CARTON_HDR.CARTON_NBR,
PKT_HDR.STORE_NBR,
PKT_HDR.SHIPTO_NAME,
PKT_HDR.STORE_TYPE,
CARTON_HDR.CARTON_TYPE,
PKT_DTL.IN_STORE_DATE,
LOCN_HDR.DSP_LOCN

ORDER BY PKT_HDR.STORE_NBR

I hoped that this would subtotal on the field CARTON_HDR.CARTON_NBR

But for some reason, MSQuery converts this into:

SELECT PKT_HDR.STORE_NBR,
PKT_HDR.SHIPTO_NAME,
PKT_HDR.STORE_TYPE,
CARTON_HDR.CARTON_TYPE,
CARTON_HDR.CARTON_NBR,
Sum(PKT_DTL.UNITS_PAKD/ITEM_MASTER.STD_PACK_QTY),
PKT_DTL.IN_STORE_DATE,
LOCN_HDR.DSP_LOCN

FROM PKMS1.CARTON_HDR CARTON_HDR,
PKMS1.ITEM_MASTER ITEM_MASTER,
PKMS1.LOCN_HDR LOCN_HDR,
PKMS1.PKT_DTL PKT_DTL,
PKMS1.PKT_HDR PKT_HDR

WHERE PKT_HDR.PKT_CTRL_NBR = CARTON_HDR.PKT_CTRL_NBR
AND PKT_HDR.PKT_CTRL_NBR = PKT_DTL.PKT_CTRL_NBR
AND CARTON_HDR.CURR_LOCN_ID = LOCN_HDR.LOCN_ID
AND PKT_DTL.SKU_ID = ITEM_MASTER.SKU_ID AND
((CARTON_HDR.STAT_CODE=20))

GROUP BY PKT_HDR.STORE_NBR,
PKT_HDR.SHIPTO_NAME,
PKT_HDR.STORE_TYPE,
CARTON_HDR.CARTON_TYPE,
CARTON_HDR.CARTON_NBR,
PKT_DTL.IN_STORE_DATE,
LOCN_HDR.DSP_LOCN

ORDER BY PKT_HDR.STORE_NBR

In other words, it changes the first group by field name... This is presumably why it subtotals on this field (PKT_HDR.STORE_NBR) rather than CARTON_HDR.CARTON_NBR

Any insights would be greatly appreciated.

Cheers,
Henio

 

[MakeItSo]

Looks like MSQUery has done nothing but to sort the fields by table. As long as this is not done in the Select part, it has absolutely no effect. In the SELECT part, it would affect the order of the output fields.

There is no relationship between your tables, so the output might be erratic.

Do you have matching fields in your tables? e.g Store_NBR or Carton_NBR or any other identifier you want to group by?

If yes: create a one to many relationship between those tables. Then Access will "know" which fields go together.

Hope this helps,
Andy

 

[henio]

Makeitso,

the WHERE clause links the tables, and yes, there are matching fields which are used in the WHERE clause. I just don't understand how to force MSQuery to subtotal on a particular field.

Cheers,
Henio

 

[MakeItSo]

Hmm - I don't have MSQuery installed on my machine.
But one practical solution would be to create the query in MS Access query designer.
There, use the Sigma in the toolbar to group the fields.
As default, all fields will be set to GROUP, so just select "SUM" for the field to be subtotaled.

Having done this, you can switch to SQL-View and copy/paste the statement into MSQuery, if you'd like.

Hope this helps you,
Andy

 

[cheerio]

A few observations:

Firstly, MS Query is simply passing your query to the ODBC driver and picking up the response. The SQL syntax to be used is specific to ODBC and not to MS Query. Other applications that use ODBC would use the same syntax. That's why MS Query help is not available. However, ODBC syntax is fairly standard SQL.

Secondly, it's a really bad idea to join tables using the WHERE clause. There is a real risk that you will create all the rows defined by the cross join of all the tables and then filter down with the WHERE clause. You could be building and dropping millions of rows. You really should use the JOIN .... ON .... construction. What the Oracle ODBC driver is going to do is take your ODBC SQL and translate it to ORACLE SQL so it is sensible to start with sound code.

Thirdly, if you have a construction:
SELECT fieldA, fieldB, fieldC, SUM(fieldD)
FROM ...
GROUP BY fieldA, fieldB, fieldC

then what you are doing is selecting all used combinations of fieldA, fieldB, fieldC and deriving a total for fieldD for each such combination.

Once you have done that you are not going to be able to derive other totals (eg just on field A) just by sorting.