Solved

Nesting query in as400 with select top expression

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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