Converting a view with subqueries to Oracle syntax

Question

Converting a view with subqueries to Oracle syntax

Lowell

SSC Guru

Points: 323518
More actions
December 3, 2004 at 9:15 am

#87209

I've got a view that basically consists of subqueries to get rollup information. It doesn't translate to oracle, and I can't seem to find a decent oracle example.
The view is pretty simple:
CREATE VIEW BENIFCOUNT_STATS AS
SELECT
ACTTBLKEY,
(SELECT COUNT(ACTTBLKEY) FROM GMHOPBEN WHERE RCVSUPPORTSVC ='Y' AND ACTTBLKEY = A.ACTTBLKEY)AS RCVSUPPORTSVC,
(SELECT COUNT(ACTTBLKEY) FROM GMHOPBEN WHERE RCVHOUSINGAST ='Y' AND ACTTBLKEY = A.ACTTBLKEY)AS RCVHOUSINGAST,
(SELECT COUNT(ACTTBLKEY) FROM GMHOPBEN WHERE RCVLONGTERM ='Y' AND ACTTBLKEY = A.ACTTBLKEY)AS RCVLONGTERM,
(SELECT COUNT(ACTTBLKEY) FROM GMHOPBEN WHERE RCVSHORTTERM ='Y' AND ACTTBLKEY = A.ACTTBLKEY)AS RCVSHORTTERM
FROM GMHOPBEN A
GROUP BY ACTTBLKEY,RCVSUPPORTSVC,RCVHOUSINGAST,RCVLONGTERM,RCVSHORTTERM

Can anyone give me a hint or example of an oracle view that does a rollup of information like this?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Hi Lowell,

My PL/SQL is sort of rusty, but I think GROUP BY isn't allowed in a view definition. Try leaving that out of the CREATE VIEW and using it in your SELECT from the view instead.

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 2

Actually, i figured out a different way to get the totals; rather than using that subquery to get the count, i'm using a group by to count case statements, which works in both SQL and Oracle using the same syntax:

SELECT

ACTTBLKEY,

SUM (CASE WHEN RCVSUPPORTSVC ='Y' THEN 1 ELSE 0 END) AS RCVSUPPORTSVC,

SUM (CASE WHEN RCVHOUSINGAST ='Y' THEN 1 ELSE 0 END)AS RCVHOUSINGAST,

SUM (CASE WHEN RCVLONGTERM ='Y' THEN 1 ELSE 0 END)AS RCVLONGTERM,

SUM (CASE WHEN RCVSHORTTERM ='Y' THEN 1 ELSE 0 END)AS RCVSHORTTERM

FROM GMHOPBEN

GROUP BY ACTTBLKEY ;

thanks for reading this!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!