Giving error while using varchar field in oracle

Question

I have a table Both the id and value are varchars

>> id          "value"
-----------------------
   a             1
   a             2
   a             5
   a             6
   a             7
   a             8
   b             4
   b             5
   b             6
   b             9

I just want to see the output

value    Result
-----------------
a        1-2,5,6-8
b        4-6,9

I tried using ListAGG() function

Its giving the error "value" as invalid identifier

SELECT
      LISTAGG ( TXT,
              ', ' )
      WITHIN GROUP (ORDER BY "value")
          "Result"
FROM
      (SELECT
            CASE
                WHEN MIN ( "value" ) = MAX ( "value" )
                THEN
                    CAST ( MIN ( "value" ) AS VARCHAR2 ( 400 ) )
                ELSE
                       MIN ( "value" )
                    || '-'
                    || MAX ( "value" )
            END
                AS TXT,
            MIN ( "value" ) AS SOURCE
       FROM
            (SELECT
                   ROW_NUMBER ( ) OVER (ORDER BY MIN ( "value" ))
                   - TO_NUMBER ( "value" )
                       AS GRP
             FROM
                   TABL2)
       GROUP BY
            GRP);

I guess is there any type mismatch..?? can you please help me...

Do you have two accounts? [That query looks kind of familiar](http://stackoverflow.com/a/20519350/73226) — Martin Smith, Dec 11 '13 at 15:19
@Martin: I dont have two Accounts... :) Its same only but i am getting the error as "value invalid identifier" my both columns are varchars..can u please help me out — user3086100, Dec 11 '13 at 15:23
If you are OK with wm_concat, then its really simple. But unfortunately its not supported. — Srini V, Dec 11 '13 at 15:40

score 2 · Accepted Answer · answered Dec 11 '13 at 15:58

There are a number of problems with the code. But the cause of the error is that you're not including value in the SELECT of the subquery.

I also noticed that you're missing PARTITION BY id, which is required in order to do the groupings you want. You're also not grouping by id in the outer query.

This query:

SELECT id,
      LISTAGG ( TXT,', ' )
      WITHIN GROUP (ORDER BY source)"Result"
FROM
      (SELECT id,
            CASE
                WHEN MIN ( "value" ) = MAX ( "value" )
                THEN
                    CAST ( MIN ( "value" ) AS VARCHAR2 ( 400 ) )
                ELSE
                       MIN ( "value" )
                    || '-'
                    || MAX ( "value" )
            END
                AS TXT,
            MIN ( "value" ) AS SOURCE
       FROM (
            SELECT id,"value",
                   ROW_NUMBER ( )  OVER (partition by id ORDER BY MIN ( "value" ))
                   - TO_NUMBER ( "value" )
                       AS GRP
             FROM
                   TABL2
             GROUP BY id,"value"
       )
       GROUP BY
            id,GRP)
GROUP BY id

Produces:

a   1-2, 5-8
b   4-6, 9

SQLFiddle here

@Joe... OP wants both column to be varchar2. What you have is a number and varchar2. Can you please check?? — Srini V, Dec 11 '13 at 16:08
@realspirituals Sorry, I don't understand what you mean. The output is inherently varchar2 due to the concat from LISTAGG. I had originally created "value" as a NUMBER in my SQLFiddle, but changing it to varchar2 does not require any changes to the SELECT, which produces the same result either way. — Joe, Dec 11 '13 at 16:15

Giving error while using varchar field in oracle

1 Answers1