Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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)

share|improve this answer
    
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 '12 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 '12 at 2:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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