Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1784
  • Last Modified:

Nesting query in as400 with select top expression

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
vanroybel
Asked:
vanroybel
  • 2
2 Solutions
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
vanroybelAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now