sql - Oracle Listagg Sub query -
select e.pem_id id, e.pem_subject subject, e.pem_content content, e.pem_sent_date sentdate, e.pem_ref_id referenceid, e.pem_from_usr_id userid, null misc, (listagg(str.str_us_id, ', ') within group(order '') attachedusers proj_email_usrs eu left outer join st_register str on eu.pmu_str_id = str.str_id eu.pmu_pem_id = '26' group '') proj_email e e.pem_prj_id = '33' , e.pmu_pem_id = '26' , e.pem_status = 's';
it throwing error as
ora-01722: invalid number
01722. 00000 - "invalid number"
*cause: specified number invalid.
*action: specify valid number.
i assume want query this:
select e.pem_id id, e.pem_subject subject, e.pem_content content, e.pem_sent_date sentdate, e.pem_ref_id referenceid, e.pem_from_usr_id userid, null misc, (select listagg(str.str_us_id, ', ') within group (order str.str_us_id) proj_email_usrs eu left outer join st_register str on eu.pmu_str_id = str.str_id eu.pmu_pem_id = e.pmu_pem_id -- correlation clause ) attachedusers proj_email e e.pem_prj_id = 33 , e.pmu_pem_id = 26 , e.pem_status = 's' ;
comments:
- added
select
beforelistagg()
. needed subquery. - remove
group by
. not needed, because want subquery return 1 row. - removed single quotes constants numbers. use single quotes string , date constants.
- added correlation clause subquery.
- moved name of column outside subquery, named in result.
- added
order by
columnwithin group
. not necessary, reasonable keep ids in order.
Comments
Post a Comment