1

I have a series of tables defined in a stored procedure to eventually return to the application like this, then culminating to a simple table which contains distinct values from a previously defined table and the count of how many times that distinct value repeats. For some reason it does not seem to be working...

...stored procedure...

OPEN p_retcursor FOR
WITH r AS
(SELECT ...
FROM MYDATATABLE),

s  AS
(SELECT ...
FROM r
WHERE ...)
,

t AS
(SELECT ...
WHERE ... 
JOIN ...
ON ...
FROM s)

SELECT t.*, COUNT(*) as distinctABcount
FROM t
GROUP BY t.columnA, t.columnB

How do I get this to work? I guess technically columnA and columnB are not columns of a table, but are columns of t so I think the order of evaluation is a bit messed up due to the fact that t is not an actual table... Query fails around the count(*) column part and may be failing on the GROUP BY.

Thanks.

Update 1: I have a theory that if I string together all the named tables into subqueries inside the from clause it will work... but that does not seem like a solution which will produce readable code considering this stored procedure is hundreds of lines long...

Update 2:
The theory was wrong, the derived table was not the problem, but as pointed out by the answer, you cannot ask for more columns than what you have set in the group by clause. I fixed it by correcting the final section of the query like suggested:

select t.columnA, t.columnB, COUNT(*) as distinctABcount
FROM t
GROUP BY t.columnA, t.columnB

which runs correctly.

1 Answer 1

2

Unless there is just something specific to Oracle syntax, my suspicion is that you have more than columnA and columnB in your derived table t. If so, you'll need to group by those columns, apply an aggregate to them or do not ask for them (replace t.* with t.columnA, t.columnB)

2
  • yes there are more columns in the derived table t. Do I need to do something with with the group by ColumnC, ColumnD? Let me try that out...
    – fa1c0n3r
    Dec 12, 2012 at 2:22
  • actually this may be working after initially testing it and asking only for columns defined in the group by... let me try to complete the whole query and let you know if it works... Thanks!
    – fa1c0n3r
    Dec 12, 2012 at 2:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.