Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Nesting query in as400 with select top expression

Posted on 2013-05-22
4
Medium Priority
?
1,778 Views
Last Modified: 2013-05-23
Hey there,

I'm trying to create a passthrough query for the as400 but I'm a bit stuck.

I have ORBHOF, a table with the header of an order. I have ORBLYN a table with the lines of an order. There is 1 to m relation between the 2 (there is 1 or more lines in each order).
I must create a list with the 70 first orders in a specific status. That is not a problem :
"SELECT doknbh FROM prdorbidta.orbhof
WHERE stadbh='35' ORDER BY doknbh
FETCH FIRST 70 ROWS ONLY"
doknbh is the order number. Stadbh is the status. So I get a result with the 70 first orders with the status 35. That is exactly what I want.

Now I need some data from the lines. I can't create a join, I'll get more then a line for each order, and then I cannot get only the first 70 orders.

I would have made a nesting query, the easy version is this one :
"select doknbh from prodorbidta.orbhof where doknbh in (SELECT doknbh FROM prdorbidta.orbhof
WHERE stadbh='35' ORDER BY doknbh
FETCH FIRST 70 ROWS ONLY)".

There is only one problem : The query gives an error with the "ORDER BY" clause. The AS400 refuses to execute the nesting query if the nested part has an order by. This the error message I get :
[IBM][iSeries Access ODBC driver][DB2 UDB]SQL0199 - keyword ORDER not expected. Valid tokens : ) UNION. (#-199) [IBM][iSeries access ODBC driver]Extended dynamic support disabled. (#30129).

I don't care how it is done, but I cannot do it from access. There is a lot of data in ORBLYN, the table with the order lines. It's too slow to access through microsoft access. That's why I made a passthrough query.

Any idea how I could solve my problem?
0
Comment
Question by:vanroybel
  • 2
4 Comments
 
LVL 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 1000 total points
ID: 39188046
What AS/400 operating system release are you running?  If you are V5R4 or later, you can use OLAP SQL specifications to rank and sort:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyolap.htm

IF you are using an older OS version, try using a common table expression (CTE support was introduced V4R4, I believe, so it has been around a long time):

WITH DT AS (SELECT doknbh FROM prdorbidta.orbhof
WHERE stadbh='35' ORDER BY doknbh)
 select doknbh from dt FETCH FIRST 70 ROWS ONLY
0
 
LVL 18

Accepted Solution

by:
Dave Ford earned 1000 total points
ID: 39188141
That's interesting. The following query works beautifully for me on DB2 for i at version 6.1:

select *
  from customer
 where customerid in (
       SELECT CUSTOMERID
         FROM TestTaken
        order by testscore desc
        fetch first 2 rows only)

Open in new window


So, what version are you on?

-- DaveSlash
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 39188168
Piggybacking on Gary's suggestion of a CTE, this works, too:

with tempTable as (
     SELECT CUSTOMERID             
       FROM TestTaken        
      order by testscore desc      
      fetch first 2 rows only)     
select c.*
  from customer c
  join tempTable
    on tempTable.customerid = c.customerid

Open in new window


HTH,
DaveSlash
0
 

Author Closing Comment

by:vanroybel
ID: 39190219
Hello,

I'm on version V5R1.
I think I got the V7R1 around somewhere.

But since I asked the question, I found a way around my problem. Instead of listing the 70 first orders, I just list them all with the info I need, and maketable in access from the data.

Thanks for your help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What we learned in Webroot's webinar on multi-vector protection.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question