nested query question

[mikej336]

Basically have 2 quaeries. The first one brings back a basic selection. 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...

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

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

Thanks

M

 

[r937]

yes it makes a big difference

this forum is for ansi sql

you'll probably get an answer quickly if you post your question in the DB2 forum -- forum178

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[Glenn9999]

What is the error you're seeing? The first thing that jumps out that I would change is to use the table correlation name in the outer select...

SELECT bla.test, bla.SFCODE, bla.SVCOTY, count(svcoty)

 

[PHV]

Why not simply this ?
SELECT substr(sfid,1,6) AS test, SFCODE, SVCOTY, count(svcoty)
FROM SVCYAD/SVCFT, SVCYAD/SRVMAST99
WHERE SFCODE = 'A2 '
GROUP BY substr(sfid,1,6), SFCODE, SVCOTY

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244