CODEWITHSUNDEEP
sqloracle-databaseconcatenationlistagg
Lyndey
Lyndey

Reputation: 77

Oracle SQL - using listagg on a concatenated column from a view

I created a view with a concatenated column.

I successfully used listagg on the individual columns, however when I attempted to use the concatenated column in the listagg, I received the error that it was an invalid identifier.

Is there a way to combine the requirement and message_desc columns in a listagg format?

my query is as follows:

select a.ID,
a.NAME,
a.YEAR,
count (a.linenum) as count_missing_docs,
listagg(a.requirement, ',') within group (order by a.requirement) as "reqs",
listagg(a.MESSAGE_DESC, '...')within group (order by a.MESSAGE_DESC)as "msgs",
listagg(a.combo,' ') within group (order by a.combo) as "all_info"
from
(SELECT
rownum "LINENUM",
FTR.ID,
FTR.NAME,
FTR.YEAR,
FTR.REQUIREMENT,
FTR.STATUS,
FTR.STATUS_IND,
FTR.MESSAGE_DESC,
FTR.REQUIREMENT||'-'||FTR.MESSAGE_DESC||'...' as "combo"
from TRACKING_REQUIREMENT FTR
where FTR.year = '1617'
and FTR.status = 'R'
and FTR.satisfied_ind = 'N'
order by 2 , 1 asc) A
group by
a.ID,
a.NAME,
a.YEAR
order by 1

Upvotes: 0

Views: 283

Answers (1)

kfinity
kfinity

Reputation: 9091

The problem is actually just that you keep putting double quotes around your identifiers - this tells oracle that you want a case sensitive identifier, but then you're referring to it without double quotes, using a case-insensitive identifier (which Oracle seems to interpret internally as ALL UPPERCASE).

Just get rid of all the double quotes and your query should work fine.

Upvotes: 3

Related Questions