Solved

Nesting query in as400 with select top expression

Posted on 2013-05-22
4
1,756 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

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:
Dave Ford 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: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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

734 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