[RESOLVED] SQL and top 20 items in a table

[gollnick]

I have an inventory cycle count file which may contain several thousand records. I am trying to get the TOP 20 items (based on qty_adjusted value) and return only those to a dataset.

I am trying to use the TOP 20 statement but am getting an Invalid Token error at execution time.


SELECT TOP 20 di3.Qty_A00001 +
FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
ORDER BY di3.Qty_A00001 DESC"

Also tried


SELECT * +
FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
ORDER BY di3.Qty_A00001 DESC LIMIT 20"

Neither works... Where am I going wrong here.... Oh .... also there may be duplicated "high" adjustments so I need to figure those into the top 20.

thank you

gollnick

[GaryMazzone]

What is the database backend? What do the tables look like?

[gollnick]

It is a DB2 (iseries) using the IBMDB2400 client using SQL to retreive my data.
Not sure what you mean by what do my tables look like. Just ordinary tables.

[GaryMazzone]

I think like this in DB2:

SELECT di3.Qty_A00001 +
FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
ORDER BY di3.Qty_A00001 DESC +
FETCH FIRST 20 ROWS ONLY

[gollnick]

I will try that but if I have multiples say

88 82 80 75 75 75 60 55 54 52 50 45 40 42 40 38 38 38 37 35

as my first high order rows ..... I do not wat to count those dulpicate 75's or 38's as a total aggrigate to my 20.... so in the above data set (based on field qty_A00001) i would have only gotten my top 16 .... understand?? This may not even be possible

thanks

gollnick

[GaryMazzone]

What about doing something like this:

SELECT di3.Qty_A00001,COUNT(di3.Qty_A00001) +
FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
GROUP BY di3.Qty_A00001 +
ORDER BY di3.Qty_A00001 DESC +
FETCH FIRST 20 ROWS ONLY

Or
SELECT DISTINCT di3.Qty_A00001 +
FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
ORDER BY di3.Qty_A00001 DESC +
FETCH FIRST 20 ROWS ONLY

[gollnick]

I will try both of those when I get back to the office in the morning. Had to leave for the day already. Will post back on ya or naa... Thanks

gollnick

[gollnick]

Hello.. this method worked somewhat

SELECT di3.Qty_A00001,COUNT(di3.Qty_A00001) +
FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
GROUP BY di3.Qty_A00001 +
ORDER BY di3.Qty_A00001 DESC +
FETCH FIRST 20 ROWS ONLY



However ..... the only column brought into the dataset is the qty_A00001.... I need a lot of other columns and if I try and define those I get an invalid token .. from or into expected..

Anything else I missed?

thank you

gollnick

[GaryMazzone]

Every column not in the aggregate ( COUNT,SUM,MAX,MIN,AVG) needs to be in the group by.

If you need other columns then the question becomes different as does the solution. What do you actually need. What columns from what tables?

[gollnick]

Hi .. I need at a minimum these additional columns from the tables

di3.item_Part di1.item_Desc di3.Item_loct di3.Item_PkDate

If I see how on these I can figure the rest

Thank you

gollnick

[GaryMazzone]

Try something like this:

Select di3.item_Part di1.item_Desc di3.Item_loct di3.Item_PkDate +
FROM FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
WHERE di3.Qty_A00001 IN ( +
SELECT DISTINCT di3.Qty_A00001 +
FROM Dobsojm1.Item_00003 di3 +
JOIN Dobsojm1.ITEM_00001 di1 +
ON di3.ITEM_00001 = di1.ITEM_00001 +
ORDER BY di3.Qty_A00001 DESC +
FETCH FIRST 20 ROWS ONLY )

[gollnick]

I built the command string this way.......


" Select di3.item_00001 di1.item_00002 di3.Item_00003 di3.Last_00001 FROM FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 WHERE di3.Qty_A00001 IN ( SELECT DISTINCT di3.Qty_A00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 ORDER BY di3.Qty_A00001 DESC FETCH FIRST 40 ROWS ONLY )"


This is the generated error at excute time
SQL0104 Token . was not valid. Valid tokens: , FROM INTO.

Very confused

gollnick

[GaryMazzone]

From is listed twice FROM FROM Dobsojm1.Item_00003 di3 JOIN

Missing all the commas between the fields in the select statement

[gollnick]

Sorry......

Did those changes...... now I have the string

" Select di3.item_00001 , di1.item_00002 , di3.Item_00003 , di3.Last_00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 WHERE di3.Qty_A00001 IN ( SELECT DISTINCT di3.Qty_A00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 ORDER BY di3.Qty_A00001 DESC FETCH FIRST 40 ROWS ONLY )"

Now I get the exception thrown

SQL0199 Keyword ORDER not expected. Valid tokens: ) UNION EXCEPT

