nested query problem.

[mikej336]

Basically have 2 quaeries. The first one brings back a basic selection between 2 joined tables. The second one groups and counts the first one.

SELECT substr(sfid,1,6) AS test, SFCODE, SVCID, SVCOTY
FROM SVCYAD/SVCFT, SVCYAD/SRVMAST99 WHERE
SFCODE = 'A2 '

SELECT test, SFCODE, SVCOTY, count(svcoty)
FROM
GROUP BY test, SFCODE, SVCOTY

That I need to run as one query. I can only find basic examples of what to do.

I have tried variations of...

SELECT test, SFCODE, SVCOTY, count(svcoty)
FROM (SELECT substr(sfid,1,6) AS test, SFCODE, SVCID, SVCOTY FROM SVCYAD/SVCFT, SVCYAD/SRVMAST99 WHERE
SFCODE = 'A2 ') as bla
GROUP BY test, SFCODE, SVCOTY

I have also tried to group the first query and it gives me an error basically saying that it is to complex.

Trying to run this on AS400/DB2 if that makes a difference.

Thanks

M

 

[BrianTyler]

I have not worked on AS400, only UDB on AIX.

Your version with the temporary table appears to be OK in UDB terms, though you may be missing a join between the two tables.

What problem are you getting with it? It is a possibilty that you don't declare temporary tables in the same way.

I am assuming that the examples you give are simplified, because there would be no need for a second query to produce results:

SELECT
substr(sfid,1,6) AS test
,SFCODE
,count(SVCOTY)
FROM
SVCYAD/SVCFT
,SVCYAD/SRVMAST99
WHERE
SFCODE = 'A2 '
group by
substr(sfid,1,6)
,SFCODE

 

[mikej336]

Yes it is smiplified. It also joins 2 other tables.

It will actually execute but then it says "creating N* of N*". Then it just runs and runs. Takes up 95% of the processor and never returns. I eventually have to kill it.

I have mirrored it in Access and the only way I can get it to work is by quering a query.

Thanks

M

 

[lespaul]

I ran a query almost identical to yours with a subquery as the FROM clause on our AS400/DB2 and it ran no problems!!

Leslie