Solved

Nesting query in as400 with select top expression

Posted on 2013-05-22
4
1,724 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 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 250 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:
daveslash earned 250 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:daveslash
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now