11 Replies Latest reply: Feb 28, 2012 1:18 AM by 919726 RSS

    Help for a query to add columns

    680875
      Hi,

      I need for a query where I should add each TableC value as an additional column.

      Please suggest...

      I have 3 tables (TableA, TableB, TableC). TableB stores TableA Id and TableC stores TableB Id
      Considering Id of TableA.

      Sample data
      TableA     
      :          
      ID     NAME     TABLENAME     ETYPE
      23     Name1     TABLE NAMEA     Etype A
                     
      TableB     :          
      ID     A_ID     RTYPE     RNAME
      26     23     RTYPEA     RNAMEA
      61     23     RTYPEB     RNAMEB
                     
      TableC     :          
      ID     B_ID     COMPNAME     CONC
      83     26     Comp Name AA     1.5
      46     26     Comp Name BB     2.2
      101     61     Comp Name CC     4.2


      Scenario 1:
      AS PER ABOVE SAMPLE DATA Put each TableC value as an additional column.
      For an Id in TableA(23) where TableB contains 2 records of A_ID (26, 61) and TableC contains 2 records for 26 and 1 record for 61.

      Output required: Put each TABLEC value as an additional column

      TableA.NAME TableA.ETYPE TableB.RTYPE TableC_1_COMPNAME     TableC_1_CONC TableC_2_COMPNAME     TableC_2_CONC     
      --------------------------------------------------------------
      Name1 EtypeA RTypeA Comp Name AA 1.5 Comp Name BB 2.2     so on..
      Name1 EtypeA RTypeB Comp Name CC 4.2 NULL NULL     

      Scenario 2: If Table C contains ONLY 1 row for each Id in TableB, output should be somewhat
      Output:
      TableA.NAME TableA.ETYPE TableB.RTYPE TableC_1_COMPNAME
      TableC_1_CONC
      value     value     value     value               value
        • 1. Re: Help for a query to add columns
          Frank Kulash
          Hi,

          Welcome to the forum!

          Do you want the data from TableC presented
          (1) in one column, or
          (2) in several columns (a different column of results for each row in the original TableC)?

          (1) Is called String Aggregation and is easier than (2).
          The best way to do this is with a user-defined aggregate function (STRAGG) which you can copy from asktom.
          Ignoring TableA for now, you could get what you want by saying
          SELECT    b.rtype
          ,         STRAGG (   c.compname
                           || ' '
                           || c.conc
                           )  AS c_data
          FROM      TableB  b
          JOIN      TableC  c  ON b.id  = c.b_id
          GROUP BY  b.rtype;
          (2) Presenting N rows of TableC as it they were N columns of the same row is called a pivot. Search for "pivot" or "rows to columns" to find examples of how to do this.

          The number of columns in a result set is hard-coded into the query. If you don't know ahead of time how many rows in TableC will match a row in TableB, you can:
          (a) guess high (for example, hard-code 20 columns and let the ones that never contain a match be NULL) or,
          (b) use Dynamic SQL to write a query for you, which has exactly as many columns as you need.

          The two scripts below contain basic information on pivots.

          This first script is similar to what you would do for case (a):
          --     How to Pivot a Result Set (Display Rows as Columns)
          
          --     For Oracle 10, and earlier
          --     Actually, this works in any version of Oracle, but the 
          --     "SELECT ... PIVOT" feature introduced in Oracle 11
          --     is better.  (See Query 2, below.)
          
          --     This example uses the scott.emp table.
          --     Given a query that produces three rows for every department,
          --     how can we show the same data in a query that has one row
          --     per department, and three separate columns?
          
          --     For example, the query below counts the number of employess
          --     in each departent that have one of three given jobs:
          
          PROMPT     ==========  0. Simple COUNT ... GROUP BY  ==========
          
          SELECT     deptno
          ,     job
          ,     COUNT (*)     AS cnt
          FROM     scott.emp
          WHERE     job     IN ('ANALYST', 'CLERK', 'MANAGER')
          GROUP BY     deptno
          ,          job;
          
          /*
          Output:
          
              DEPTNO JOB              CNT
          ---------- --------- ----------
                  20 CLERK              2
                  20 MANAGER            1
                  30 CLERK              1
                  30 MANAGER            1
                  10 CLERK              1
                  10 MANAGER            1
                  20 ANALYST            2
          */
          
          
          PROMPT     ==========  1. Pivot  ==========
          
          SELECT     deptno
          ,     COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)     AS analyst_cnt
          ,     COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)     AS clerk_cnt
          ,     COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)     AS manager_cnt
          FROM     scott.emp
          WHERE     job     IN ('ANALYST', 'CLERK', 'MANAGER')
          GROUP BY     deptno;
          
          
          /*
          --     Output:
          
              DEPTNO ANALYST_CNT  CLERK_CNT MANAGER_CNT
          ---------- ----------- ---------- -----------
                  30           0          1           1
                  20           2          2           1
                  10           0          1           1
          
          
          --     Explanation
          
          (1) Decide what you want the output to look like.
               (E.g. "I want a row for each department,
               and columns for deptno, analyst_cnt, clerk_cnt and manager_cnt)
          (2) Get a result set where every row identifies which row
               and which column of the output will be affected.
               In the example above, deptno identifies the row, and
               job identifies the column.
               Both deptno and job happened to be in the original table.
               That is not always the case; sometimes you have to
               compute new columns based on the original data.
          (3) Use aggregate functions and CASE (or DECODE) to produce
               the pivoted columns.  
               The CASE statement will pick
               only the rows of raw data that belong in the column.
               If each cell in the output corresponds to (at most)
               one row of input, then you can use MIN or MAX as the
               aggregate function.
               If many rows of input can be reflected in a single cell
               of output, then use SUM, COUNT, AVG, STRAGG, or some other 
               aggregate function.
               GROUP BY the column that identifies rows.
          */
          
          
          PROMPT     ==========  2. Oracle 11 PIVOT  ==========
          
          WITH     e     AS
          (     -- Begin sub-query e to SELECT columns for PIVOT
               SELECT     deptno
               ,     job
               FROM     scott.emp
          )     -- End sub-query e to SELECT columns for PIVOT
          SELECT     *
          FROM     e
          PIVOT     (     COUNT (*)
                    FOR     job     IN     ( 'ANALYST'     AS analyst
                                   , 'CLERK'     AS clerk
                                   , 'MANAGER'     AS manager
                                   )
               )
          ;
          
          /*
          NOTES ON ORACLE 11 PIVOT:
          
          (1) You must use a sub-query to select the raw columns.
          An in-line view (not shown) is an example of a sub-query.
          (2) GROUP BY is implied for all columns not in the PIVOT clause.
          (3) Column aliases are optional.  
          If "AS analyst" is omitted above, the column will be called 'ANALYST' (single-quotes included).
          {code}
          
          The second script, below, shows one way of doing a dynamic pivot in SQL*Plus:
          {code}
          /*
          How to Pivot a Table with a Dynamic Number of Columns
          
          This works in any version of Oracle
          The "SELECT ... PIVOT" feature introduced in Oracle 11
          is much better for producing XML output.
          
          Say you want to make a cross-tab output of
          the scott.emp table.
          Each row will represent a department.
          There will be a separate column for each job.
          Each cell will contain the number of employees in
               a specific department having a specific job.
          The exact same solution must work with any number
          of departments and columns.
          (Within reason: there's no guarantee this will work if you 
          want 2000 columns.)
          
          Case 0 "Basic Pivot" shows how you might hard-code three
          job types, which is exactly what you DON'T want to do.
          Case 1 "Dynamic Pivot" shows how get the right results
          dynamically, using SQL*Plus.  
          (This can be easily adapted to PL/SQL or other tools.)
          */
          
          
          PROMPT     ==========  0. Basic Pivot  ==========
          
          SELECT     deptno
          ,     COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)     AS analyst_cnt
          ,     COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)     AS clerk_cnt
          ,     COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)     AS manager_cnt
          FROM     scott.emp
          WHERE     job     IN ('ANALYST', 'CLERK', 'MANAGER')
          GROUP BY     deptno
          ORDER BY     deptno
          ;
          
          
          PROMPT     ==========  1. Dynamic Pivot  ==========
          
          --     *****  Start of dynamic_pivot.sql  *****
          
          -- Suppress SQL*Plus features that interfere with raw output
          SET     FEEDBACK     OFF
          SET     PAGESIZE     0
          
          SPOOL     p:\sql\cookbook\dynamic_pivot_subscript.sql
          
          SELECT     DISTINCT
               ',     COUNT (CASE WHEN job = '''
          ||     job
          ||     ''' '     AS txt1
          ,     'THEN 1 END)     AS '
          ||     job
          ||     '_CNT'     AS txt2
          FROM     scott.emp
          ORDER BY     txt1;
          
          SPOOL     OFF
          
          -- Restore SQL*Plus features suppressed earlier
          SET     FEEDBACK     ON
          SET     PAGESIZE     50
          
          SPOOL     p:\sql\cookbook\dynamic_pivot.lst
          
          SELECT     deptno
          @@dynamic_pivot_subscript
          FROM     scott.emp
          GROUP BY     deptno
          ORDER BY     deptno
          ;
          
          SPOOL     OFF
          
          --     *****  End of dynamic_pivot.sql  *****
          
          /*
          EXPLANATION:
          The basic pivot assumes you know the number of distinct jobs,
          and the name of each one.  If you do, then writing a pivot query
          is simply a matter of writing the correct number of ", COUNT ... AS ..."\
          lines, with the name entered in two places on each one.  That is easily
          done by a preliminary query, which uses SPOOL to write a sub-script 
          (called dynamic_pivot_subscript.sql in this example).
          
          The main script invokes this sub-script at the proper point.
          In practice, .SQL scripts usually contain one or more complete
          statements, but there's nothing that says they have to.
          This one contains just a fragment from the middle of a SELECT statement.
          
          Before creating the sub-script, turn off SQL*Plus features that are 
          designed to help humans read the output (such as headings and 
          feedback messages like "7 rows selected.", since we do not want these 
          to appear in the sub-script.
          Turn these features on again before running the main query.
          
          */
          {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          • 2. Re: Help for a query to add columns
            680875
            Thanks for replying Frank... seems to be very advanced, actually I am not a DBA... so would need your guidance more.

            I have tried to make my request more simple, is there a way I can use code tag while posting...

            As per sample data below : For an Id in TABLE_A(23) where TABLE_B contains 2 records of TABLE_A_ID (26, 61) and TABLE_C contains 2 records for 26 and 1 record for 61.

            Output required is, so that 2 records with TABLE_B_ID 26 in TABLE_C should be combined to a single row as shown below (note Comp AA, Comp BB). Also there can be maximum 8 records in table_C for any given TABLE_B_ID
            -----------------------------------------------------------------------------------------------
            TABLE_A_NAME     TABLE_A_ETYPE     TABLE_B_RTYPE     TABLE_C_1_COMPNAME     TABLE_C_1_CONC     TABLE_C_2_COMPNAME     TABLE_C_2_CONC

            Name1          EtypeA          RTypeA          Comp Name AA          1.5          Comp Name BB          2.2
            Name1          EtypeA          RTypeB          Comp Name CC          4.2          NULL               NULL
            -----------------------------------------------------------------------------------------------


            SAMPLE data:

            TABLE_A
            ID NAME TABLENAME ETYPE
            23 Name 1 TABLE NAME A Etype A

            TABLE_B
            ID TABLE_A_ID RTYPE RNAME
            26 23 RTYPE A RNAME A
            61 23 RTYPE B RNAME B

            TABLE_C
            ID TABLE_B_ID COMPNAME CONC
            83 26 Comp Name AA 1.5
            46 26 Comp Name BB 2.2
            101 61 Comp Name CC 4.2
            • 3. Re: Help for a query to add columns
              Frank Kulash
              Hi,

              You explained pretty well what goes on each row.
              My question was how many columns do you want?
              If you're content with one huge column containing all the relevant data from TableC (in a comma-delimited list) you can use string aggregation (e.g. STRAGG), which would be simpler.
              Actually, you made it pretty clear that you wanted separate columns for each row in TableC, but I don't know if that is an absolute requirement or not.

              The STRAGG function is extremely useful. It involves copying and running about 60 lines of code from the asktom site: no moodifications are needed.
              Once STRAGG is installed, you can use it in any query you want.
              If you have Oracle 10 (or up) you may already have a similar function, WM_CONCAT (owned by WMSYS) installed. WM_CONCAT is undocumented, so I don't recommend it for anything other than proving to yourself how useful a string aggregation function can be.

              To post formatted data on this site, type

              {code}

              (all small letters, in curly brackets) before and after sections where spacing is important.
              • 4. Re: Help for a query to add columns
                680875
                Thanks again for taking care of it...

                Answer to your questions :
                Question was how many columns do you want
                There can be maximum 10 records for TABLE_C

                As per sample data in my original post, we are looking for an output like (notice Comp Name AA & Comp Name BB should be in single row..):
                TABLE_A_NAME     TABLE_A_ETYPE     TABLE_B_RTYPE     TABLE_C_1_COMPNAME     TABLE_C_1_CONC     TABLE_C_2_COMPNAME     TABLE_C_2_CONC
                
                Name1          EtypeA          RTypeA          Comp Name AA          1.5          Comp Name BB          2.2 
                Name1          EtypeA          RTypeB          Comp Name CC          4.2          NULL          NULL
                • 5. Pivot with ROW_NUMBER
                  Frank Kulash
                  Hi,

                  The main difference between your problem and the "Simple Pivot" I posted earlier is deciding up in which column the pivoted data will wind.
                  In the "Simple Pivot" example, that was governed by the column "job".
                  If job='ANALYST', the data went into the first column.
                  If job='CLERK', the data went into the second column.
                  If job='MANAGER', the data went into the third column.
                  You don't have any corresponding column in your table_c, so I generated one, namely r_num, in the sub-query numbered_c. I decided that they pivoted columns would be in order by compname, and, if two rows had the same compname, id. You can choose any order you want, just give each row some value that will be unique when combined with table_b_id.
                  WITH     numbered_c     AS
                  (
                       SELECT     table_b_id
                       ,     compname
                       ,     conc
                       ,     ROW_NUMBER () OVER
                                 (     PARTITION BY     table_b_id
                                      ORDER BY     compname
                                      ,          id
                                 ) AS r_num
                       FROM     table_c
                  )
                  SELECT     MAX (a.name)     AS table_a_name
                  ,     MAX (a.etype)     AS table_a_etype
                  ,     b.rtype          AS table_b_rtype
                  ,     MAX (CASE WHEN r_num = 1 THEN c.compname END)
                                 AS table_c_1_compname
                  ,     MAX (CASE WHEN r_num = 1 THEN c.conc END)
                                 AS table_c_1_conc
                  ,     MAX (CASE WHEN r_num = 2 THEN c.compname END)
                                 AS table_c_2_compname
                  ,     MAX (CASE WHEN r_num = 2 THEN c.conc END)
                                 AS table_c_2_conc
                  ,     MAX (CASE WHEN r_num = 3 THEN c.compname END)
                                 AS table_c_3_compname
                  ,     MAX (CASE WHEN r_num = 3 THEN c.conc END)
                                 AS table_c_3_conc
                  FROM     table_a          a
                  JOIN     table_b          b     ON a.id     = b.table_a_id
                  JOIN     numbered_c     c     ON b.id = c.table_b_id
                  GROUP BY     b.rtype;
                  Output:
                  TABLE   TABLE    TABLE    TABLE         TABLE TABLE         TABLE TABLE        TABLE
                  _A      _A       _B       _C_1           _C_1 _C_2           _C_2 _C_3         _C_3
                  _NAME   _ETYPE   _RTYPE   COMPNAME       CONC COMPNAME       CONC COMPNAME     CONC
                  ------- -------- -------- ------------ ------ ------------ ------ ------------ ------
                  Name 1  Etype A  RTYPE A  Comp Name AA    1.5 Comp Name BB    2.2
                  Name 1  Etype A  RTYPE B  Comp Name CC    4.2
                  I hard-coded 3 sets of columns from table_c. You can hard-code 10 sets, or however many you want. Notice that the unused ones are blank. (That is, the last two columns above are never used, but they still appear in the output.)
                  • 6. Re: Pivot with ROW_NUMBER
                    680875
                    Great Frank.... frankly speaking it looks very good & advance for me but I am learning.. :)

                    I created test records as below,
                    I know I am doing something wrong to execute the sql..:( I copied the complete sql as per your last mail and trying to execute it as is using Toad... it says
                    "0 rows selected" ??, I need to learn more but please bear with me....
                    create table TABLE_A (ID number(19,0) not null, NAME varchar2(255 char), TABLENAME varchar2(255 char), ETYPE varchar2(255 char), primary key (ID));
                    create table TABLE_B (ID number(19,0) not null, RTYPE varchar2(255 char), RNAME varchar2(255 char), TABLE_A_ID number(19,0) not null, primary key (ID));
                    create table TABLE_C (ID number(19,0) not null, COMPNAME varchar2(255 char), CONC double precision, TABLE_B_ID number(19,0) not null, primary key (ID));
                    Insert into TABLE_A (ID,NAME,TABLENAME,ETYPE) values (23,'Name 1','TABLE NAME A','Etype A');
                    Insert into TABLE_B (ID,RTYPE,RNAME,TABLE_A_ID) values (26,'RTYPE A','RNAME A',23);
                    Insert into TABLE_B (ID,RTYPE,RNAME,TABLE_A_ID) values (61,'RTYPE B','RNAME B',23);
                    Insert into TABLE_C (ID,COMPNAME,CONC,TABLE_B_ID) values (83,'Comp Name AA',1.5,26);
                    Insert into TABLE_C (ID,COMPNAME,CONC,TABLE_B_ID) values (46,'Comp Name BB',2.2,26);
                    Insert into TABLE_C (ID,COMPNAME,CONC,TABLE_B_ID) values (101,'Comp Name CC',4.2,61);
                    • 7. Re: Pivot with ROW_NUMBER
                      680875
                      Sorry for the last post, there was some problem with data, NOW I am able to get the result... :)

                      I need some help.

                      a) As you mentioned
                      +"hard-coded 3 sets of columns from table_c. You can hard-code 10 sets, or however many you want.+
                      +Notice that the unused ones are blank. (That is, the last two columns above are never used, but they still appear in the output.)"+

                      Is there a way to get Maximum COLUMNS for "TABLE_C_1_COMPNAME", "TABLE_C_1_CONC" "TABLE_C_2_COMPNAME", "TABLE_C_2_CONC".... ONLY if it is there
                      means somehow to pass MAX NUMBER of columns and we should NOT get "unused blank ones"

                      b) Finally, as per my data we will pass TABLE_A Id (i.e. 23), I added "where a.id=23" after Join, just want to confirm, it is correct right ?
                      "FROM     table_a     a
                      JOIN     table_b     b ON a.id = b.table_a_id
                      JOIN     numbered_c c ON b.id = c.table_b_id
                      where a.id=23
                      GROUP BY     b.rtype;"
                      • 8. Re: Help for a query to add columns
                        680875
                        Hi,

                        I am trying to use "String Aggregation" as this looks simple.

                        We are using Oracle 10.2.0.1.0 what is the best way to create STRAGG....

                        I tried creatingAGG following this site http://orajourn.blogspot.com/2008/06/stragg.html i.e
                        CREATE OR REPLACE TYPE oracle.stragg_type AS TABLE OF VARCHAR2(4000); then executed..
                        CREATE OR REPLACE FUNCTION oracle.stragg (
                        nt_in IN stragg_type,
                        delimiter_in IN VARCHAR2 DEFAULT ','
                        ) RETURN varchar2 IS
                        v_idx PLS_INTEGER;
                        v_str varchar2(32000);
                        v_dlm VARCHAR2(10);
                        BEGIN
                        v_idx := nt_in.FIRST;
                        WHILE v_idx IS NOT NULL LOOP
                        v_str := v_str || v_dlm || nt_in(v_idx);
                        v_dlm := delimiter_in;
                        v_idx := nt_in.NEXT(v_idx);
                        END LOOP;
                        RETURN v_str;
                        END stragg;
                        When I try to execute this query, it says "ORA-06533: wrong number or types... in call to 'STRAGG'"..
                        • 9. Re: Pivot with ROW_NUMBER
                          Frank Kulash
                          Hi,
                          user10828299 wrote:
                          Sorry for the last post, there was some problem with data, NOW I am able to get the result... :)

                          I need some help.

                          a) As you mentioned
                          +"hard-coded 3 sets of columns from table_c. You can hard-code 10 sets, or however many you want.+
                          +Notice that the unused ones are blank. (That is, the last two columns above are never used, but they still appear in the output.)"+

                          Is there a way to get Maximum COLUMNS for "TABLE_C_1_COMPNAME", "TABLE_C_1_CONC" "TABLE_C_2_COMPNAME", "TABLE_C_2_CONC".... ONLY if it is there
                          means somehow to pass MAX NUMBER of columns and we should NOT get "unused blank ones"
                          See the script "How to Pivot a Table with a Dynamic Number of Columns", at the end of my first message in this thread.
                          b) Finally, as per my data we will pass TABLE_A Id (i.e. 23), I added "where a.id=23" after Join, just want to confirm, it is correct right ?
                          "FROM     table_a     a
                          JOIN     table_b     b ON a.id = b.table_a_id
                          JOIN     numbered_c c ON b.id = c.table_b_id
                          where a.id=23
                          GROUP BY     b.rtype;"
                          That looks good, but without seeing some sample data and the results you want to get from that data, I'm just guessing.
                          • 10. Re: Help for a query to add columns
                            Frank Kulash
                            Hi,

                            I use Tom Kyte's version of STRAGG, which I copied (without any changes) from asktom. I'll call this K_STRAGG in this message.
                            I haven't used the function from the link you posted, which I'll call O_STRAGG in this message. I may have time tomorrow to try it out.

                            In my first message, I gave you an example of how you might use K_STRAGG. Once you have it installed, and a synonym defined, it's just as easy to use as the built-in aggregate functions SUM, MIN, etc. Like all the built-in aggregates, K_STRAGG can only take one argument, so if you want a delimited other than the default one, you have to use REPLACE (or something similar) on the results.

                            You didn't post the code that produced the ORA-06533 error. From the link, it seems that the way to call it is
                            o_stragg (cast(multiset(select b from test) as oracle.STRAGG_TYPE),'|')
                            Is that what you were doing?
                            The complicated call seems to be the price you pay for getting that second argument.

                            At any rate, whenever you have a question about an error message, remember to post the complete query that caused it, and the complete error message (with line number).

                            When you use {code} tags on this site, there is no difference between the beginning and end tags. Do not include a slash before the word "code" in the end tag.
                            • 11. How do we know we need to add datafile in our tablespace
                              919726
                              hi
                              can anyone help me how do know to add more datafile in our tablespace when our tablespace got filled .