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.
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.