• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3197
  • Last Modified:

AS400 DB2 how to run nested subqueries

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
glenn_r
Asked:
glenn_r
  • 3
  • 2
2 Solutions
 
glenn_rAuthor Commented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
glenn_rAuthor Commented:
Any ideas why id be getting that error?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
glenn_rAuthor Commented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
 
LowfatspreadCommented:
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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