first alias works second doesn't throws invalid identifier

Question

Recently I came across a very strange issue (Oracle 12c), the LISTAGG works for the first alias column but not for the second one (both are almost identical) throwing ORA-00904: "PARAMDESC": invalid identifier. Here is the SQL:

 SELECT s_sample.s_sampleid,
   b.u_tarolotnumber,
   p.productdesc,
   s_sample.productid,
   LISTAGG(wi.s_departmentid, '; ') WITHIN
   GROUP(ORDER BY wi.s_departmentid) Department, 
   wi.workitemid, 
   s_sample.sampletypeid, 
   NVL((SELECT sa.aliasid 
          FROM sdialias sa
         WHERE sa.sdcid = 'Workitem'
           AND sa.keyid1 = wi.workitemid), wi.workitemdesc)workitemdesc, 
   NVL((SELECT sa.aliasid
          FROM sdialias sa
         WHERE sa.sdcid = 'ParamList' 
           AND sa.keyid1 = p.paramlistid), p.paramlistdesc) paramdesc

 FROM s_sample
 JOIN s_batch b ON s_sample.batchid = b.s_batchid
            AND s_sample.classification = 'ReagentQuality'
 JOIN s_product p ON s_sample.productid = p.s_productid
 JOIN sdiworkitem sdi ON sdi.keyid1 = s_sample.s_sampleid
                  AND sdi.sdcid = 'Sample'
 JOIN workitem wi ON wi.workitemid = sdi.workitemid
              AND wi.workitemversionid = sdi.workitemversionid
 JOIN sdiworkitemitem sdiwi ON wi.workitemid = sdiwi.workitemid
                        AND sdiwi.keyid1 = s_sample.s_sampleid
                        AND sdiwi.sdcid = 'Sample'
 JOIN paramlist p ON p.paramlistid = sdiwi.itemkeyid1
              AND p.paramlistversionid = sdiwi.itemkeyid2
              AND p.variantid = sdiwi.itemkeyid3
 WHERE s_sample.s_sampleid IN ('S100')
 AND 1 = 1
 GROUP BY s_sample.s_sampleid,
      b. u_tarolotnumber,
      p.productdesc,
      s_sample.productid,
      s_sample.sampletypeid,
      wi.workitemid,
      workitemdesc,          
      paramdesc;

Did lot of googling, but no luck! Any help would be highly appreciated.

Thanks in advance Suv

It has nothing to do with `LISTAGG` or Oracle 12c. Aliased columns can't be used in `GROUP BY` without nesting. Since `PARAMDESC` was seen by the parser first , the `invalid identifier` error was thrown for it and not for `workitemdesc`. — Kaushik Nayak, Feb 24 '18 at 16:59
You are right. But the strange part is - if I comment out NVL((SELECT sa.aliasid FROM sdialias sa WHERE sa.sdcid = 'ParamList' AND sa.keyid1 = p.paramlistid), p.paramlistdesc) paramdesc and the same in group by clause it works perfectly (without nesting). What I don't understand is how come it works for workitemdesc and not the paramdesc. — Suvransu Kumar Mohanty, Feb 24 '18 at 17:26
Because in that case it is not grouping by the *alias* of workitemdesc, it is grouping by the true *column* wi.workitemdesc — Connor McDonald, Feb 25 '18 at 05:38

first alias works second doesn't throws invalid identifier

0 Answers0