oracle - SQL - How to use a column in a select statement without specifying it in the group by clause -

oracle - SQL - How to use a column in a select statement without specifying it in the group by clause -

i trying run query fails:

select p.product_id, p.product_name, cast(collect(coalesce(product_type, decode(product_description,null,'descr' || '-' product_description) my_type) product_type, decode(product_source_location, null, 'no_source', product_source_location) products grouping p.product_id, p.product_name

it fails because product_source_location not part of grouping clause.

i dont want include product_source_location in group by clause result becomes incorrect. there way can utilize product_source_location in decode function shown above without having include in grouping clause?

interestingly using product_type in coalesce function , not forcefulness me include product_type in grouping clause.

try doing this:

coalesce(max(product_source_location), 'no_source')

the complicated collect statement correct, because collect aggregation function. final decode using column not mentioned in group by clause. fixes problem wrapping max() around it.

and, can using decode():

decode(max(product_source_location), null, 'no_source', max(product_source_location))

but encourage larn more standard coalesce() function , case function.

sql oracle plsql oracle10g oracle11g