This discussion is archived
11 Replies Latest reply: Feb 27, 2012 11:18 PM by 919726 RSS

Help for a query to add columns

680875 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    hi
    can anyone help me how do know to add more datafile in our tablespace when our tablespace got filled .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points