subselect query

[btgreen]

Hello,

I am trying to write a subselect query but am getting an error message. Does DB2 support subselects similar to that of Oracle and MS SQL? Here is an example.


SELECT SUM(AMT) as sum_amt, a.SBSR_ID, a.SPSR_ID,
(SELECT COUNT(AMT)
FROM asouthard.eclaims b
where b.spsr_id = a.spsr_id and b.sbsr_id = a.sbsr_id) as AMT_COUNT
FROM asouthard.eclaims a
where a.SPSR_ID = 'AAECU' and a.SBSR_ID in ('003541153', '082546679')
group by a.SPSR_ID,a.SBSR_ID

It works fine in MS SQL but dies at COUNT(AMT) in DB2.

Any help is greatly appreciated.

I understand that this can be re-written in a join statement but the performance is much poorer doing this so I wanted to try this format.

 

[Dagon]

You'll probably need to treat the COUNT(AMT) as a table and select from it:

SELECT SUM(AMT) as sum_amt, a.SBSR_ID, a.SPSR_ID, c.amt_count
FROM asouthard.eclaims a,
(SELECT COUNT(AMT) as amt_count
FROM asouthard.eclaims b
where b.spsr_id = a.spsr_id and
b.sbsr_id = a.sbsr_id) c
where a.SPSR_ID = 'AAECU' and a.SBSR_ID in ('003541153', '082546679')
group by a.SPSR_ID,a.SBSR_ID

 

[swampBoogie]

Why a sub-query?

SELECT SUM(AMT) as sum_amt,
SBSR_ID,
SPSR_ID,
COUNT(AMT) as AMT_COUNT
FROM asouthard.eclaims
where SPSR_ID = 'AAECU'
and SBSR_ID in ('003541153', '082546679')
group by SPSR_ID,SBSR_ID

 

[btgreen]

This was just a sample query not actually the data I needed. I have to do a subquery as I need all the data in the outside query and only those results that match criteria in the subselect statement.