DB2 Join Query that Maximizes Column

Question

I am OK at basic SQL, but my understanding fails when it comes to more complex searches.

Right now, I am trying to return records for Location 16.

Most records are empty, so I cannot debug my database application with those records.

SELECT
  I.PART_NUM, I.ID, L.SHELF, L.IN_STOCK
FROM
  INVENTORY I
  JOIN LOCATIONS L ON I.ID=L.INV_ID
WHERE
  L.ID=16 AND PART_NUM IN 
  (
    SELECT
      TOP 10 N.PART_NUM
    FROM
      INVENTORY N 
      JOIN LOCATIONS T ON N.ID=T.INV_ID
    WHERE
      T.ID=16
    ORDER BY 
      IN_STOCK
  )
ORDER BY 
      IN_STOCK, I.PART_NUM

Developers are not given direct access to the database, but rather make calls using an in-house developed application that permits basic SQL calls.

When I try running the code below, I get the following error:

ERROR [42601][IBM][DB2/AIX64] SQL0104N An unexpected token "10" was found following "DOR IN ( SELECT TOP". Expected tokens may include: "CONCAT".

I wasn't sure if this was a limitation of our in-house developed application or something in DB2.

I went to the SQL Tutorial here:

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

There, I used the tables they supply to create a similar query:

SELECT 
    OrderID, ProductID, Quantity, LastName, FirstName
FROM 
    OrderDetails I 
    JOIN Orders L ON I.OrderID=L.OrderID
WHERE 
    EmployeeID=5 AND OrderDetailID IN 
    (
        SELECT 
            TOP 10 N.OrderDetailID 
        FROM 
            OrderDetails N 
            JOIN Orders T ON N.OrderID=T.OrderID
        WHERE 
            EmployeeID=5
        ORDER BY 
            Quantity
    )
ORDER BY 
    Quantity

It gives me a similar error:

Error 1: could not prepare statement (1 near "10": syntax error)

What do I need to do to modify my SQL query to return the Vendors with the most quantity in stock?

I can order the LOCATIONS table by the IN_STOCK quantity, but then I cannot filter to get INVENTORY records that are valid (lots of test data in the database).

Answer 1

The clause Select TOP is not valid on DB2. Intead use

SELECT * 
FROM myTable
ORDER BY id
FETCH FIRST 10 ROWS ONLY