Query with embedded CURSOR - what is the error?

Question

The following Query Works....

SELECT art.cmp_lvl,
              (
               CURSOR (
                 SELECT   a.cmp_lvl,
                          (
                            CURSOR (
                              SELECT   b.cmp_lvl_code,
                                       b.v1 "R_ST",
                                       b.f_name
                                  FROM temp_data b
                                 WHERE b.cmp_lvl = a.cmp_lvl
                              ORDER BY SEQ
                            )
                          ) employee
                     FROM temp_data a
                    WHERE a.cmp_lvl = art.cmp_lvl
                 GROUP BY a.cmp_lvl
               )
              ) g_pos_record
         FROM temp_data art
        WHERE art.report_name = 'EMP_REPORT'
        GROUP BY cmp_lvl
        ORDER BY cmp_lvl DESC;

Then I simply add another column name as shown below

SELECT art.cmp_lvl,
       art.v1                    /*ADDED THIS LINE, SHOWS AN ERROR IN THIS LINE */
              (
               CURSOR (
                 SELECT   a.cmp_lvl,
                          a.v1   /*ADDED THIS LINE */
                          (
                            CURSOR (
                              SELECT   b.cmp_lvl_code,
                                       b.v1 "R_ST",
                                       b.f_name 
                                  FROM temp_data b
                                 WHERE b.cmp_lvl = a.cmp_lvl
                                   AND b.v1 = a.v1           /*ADDED THIS LINE */
                              ORDER BY SEQ
                            )
                          ) employee
                     FROM temp_data a
                    WHERE a.cmp_lvl = art.cmp_lvl
                      AND a.v1 = art.v1                      /*ADDED THIS LINE */
                 GROUP BY a.cmp_lvl
               )
              ) g_pos_record
         FROM temp_data art
        WHERE art.report_name = 'EMP_REPORT' 
        GROUP BY cmp_lvl
        ORDER BY cmp_lvl DESC;

Error: ORA-00904: "ART", "V1": Invalid Identifier 00904. 00000 - "%s: Invalid identifier" "Cause *Action Error at Line 2

I Cannot figure out why. The column added is in the table (for sure).

missing the comma is one issue, you need to group by the newly added field or get a aggregate value of that — Srinika Pinnaduwage
good that you remember that a question is asked 2 weeks ago :O — Srinika Pinnaduwage

Thorsten Kettner Thorsten Kettner · Accepted Answer · 2020-08-19T20:38:15

You aggregate your data to one result row per cmp_lvl (GROUP BY cmp_lvl). Then you want to show the cmp_lvl's v1. However, as there are multiple rows per cmp_lvl that you are aggregating, which row's v1 shall be shown?

Moreover, there is a comma missing after art.v1.

One solution is to decide for one v1, e.g.:

SELECT art.cmp_lvl,
       MAX(art.v1),
       ...

Query with embedded CURSOR - what is the error?

1 Answers