Sorry I'm slow on this. Simple sql statements I can handle.. This is something new to me.

gollnick

[GaryMazzone]

I don't have DB2. I would try the Inner statement first just see if it runs and gets you the info you want. If that does then We go on from there. There are is another option (at least in SQL Server) I could use also. That would be treating the select that is use for the In statement as a dirived table and performing an INNER JOIN on that also.

[gollnick]

I've tried..... this is from the sql manager script create .. so DB2 does not really run into play

Connected to relational database Uitasc3 on Uitasc3.electrolux-na.com as Gollnwe1 - 767074/Quser/Qzdasoinit

> Select di3.item_00001 , di1.item_00002 , di3.Item_00003 , di3.Last_00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 WHERE di3.Qty_A00001 > 0 IN ( SELECT DISTINCT di3.Qty_A00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 ORDER BY di3.Qty_A00001 DESC FETCH FIRST 40 ROWS ONLY )

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword IN not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. Cause . . . . . : The keyword IN was not expected here. A syntax error was detected at keyword IN. The partial list of valid tokens is FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

Processing ended because the highlighted statement did not complete successfully


gollnick

[GaryMazzone]

This is worng

Select di3.item_00001 , di1.item_00002 , di3.Item_00003 , di3.Last_00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 WHERE di3.Qty_A00001 > 0 IN ( SELECT DISTINCT di3.Qty_A00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 ORDER BY di3.Qty_A00001 DESC FETCH FIRST 40 ROWS ONLY )



Look at the where Clause:
WHERE di3.Qty_A00001 > 0 IN

That is not valid Remove the > 0 before the IN or write it like this:
WHERE di3.Qty_A00001 > 0 AND di3.Qty_A00001 IN

[gollnick]

Did that .. got further..


> Select di3.item_00001 , di1.item_00002 , di3.Item_00003 , di3.Last_00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 AND di3.ITEM_TYPE = di1.packa00001 WHERE di3.Qty_A00001 > 0 AND di3.Qty_A00001 IN ( SELECT DISTINCT di3.Qty_A00001 FROM Dobsojm1.Item_00003 di3 JOIN Dobsojm1.ITEM_00001 di1 ON di3.ITEM_00001 = di1.ITEM_00001 ORDER BY di3.Qty_A00001 DESC FETCH FIRST 40 ROWS ONLY )

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword ORDER not expected. Valid tokens: ) UNION EXCEPT. Cause . . . . . : The keyword ORDER was not expected here. A syntax error was detected at keyword ORDER. The partial list of valid tokens is ) UNION EXCEPT. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.


gollnick

[GaryMazzone]

What version of DB2? the FETCH FIRST FETCH FIRST * ROWS ONLY is version 8 and above only.

[gollnick]

I do not think that is the issue.. This works fine


SELECT *
FROM Dobsojm1.Item_00003 di3
JOIN Dobsojm1.ITEM_00001 di1
ON di3.ITEM_00001 = di1.ITEM_00001 AND di3.ITEM_TYPE = di1.packa00001
Where di3.Last_00001 = '2011-01-04'
ORDER BY di3.Qty_A00001 DESC
FETCH FIRST 40 ROWS ONLY

gollnick

[GaryMazzone]

So lets try it as a drivived table with an Inner Join then

Select di3.item_00001 , di1.item_00002 , di3.Item_00003 , di3.Last_00001 \
FROM Dobsojm1.Item_00003 di3 
JOIN Dobsojm1.ITEM_00001 di1 
    ON di3.ITEM_00001 = di1.ITEM_00001 AND di3.ITEM_TYPE = di1.packa00001 
INNER JOIN ( SELECT DISTINCT di3.Qty_A00001 
                FROM Dobsojm1.Item_00003 di3 
                JOIN Dobsojm1.ITEM_00001 di1 
                    ON di3.ITEM_00001 =         di1.ITEM_00001 
                ORDER BY di3.Qty_A00001 DESC 
                FETCH FIRST 40 ROWS ONLY ) myInner 
    on MyInner.Qty_A00001 = di3.Qty_A00001 
WHERE di3.Qty_A00001 > 0

[leinad31]

The sorting is additional overhead (you need to return entire resultset of join before sorting can be done )... all preceding approaches to problem is not scalable with table data growth. Is the join necessary to get first 20 rows, or are there instances where di3.ITEM_00001 = di1.ITEM_00001 will not be satisfied (key doesn't exist in other table)?

What are the existing indexes? And is Qty_A00001 update/delete transaction heavy?

Related reference http://publib.boulder.ibm.com/infoce...2z_indexes.htm

[gollnick]

YES..... that worked ... thank you. I can build on this from here...

Thanks

gollnick

[GaryMazzone]

OK... If this resolves your issue please do us a favor and mark the thread as resolved.