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

x
?
Solved

AS400 DB2 how to run nested subqueries

Posted on 2011-02-17
6
Medium Priority
?
3,104 Views
Last Modified: 2012-05-11
I mostly work with SQL Server. I'm not a AS400 DB2 expert. How do I write the following subquery? When I run it display the following error.

error during prepare
37000(-104)[ibm[psystem i access odbc driver)[db2 for i5/os)sql0104 - token <end-of-statement> was not valid. valid tokens: as in out <identifier>.

Example1:

SELECT *
FROM
(
SELECT *
FROM TABLE1
)

Example2 - both table 1 and table 2 have the same number of columns

SELECT *
FROM
(
SELECT *
FROM TABLE1
UNION ALL
SELECT *
FROM TABLE2
)
0
Comment
Question by:glenn_r
  • 3
  • 2
6 Comments
 

Author Comment

by:glenn_r
ID: 34918549
in addition i've tried specifying the column names with alias but it still throws the same error. I'm simulating 2 tables with the same columns and column types.

example:

SELECT column1, column2
FROM
(
SELECT po1 AS column1, line1 AS column2
FROM TABLE1
UNION ALL
SELECT po2 AS column1, line2 AS column2
FROM TABLE2
)
0
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 248 total points
ID: 34918568
The following queries work beautifully for me on AS/400 v6r1

HTH,
DaveSlash

select * from (
  select *
  from   mytable  where  deleteRequest = 'N'
) as tempTable
where userid = 2102

----------------------------

select * from (
  select *
  from   mytable
  where  deleteRequest = 'N'
  union
  select *
  from   mytable
  where  deleteRequest = 'Y'
) as tempTable
where userid = 2102

Open in new window

0
 

Author Comment

by:glenn_r
ID: 34918603
Any ideas why id be getting that error?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:glenn_r
ID: 34918632
daveslash: I see you specify a tablename after the subquery (as tempTable). Can you strip this off and see if you get the same error. Perhaps thats my problem.
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 34918659

That's exactly your problem.

Personally, I prefer to use the WITH clause instead of embedding a SELECT in the FROM clause.

e.g.

with deleteRequestNo as (
  select *
  from   myTable
  where  deleteRequest = 'N'
),
deleteRequestYes as (
  select *
  from   myTable
  where  deleteRequest = 'Y'
)
select *
from   deleteRequestNo
union
select *
from   deleteRequestYes

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 252 total points
ID: 34927341
your problem is that you haven't named the subquery... that is the missing token...

select * from
  (select ....
       from ...
     union ...
       select
         .... from

 ) as ANAME

required in both db2 and MSSQL etc...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview

927 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