1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Getting error as ORA-06550: line 5, column 16: PL/SQL: ORA-00913: too many values

Discussion in 'Oracle Apps Technical' started by Venkiibm, Jun 3, 2014.

  1. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    hi guys I was trying to insert the table based on the below select statement and getting error as toomany values .Could you please let me know how to resolve this issue.

    DROP TABLE Sumtemp;
    CREATE TABLE Sumtemp
    (
    temp_DIV_LOC_NBR NUMBER(6),
    temp_RECV_LOC_NBR NUMBER(6),
    temp_ZL_DIVN_NBR number(5),
    temp_var1 Number(01));

    Declare
    temp_var number (01);
    Begin
    LOOP
    inseRt INTO sumtemp (temp_DIV_LOC_NBR,temp_RECV_LOC_NBR,temp_ZL_DIVN_NBR,temp_var1)
    SELECT RQ.DIV_LOC_NBR,rq.RECV_LOC_NBR,rq.ZL_DIVN_NBR,

    CASE
    when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 1 END AS TEMP_VAR,
    CASE
    when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'Y' AND EXCLUDED_F ='N' THEN 2 END AS TEMP_VAR,
    case
    when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'N' AND EXCLUDED_F ='Y' THEN 3 END AS TEMP_VAR,
    case
    when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'Y' AND EXCLUDED_F ='Y' THEN 4 END AS TEMP_VAR,
    case
    when rq.COLLATERAL_UNITS > 0 and rq.COLLATERAL_F = 'Y'and FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 5 END AS TEMP_VAR,
    -- SUM(RQ.CALC_PHYSICAL_CNTR_COUNT) AS CARTON_UNTS ,
    -- SUM(RQ.TTL_BOOKED_QTY) AS TTL_BK_QTY

    FROM RCPT_QTY rq
    where rq.div_loc_nbr =12
    GROUP by rq.DIV_LOC_NBR,
    rq.RECV_LOC_NBR,
    RQ.RCPT_MTHD_CD,
    TEMP_VAR;
    END LOOP;
    End;
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    In your INSERT statement (and table), you have 4 columns only:

    while in your SELECT statement you have 8 columns (and may be more if I include the 2 commented lines):

    Code (SQL):
    SELECT
    RQ.DIV_LOC_NBR,   -- col1
    rq.RECV_LOC_NBR,  -- col2
    rq.ZL_DIVN_NBR,   -- col3
    CASE
    WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 1 END AS TEMP_VAR, -- col4
    CASE
    WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'Y' AND EXCLUDED_F ='N' THEN 2 END AS TEMP_VAR, -- col5
    CASE
    WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'N' AND EXCLUDED_F ='Y' THEN 3 END AS TEMP_VAR, -- col6
    CASE
    WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'Y' AND EXCLUDED_F ='Y' THEN 4 END AS TEMP_VAR, -- col7
    CASE
    WHEN rq.COLLATERAL_UNITS > 0 AND rq.COLLATERAL_F = 'Y'AND FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 5 END AS TEMP_VAR, -- col8
    -- SUM(RQ.CALC_PHYSICAL_CNTR_COUNT) AS CARTON_UNTS ,
    -- SUM(RQ.TTL_BOOKED_QTY) AS TTL_BK_QTY
    You need to have exactly the same number of columns in both statements.
    At first sight, the CASE statement is not correctly written; it should rather be:

    Code (SQL):
    INSERT
    INTO sumtemp
      (
        temp_DIV_LOC_NBR,
        temp_RECV_LOC_NBR,
        temp_ZL_DIVN_N BR,
        temp_var1
      )
    SELECT RQ.DIV_LOC_NBR, -- col1
      rq.RECV_LOC_NBR,     -- col2
      rq.ZL_DIVN_NBR,      -- col3
      CASE
        WHEN rq.COLLATERAL_F = 'N'
        AND FOREIGN_F        = 'N'
        AND EXCLUDED_F       ='N'
        THEN 1
        WHEN rq.COLLATERAL_F = 'N'
        AND FOREIGN_F        = 'Y'
        AND EXCLUDED_F       ='N'
        THEN 2
        WHEN rq.COLLATERAL_F = 'N'
        AND FOREIGN_F        = 'N'
        AND EXCLUDED_F       ='Y'
        THEN 3
        WHEN rq.COLLATERAL_F = 'N'
        AND FOREIGN_F        = 'Y'
        AND EXCLUDED_F       ='Y'
        THEN 4
        WHEN rq.COLLATERAL_UNITS > 0
        AND rq.COLLATERAL_F      = 'Y'
        AND FOREIGN_F            = 'N'
        AND EXCLUDED_F           ='N'
        THEN 5
      END AS TEMP_VAR
    FROM RCPT_QTY rq
    WHERE rq.div_loc_nbr =12 ;
     
  3. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thanks rajen ,It is working fine . I may need some more help when I going to write some additional logic in this query.

    Thanks a lot..
     
  4. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    I am new to oracle and converting all the mainframe logic into oracle ,So I will ask small answers please help me
    I am getting below error as ORA-06550: line 19, column 3:
    PL/SQL: ORA-00909: invalid number of arguments
    ORA-06550: line 5, column 1:

    And also when I am using group by clause I am not able to use the in other fileds selection which are not present in the group by clause
    could you please let me know how to select the other fields which are not in the group by clause. If I will use the all the select fields in the group by clause I am getting different results.
    Query as below ( I didn't include required other selected fields)
    Declare
    temp_var number (01);
    Begin
    -- LOOP
    INSERT into TempVenkat
    (
    temp_DIV_LOC_NBR,
    temp_RECV_LOC_NBR,
    temp_ZL_DIVN_NBR,
    temp_rcpt_mthd_cd,
    temp_tot_unit,
    temp_dolar_amt,
    temp_var1
    )
    SELECT DISTINCT RQ.DIV_LOC_NBR, -- col1
    rq.RECV_LOC_NBR, -- col2
    rq.ZL_DIVN_NBR, -- col3
    rq.rcpt_mthd_cd, --- col4
    sum((rq.TTL_BOOKED_QTY),
    sum(TTL_BOOKED_OWN_RETAIL),
    CASE
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'N'
    AND EXCLUDED_F ='N'
    THEN 1
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'Y'
    AND EXCLUDED_F ='N'
    THEN 2
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'N'
    AND EXCLUDED_F ='Y'
    THEN 3
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'Y'
    AND EXCLUDED_F ='Y'
    THEN 4
    WHEN rq.COLLATERAL_UNITS > 0
    AND rq.COLLATERAL_F = 'Y'
    AND FOREIGN_F = 'N'
    AND EXCLUDED_F ='N'
    THEN 5 END AS TEMP_VAR
    FROM RCPT_QTY rq
    WHERE BOOK_INTRANSIT_TS = ('23-May-14')
    order by
    rq.DIV_LOC_NBR,
    rq.RECV_LOC_NBR,
    RQ.RCPT_MTHD_CD,
    TEMP_VAR,
    GROUP by rq.DIV_LOC_NBR,
    rq.RECV_LOC_NBR,
    RQ.RCPT_MTHD_CD,
    TEMP_VAR;
    end;



    Error report:
    ORA-06550: line 19, column 3:
    PL/SQL: ORA-00909: invalid number of arguments
    ORA-06550: line 5, column 1:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    There an extra bracket in line:
    It should be:
    Code (SQL):
    SUM(rq.TTL_BOOKED_QTY),
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Other points to be noted:
    1)
    Not very clear what is required to SUM and insert in table TempVenkat.

    If table TempVenkat contains only the columns you have listed in your PL/SQL, then the current GROUP BY is enough.

    Of course, if you GROUP BY extra fields, the result of the SUM and the no. of rows returned might be different. But, then you may have more than 1 row for a given combination of (temp_DIV_LOC_NBR, temp_RECV_LOC_NBR, temp_ZL_DIVN_NBR, temp_rcpt_mthd_cd, temp_var1) AND you cannot use the same logic to populate your table (I mean : INSERT INTO .. SELECT ... statement).

    2) Your ORDER BY clause should be after GROUP_BY clause.
     
  7. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thanks rajen I removed and tried and getting error in the below area

    THEN 4
    WHEN rq.COLLATERAL_UNITS > 0
    AND rq.COLLATERAL_F ='Y'
    AND FOREIGN_F ='N

    errror as below Error report:
    ORA-06550: line 49, column 14:
    PL/SQL: ORA-00936: missing expression
    ORA-06550: line 5, column 1:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Venki,
    Please send me the complete code.
    In the portion you pasted, there's a missing single quote in the last line - I don't know if it's just a copy/paste error or not.

    Also send the table creation scripts so that I may create them & test it further.

    Thanks.
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Another error you'll hit soon:

    You can't have a GROUP BY a column alias (here for ex. for the CASE statement GROUP BY TEMP_VAR...).
    Query needs to be transformed to inline view ... or repeat the CASE statement in GROUP BY clause.
     
  10. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi Rajen,

    I have modified the query and above issue is resolved and I am getting error as
    ORA-06550: line 18, column 31:
    PL/SQL: ORA-00909: invalid number of arguments
    ORA-06550: line 5, column 1:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.


    Sql script is

    DROP table TempVenkat;

    create table TempVenkat
    (TEMP_DIV_LOC_NBR NUMBER(6),
    TEMP_RECV_LOC_NBR NUMBER(6),
    TEMP_ZL_DIVN_NBR NUMBER(5) ,
    TEMP_RCPT_MTHD_CD CHAR(1) ,
    TEMP_TOT_UNIT NUMBER(15),
    TEMP_DOLAR_AMT NUMBER(15,2) ,
    TEMP_VAR1 NUMBER(1)
    );
    Declare
    temp_var number (01);
    Begin
    -- LOOP
    INSERT into TempVenkat
    (
    temp_DIV_LOC_NBR,
    temp_RECV_LOC_NBR,
    temp_ZL_DIVN_NBR,
    temp_rcpt_mthd_cd,
    temp_tot_unit,
    temp_dolar_amt,
    temp_var1
    )
    SELECT DISTINCT RQ.DIV_LOC_NBR,
    rq.RECV_LOC_NBR,
    rq.ZL_DIVN_NBR,
    rq.rcpt_mthd_cd,
    sum((rq.TTL_BOOKED_QTY),
    sum(TTL_BOOKED_OWN_RETAIL),
    CASE
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'N'
    AND EXCLUDED_F ='N'
    THEN 1
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'Y'
    AND EXCLUDED_F ='N'
    THEN 2
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'N'
    AND EXCLUDED_F ='Y'
    THEN 3
    WHEN rq.COLLATERAL_F = 'N'
    AND FOREIGN_F = 'Y'
    AND EXCLUDED_F ='Y'
    THEN 4
    WHEN rq.COLLATERAL_UNITS > 0
    AND rq.COLLATERAL_F = 'Y'
    AND FOREIGN_F = 'N'
    AND EXCLUDED_F ='N'
    THEN 5 END AS TEMP_VAR
    FROM RCPT_QTY rq
    WHERE BOOK_INTRANSIT_TS = ('23-May-14')
    GROUP by rq.DIV_LOC_NBR,
    rq.RECV_LOC_NBR,
    rq.ZL_DIVN_NBR
    RQ.RCPT_MTHD_CD,
    TEMP_VAR,
    order by
    rq.DIV_LOC_NBR,
    rq.RECV_LOC_NBR,
    rq.ZL_DIVN_NBR,
    RQ.RCPT_MTHD_CD,
    TEMP_VAR;
    End;
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Venki,

    As explained above, you have an error in the line SUM((...) - extra bracket + the GROUP BY won't work.
    I've done a couple of corrections in the script and use an in-line view (I still have a doubt on the column BOOK_INTRANSIT_TS : is it DATE type or VARCHAR2 ? I suppose the data you have is stored in this format either as a date or a character ).

    Try the following:

    Code (SQL):
    --
    DECLARE
      temp_var NUMBER (1);
    BEGIN
      --
      INSERT
      INTO tempvenkat
        (
          temp_div_loc_nbr,
          temp_recv_loc_nbr,
          temp_zl_divn_nbr,
          temp_rcpt_mthd_cd,
          temp_tot_unit,
          temp_dolar_amt,
          temp_var1
        )
      SELECT div_loc_nbr,
        recv_loc_nbr,
        zl_divn_nbr,
        rcpt_mthd_cd,
        SUM(unit),
        SUM(amt),
        var
      FROM
        (SELECT rq.div_loc_nbr,
          rq.recv_loc_nbr,
          rq.zl_divn_nbr,
          rq.rcpt_mthd_cd,
          rq.ttl_booked_qty unit,
          rq.ttl_booked_own_retail amt,
          CASE
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'N'
            AND rq.excluded_f    = 'N'
            THEN 1
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'Y'
            AND rq.excluded_f    = 'N'
            THEN 2
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'N'
            AND rq.excluded_f    = 'Y'
            THEN 3
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'Y'
            AND rq.excluded_f    = 'Y'
            THEN 4
            WHEN rq.collateral_units > 0
            AND rq.collateral_f      = 'Y'
            AND rq.foreign_f         = 'N'
            AND rq.excluded_f        = 'N'
            THEN 5
          END AS var
        FROM rcpt_qty rq
        WHERE rq.book_intransit_ts = ('23-May-14')
        )
      GROUP BY div_loc_nbr,
        recv_loc_nbr,
        zl_divn_nbr,
        rcpt_mthd_cd,
        var
      ORDER BY div_loc_nbr,
        recv_loc_nbr,
        zl_divn_nbr,
        rcpt_mthd_cd,
        var;
    END;
    /
     
  12. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Rajen it is working fine . Book_intransit_TS is date so that this query is working as per your changes. Thanks for your help..
     
  13. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi Guys,

    I have one quick question in case when condition can we use like below if it is not can plz let me know how to pass multiple values after case END

    CASE
    WHEN tv.temp_var1 = 1 then 'N','N',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 2 then 'Y','N',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 3 then 'N','Y',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 4 then 'Y','Y',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 5 then 'N','N',tv.temp_var1,'Y'
    END AS TEMP_FROEIGN_F,TEMP_EXCLUDED_F,TEMP_COMBINATION_F,TEMP_COLLATERAL_F,


    I am using the above code in the below query and getting error as SQL Error: ORA-00905: missing keyword



    DROP table TempVenkat1;
    drop table tempsum483;
    create table TempVenkat1
    (TEMP_DIV_LOC_NBR NUMBER(6),
    TEMP_RECV_LOC_NBR NUMBER(6),
    TEMP_ZL_DIVN_NBR NUMBER(5),
    TEMP_RCPT_MTHD_CD CHAR(1) ,
    TEMP_VAR1 NUMBER(1),
    --TEMP_VAR2 NUMBER(1),
    TEMP_TOT_UNIT NUMBER(15),
    TEMP_CART_UNIT NUMBER(15,2),
    TEMP_DOLAR_AMT NUMBER(15,2)

    );

    create table tempsum483
    (TEMP_DIV_LOC_NBR NUMBER(6),
    TEMP_RCPT_QTY_SUMMARY_TYP_CD CHAR(2),
    TEMP_CREATE_TS DATE,
    TEMP_CREATE_USERID_V VARCHAR2(32),
    TEMP_LAST_UPD_TS DATE,
    TEMP_LAST_UPD_USERID_V VARCHAR2(32),
    TEMP_ZL_DIVN_NBR NUMBER(5),
    TEMP_AMC_DAY_I NUMBER(8),
    TEMP_AMC_WEEK_I NUMBER(7),
    TEMP_AMC_PERIOD_I NUMBER(6),
    TEMP_AMC_YEAR_I NUMBER(4),
    TEMP_RECORD_COUNT NUMBER(15),
    TEMP_TOT_UNIT NUMBER(15),
    TEMP_DOLAR_AMT NUMBER(15,2),
    TEMP_RECV_LOC_NBR NUMBER(6),
    TEMP_MRKNG_CD NUMBER(5),
    TEMP_RECEIPT_TRACK_F CHAR(1),
    TEMP_FOREIGN_F CHAR(1),
    TEMP_EXCLUDED_F CHAR(1) ,
    TEMP_COMBINATION_F CHAR(1) ,
    TEMP_COLLATERAL_F CHAR(1) ,
    TEMP_COLLATERAL_UNITS NUMBER(15),
    TEMP_CARTONS_COUNT NUMBER(15),
    TEMP_VAR1 NUMBER(1)
    --TEMP_VAR2 NUMBER(1)
    );
    DECLARE
    temp_var NUMBER (1);
    BEGIN
    --
    INSERT
    INTO tempvenkat1 tv
    (
    temp_div_loc_nbr,
    temp_recv_loc_nbr,
    temp_zl_divn_nbr,
    temp_rcpt_mthd_cd,
    temp_var1,
    -- temp_var2,
    temp_tot_unit,
    temp_cart_unit,
    temp_dolar_amt

    )
    SELECT distinct div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var,
    --- VAR1,
    SUM(unit),
    SUM(CARTUNIT),
    SUM(amt)
    FROM
    (SELECT rq.div_loc_nbr,
    rq.recv_loc_nbr,
    rq.zl_divn_nbr,
    rq.rcpt_mthd_cd,
    CASE
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'N'
    AND rq.excluded_f = 'N'
    THEN 1
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'Y'
    AND rq.excluded_f = 'N'
    THEN 2
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'N'
    AND rq.excluded_f = 'Y'
    THEN 3
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'Y'
    AND rq.excluded_f = 'Y'
    THEN 4
    WHEN (rq.collateral_units > 0)
    THEN 5
    END AS var, ------COMBINATION FLAG

    CASE WHEN rq.collateral_units > 0 then rq.collateral_units else
    rq.ttl_booked_qty end as unit,
    CASE WHEN rq.collateral_units > 0 THEN 0 ELSE
    rq.CALC_PHYSICAL_CNTR_COUNT END AS CARTUNIT,
    CASE WHEN rq.collateral_units > 0 THEN 0 ELSE
    rq.ttl_booked_own_retail END AS amt
    FROM rcpt_qty rq
    WHERE rq.book_intransit_ts = ('22-May-14')
    )
    GROUP BY div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var
    -- VAR1
    ORDER BY div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var ASC;
    END;
    /
    insert into tempsum483
    select
    tv.temp_div_loc_nbr,
    'DS', --as TEMP_RCPT_QTY_SUMMARY_TYP_CD,
    sysdate,-- as TEMP-CREATE_TS ,
    'Abhi',-- as TEMP-CREATE_USERID_V,
    sysdate,-- as TEMP-LAST_UPD_TS ,
    'XXXXXXXXXXX',-- as TEMP-LAST_UPD_USERID_V,
    tv.temp_zl_divn_nbr,

    (select am.amc_day_i from lu_amc_day@ldsd.link am where am.calendar_date = ('23-MAY-2014')),
    0 ,0 ,0 ,0,
    tv.temp_tot_unit,
    tv.temp_dolar_amt,
    tv.temp_recv_loc_nbr,
    CASE WHEN tv.temp_rcpt_mthd_cd = 'M' THEN 1
    WHEN tv.temp_rcpt_mthd_cd = 'O' THEN 2
    WHEN tv.temp_rcpt_mthd_cd = 'C' THEN 3
    WHEN tv.temp_rcpt_mthd_cd = 'N' THEN 4
    WHEN tv.temp_rcpt_mthd_cd = 'D' THEN 5
    END || tv.temp_var1, --- MARKING CODE IS COMBINATION OF RCVMETHOD+ COMBIN FLAG
    tv.temp_rcpt_mthd_cd,
    CASE
    WHEN tv.temp_var1 = 1 then 'N','N',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 2 then 'Y','N',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 3 then 'N','Y',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 4 then 'Y','Y',tv.temp_var1,'N'
    WHEN tv.temp_var1 = 5 then 'N','N',tv.temp_var1,'Y'
    END AS TEMP_FROEIGN_F,TEMP_EXCLUDED_F,TEMP_COMBINATION_F,TEMP_COLLATERAL_F,
    0, temp_cart_unit,
    tv.temp_var1 from tempvenkat1 tv;

    Merge INTO rcpt_qty_summary_483 rs
    using Tempsum483 TS
    ON (rs.DIV_LOC_NBR = ts.TEMP_DIV_LOC_NBR
    AND rs.RCPT_QTY_SUMMARY_TYP_CD = ts.TEMP_RCPT_QTY_SUMMARY_TYP_CD
    AND rs.ZL_DIVN_NBR = ts.TEMP_ZL_DIVN_NBR
    AND rs.RECV_LOC_NBR = ts.TEMP_RECV_LOC_NBR)
    WHEN MATCHED THEN
    UPDATE SET rs.AMC_DAY_I = ts.TEMP_AMC_DAY_I,
    rs.RECORD_COUNT = ts.TEMP_RECORD_COUNT,
    rs.UNITS=TS.TEMP_TOT_UNIT ,
    rs.DOLLARS = TS.TEMP_DOLAR_AMT,
    -- rs.RECV_LOC_NBR=TS.TEMP_RECV_LOC_NBR,
    rs.MRKNG_CD=ts.TEMP_MRKNG_CD,
    rs.FOREIGN_F=ts.TEMP_FOREIGN_F,
    rs.EXCLUDED_F=ts.TEMP_EXCLUDED_F,
    rs.COLLATERAL_F = ts.TEMP_COLLATERAL_F,
    rs.COMBINATION_F = ts.TEMP_VAR1,
    rs.COLLATERAL_UNITS= ts.TEMP_COLLATERAL_UNITS,
    rs.CARTONS_COUNT = ts.TEMP_CARTONS_COUNT

    WHEN NOT MATCHED THEN
    INSERT (DIV_LOC_NBR,
    RCPT_QTY_SUMMARY_TYP_CD,
    CREATE_TS,CREATE_USERID_V,
    LAST_UPD_TS,LAST_UPD_USERID_V,
    ZL_DIVN_NBR,
    AMC_DAY_I,
    AMC_WEEK_I,
    AMC_PERIOD_I,
    AMC_YEAR_I,
    RECORD_COUNT,
    UNITS,
    DOLLARS,
    RECV_LOC_NBR,
    MRKNG_CD,
    RECEIPT_TRACK_F,
    FOREIGN_F,EXCLUDED_F,
    COMBINATION_F,
    COLLATERAL_F,
    COLLATERAL_UNITS,
    CARTONS_COUNT)
    VALUES(TEMP_DIV_LOC_NBR,
    TEMP_RCPT_QTY_SUMMARY_TYP_CD,
    TEMP_CREATE_TS,
    TEMP_CREATE_USERID_V,
    TEMP_LAST_UPD_TS,
    TEMP_LAST_UPD_USERID_V,
    TEMP_ZL_DIVN_NBR,
    TEMP_AMC_DAY_I,
    TEMP_AMC_WEEK_I,
    TEMP_AMC_PERIOD_I,
    TEMP_AMC_YEAR_I,
    TEMP_RECORD_COUNT,
    TEMP_TOT_UNIT,
    TEMP_DOLAR_AMT,
    TEMP_RECV_LOC_NBR,
    TEMP_MRKNG_CD ,
    TEMP_RECEIPT_TRACK_F,
    TEMP_FOREIGN_F ,
    TEMP_EXCLUDED_F ,
    TEMP_COMBINATION_F,
    TEMP_COLLATERAL_F,
    TEMP_COLLATERAL_UNITS,
    TEMP_CARTONS_COUNT);



    ---select * from TempSUM483;
     
  14. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    No Venki, this is not possible: CASE function can only return a single value.

    Either you repeat the logic for every value to be determined or you use CURSOR-LOOP-FETCH and use IF-THEN-ELSE conditions to assign or set the various values.
     
    Venkiibm likes this.
  15. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi Guys,

    I have below query need to know the number of the record which are grouped in the below i am getting below error for the record count

    Please let me know how to get the record count .

    Error report:
    ORA-00937: not a single-group group function
    ORA-06512: at line 4
    00937. 00000 - "not a single-group group function"
    *Cause:
    *Action:

    DROP table TempCALC;
    --drop table tempsum483;
    create table TempCALC
    (TEMP_DIV_LOC_NBR NUMBER(6),
    TEMP_RECV_LOC_NBR NUMBER(6),
    TEMP_ZL_DIVN_NBR NUMBER(5),
    TEMP_RCPT_MTHD_CD CHAR(1) ,
    TEMP_VAR1 NUMBER(1),
    --TEMP_VAR2 NUMBER(1),
    TEMP_REC_CNT NUMBER(15),
    TEMP_TOT_UNIT NUMBER(15),
    TEMP_CART_UNIT NUMBER(15,2),
    TEMP_DOLAR_AMT NUMBER(15,2)

    );


    DECLARE
    temp_var NUMBER (1);
    BEGIN
    INSERT
    INTO tempCALC tc
    (
    temp_div_loc_nbr,
    temp_recv_loc_nbr,
    temp_zl_divn_nbr,
    temp_rcpt_mthd_cd,
    temp_var1,
    -- temp_var2,
    temp_rec_cnt,
    temp_tot_unit,
    temp_cart_unit,
    temp_dolar_amt

    )
    SELECT distinct div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var,
    --- VAR1,
    REC_CNT,
    SUM(unit),
    SUM(CARTUNIT),
    SUM(amt)
    FROM
    (SELECT rq.div_loc_nbr,
    rq.recv_loc_nbr,
    rq.zl_divn_nbr,
    rq.rcpt_mthd_cd,
    CASE
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'N'
    AND rq.excluded_f = 'N'
    THEN 1
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'Y'
    AND rq.excluded_f = 'N'
    THEN 2
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'N'
    AND rq.excluded_f = 'Y'
    THEN 3
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'Y'
    AND rq.excluded_f = 'Y'
    THEN 4
    WHEN (rq.collateral_units > 0)
    THEN 5
    ELSE 0

    END AS var, ------COMBINATION FLAG
    COUNT(*) AS REC_CNT,

    CASE WHEN rq.collateral_units > 0 then rq.collateral_units else
    rq.ttl_booked_qty end as unit,
    CASE WHEN rq.collateral_units > 0 THEN 0 ELSE
    rq.CALC_PHYSICAL_CNTR_COUNT END AS CARTUNIT,
    CASE WHEN rq.collateral_units > 0 THEN 0 ELSE
    rq.ttl_booked_own_retail END AS amt
    FROM rcpt_qty rq
    WHERE rq.book_intransit_ts = ('22-May-14')
    )
    GROUP BY div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var
    -- VAR1
    ORDER BY div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var ASC;
    END;
    /
     
  16. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Venki,

    You just have to remove the COUNT(*) from the in-line view and put it in the main SELECT, i.e.:

    Code (SQL):
    DECLARE
      temp_var NUMBER (1);
    BEGIN
      INSERT
      INTO tempCALC tc
        (
          temp_div_loc_nbr,
          temp_recv_loc_nbr,
          temp_zl_divn_nbr,
          temp_rcpt_mthd_cd,
          temp_var1,
          -- temp_var2,
          temp_rec_cnt,
          temp_tot_unit,
          temp_cart_unit,
          temp_dolar_amt
        )
      SELECT DISTINCT div_loc_nbr,
        recv_loc_nbr,
        zl_divn_nbr,
        rcpt_mthd_cd,
        var,
        --- VAR1,
        COUNT(*) REC_CNT,
        SUM(unit),
        SUM(CARTUNIT),
        SUM(amt)
      FROM
        (SELECT rq.div_loc_nbr,
          rq.recv_loc_nbr,
          rq.zl_divn_nbr,
          rq.rcpt_mthd_cd,
          CASE
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'N'
            AND rq.excluded_f    = 'N'
            THEN 1
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'Y'
            AND rq.excluded_f    = 'N'
            THEN 2
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'N'
            AND rq.excluded_f    = 'Y'
            THEN 3
            WHEN rq.collateral_f = 'N'
            AND rq.foreign_f     = 'Y'
            AND rq.excluded_f    = 'Y'
            THEN 4
            WHEN (rq.collateral_units > 0)
            THEN 5
            ELSE 0
          END      AS var, ------COMBINATION FLAG
          --COUNT(*) AS REC_CNT,
          CASE
            WHEN rq.collateral_units > 0
            THEN rq.collateral_units
            ELSE rq.ttl_booked_qty
          END AS unit,
          CASE
            WHEN rq.collateral_units > 0
            THEN 0
            ELSE rq.calc_physical_cntr_count
          END AS cartunit,
          CASE
            WHEN rq.collateral_units > 0
            THEN 0
            ELSE rq.ttl_booked_own_retail
          END AS amt
        FROM rcpt_qty rq
        WHERE rq.book_intransit_ts = ('22-May-14')
        )
      GROUP BY div_loc_nbr,
        recv_loc_nbr,
        zl_divn_nbr,
        rcpt_mthd_cd,
        var
        -- VAR1
      ORDER BY div_loc_nbr,
        recv_loc_nbr,
        zl_divn_nbr,
        rcpt_mthd_cd,
        var ASC;
    END;
    /
     
    Venkiibm likes this.
  17. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    i have below query which is inserting or updating the table based on the some conditions. When i am running the below query it is not displaying saying that no of rows merged and inserted like below

    I need to get display saying that how many rows inserted/deleted from the rcpt_qty_summary_483 table.

    SET SERVEROUTPUT ON;
    --Drop table TempDay;
    Drop table tempcalc;
    Drop table tempsum483;
    --CREATE TABLE TempDay
    -- (
    -- temp_date date,
    -- temp_flag char(4),
    -- temp_period number(2)
    --);
    create table Tempcalc
    (TMP_DIV_LOC_NBR NUMBER(6),
    TMP_RECV_LOC_NBR NUMBER(6),
    TMP_ZL_DIVN_NBR NUMBER(5),
    TMP_RCPT_MTHD_CD CHAR(1) ,
    TMP_REC_CNT NUMBER(15),
    TMP_VAR1 NUMBER(1),
    TMP_TOT_UNIT NUMBER(15),
    TMP_CART_UNIT NUMBER(15,2),
    TMP_DOLAR_AMT NUMBER(15,2)
    --TEMP_PERIOD NUMBER(2)
    );
    create table tempsum483
    (TEMP_DIV_LOC_NBR NUMBER(6),
    TEMP_RCPT_QTY_SUMMARY_TYP_CD CHAR(2),
    TEMP_CREATE_TS DATE,
    TEMP_CREATE_USERID_V VARCHAR2(32),
    TEMP_LAST_UPD_TS DATE,
    TEMP_LAST_UPD_USERID_V VARCHAR2(32),
    TEMP_ZL_DIVN_NBR NUMBER(5),
    TEMP_AMC_DAY_I NUMBER(8),
    TEMP_AMC_WEEK_I NUMBER(7),
    TEMP_AMC_PERIOD_I NUMBER(6),
    TEMP_AMC_YEAR_I NUMBER(4),
    TEMP_RECORD_COUNT NUMBER(15),
    TEMP_TOT_UNIT NUMBER(15),
    TEMP_DOLAR_AMT NUMBER(15,2),
    TEMP_RECV_LOC_NBR NUMBER(6),
    TEMP_MRKNG_CD NUMBER(5),
    TEMP_RECEIPT_TRACK_F CHAR(1),
    TEMP_FOREIGN_F CHAR(1),
    TEMP_EXCLUDED_F CHAR(1) ,
    TEMP_COMBINATION_F CHAR(1) ,
    TEMP_COLLATERAL_F CHAR(1) ,
    TEMP_COLLATERAL_UNITS NUMBER(15),
    TEMP_CARTONS_COUNT NUMBER(15),
    TEMP_VAR1 NUMBER(1)
    );

    Declare
    temp_year number(4);
    temp_amc_year number(4);
    temp_week number(02);
    temp_period number(02);
    temp_week_date DATE;
    temp_period_date DATE;
    temp_ppr_date DATE;
    n number := 1;
    BEGIN
    -- Get Year, Peroid, week from AMC

    select am.amc_year_i,am.cal_year,am.period,am.week into temp_amc_year,temp_year,temp_period,temp_week from LU_amc_DAY@LDSD.LINK am
    where am.calendar_date = ('23-MAY-1996');

    -- Current Day
    --Insert into TempDay
    --select am.calendar_date,'CD',--current day
    --temp_period from LU_amc_DAY@LDSD.LINK am
    --where am.calendar_date = ('23-MAY-1996');

    --for n in 1..12 LOOP
    -- if n=12 then
    -- insert into TempDay
    -- select am.calendar_date,'PB',12 from LU_amc_DAY@LDSD.LINK am
    -- where am.cal_year = temp_amc_year+1 and am.period = 12 and am.week=1 and am.day_of_week =1;
    -- insert into TempDay
    -- select am.calendar_date,'PE',12 from LU_amc_DAY@LDSD.LINK am
    -- where am.cal_year = temp_amc_year+1 and am.period = 12 and am.week=4 and am.day_of_week =7;
    -- else
    -- insert into TempDay
    -- select am.calendar_date,'PB',n from LU_amc_DAY@LDSD.LINK am
    -- where am.cal_year = temp_amc_year and am.period = n and am.week=1 and am.day_of_week =1;
    -- insert into TempDay
    -- select am.calendar_date,'PE',n from LU_amc_DAY@LDSD.LINK am
    -- where am.cal_year = temp_amc_year and am.period = n and am.week=4 and am.day_of_week =7;
    -- end if;
    -- end loop;
    for n in 1..temp_period LOOP
    --insert into
    INSERT
    INTO tempcalc tv
    (
    tv.tmp_div_loc_nbr,
    tv.tmp_recv_loc_nbr,
    tv.tmp_zl_divn_nbr,
    tv.tmp_rcpt_mthd_cd,
    tv.tmp_rec_cnt,
    tv.tmp_var1,
    tv.tmp_tot_unit,
    tv.tmp_cart_unit,
    tv.tmp_dolar_amt
    -- temp_period
    )
    SELECT distinct
    div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    count(*) as rec_cnt,
    var,
    SUM(unit),
    SUM(CARTUNIT),
    SUM(amt)
    FROM
    (SELECT rq.div_loc_nbr,
    rq.recv_loc_nbr,
    rq.zl_divn_nbr,
    rq.rcpt_mthd_cd,
    CASE
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'N'
    AND rq.excluded_f = 'N'
    THEN 1
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'Y'
    AND rq.excluded_f = 'N'
    THEN 2
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'N'
    AND rq.excluded_f = 'Y'
    THEN 3
    WHEN rq.collateral_f = 'N'
    AND rq.foreign_f = 'Y'
    AND rq.excluded_f = 'Y'
    THEN 4
    WHEN (rq.collateral_units > 0)
    THEN 5
    ELSE 0

    END AS var, ------COMBINATION FLAG

    CASE WHEN rq.collateral_units > 0 then rq.collateral_units else
    rq.ttl_booked_qty end as unit,
    CASE WHEN rq.collateral_units > 0 THEN 0 ELSE
    rq.CALC_PHYSICAL_CNTR_COUNT END AS CARTUNIT,
    CASE WHEN rq.collateral_units > 0 THEN 0 ELSE
    rq.ttl_booked_own_retail END AS amt
    FROM rcpt_qty rq
    WHERE rq.book_intransit_ts >= (select temp_date from TempDay where temp_flag ='PB' and
    temp_period = n)
    and rq.book_intransit_ts <= (select temp_date from TempDay where temp_flag ='PE' and
    temp_period = n)


    )

    GROUP BY
    div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var
    -- VAR1
    ORDER BY div_loc_nbr,
    recv_loc_nbr,
    zl_divn_nbr,
    rcpt_mthd_cd,
    var ASC;
    Insert into tempsum483
    select
    tv.tmp_div_loc_nbr,
    n, --as TEMP_RCPT_QTY_SUMMARY_TYP_CD,
    sysdate, -- as TEMP-CREATE_TS ,
    USER , -- as TEMP-CREATE_USERID_V,
    sysdate, -- as TEMP-LAST_UPD_TS ,
    USER , -- as TEMP-LAST_UPD_USERID_V,
    tv.tmp_zl_divn_nbr,
    0,
    0,
    (select am.amc_period_i from lu_amc_day@ldsd.link am where am.calendar_date = ('23-MAY-2014')) ---- this we need to different period dates
    ,0,tv.tmp_rec_cnt,
    tv.tmp_tot_unit,
    tv.tmp_dolar_amt,
    tv.tmp_recv_loc_nbr,
    CASE WHEN tv.tmp_rcpt_mthd_cd = 'M' THEN 1
    WHEN tv.tmp_rcpt_mthd_cd = 'O' THEN 2
    WHEN tv.tmp_rcpt_mthd_cd = 'C' THEN 3
    WHEN tv.tmp_rcpt_mthd_cd = 'N' THEN 4
    WHEN tv.tmp_rcpt_mthd_cd = 'D' THEN 5
    END || tv.tmp_var1, --- MARKING CODE IS COMBINATION OF RCVMETHOD+ COMBIN FLAG
    tv.tmp_rcpt_mthd_cd,
    CASE
    WHEN tv.tmp_var1 = 1 or tv.tmp_var1 = 3 or tv.tmp_var1 = 5 then 'N'
    WHEN tv.tmp_var1 = 2 or tv.tmp_var1 = 4 then 'Y' else ' ' end as TEMP_FROEIGN_F,
    CASE
    WHEN tv.tmp_var1 = 1 or tv.tmp_var1 = 2 or tv.tmp_var1 = 5 then 'N'
    WHEN tv.tmp_var1 = 3 or tv.tmp_var1 = 4 then 'Y' else ' ' end as TEMP_EXCLUDED_F,
    tv.tmp_var1,
    CASE
    WHEN tv.tmp_var1 = 1 or tv.tmp_var1 = 2 or tv.tmp_var1 = 3 or tv.tmp_var1 = 4 then 'N'
    WHEN tv.tmp_var1 = 5 then 'Y' else ' ' end as TEMP_COLLATERAL_F,
    0, tmp_cart_unit,
    tv.tmp_var1 from tempcalc tv;

    end loop;

    Merge INTO rcpt_qty_summary_483 rs
    using Tempsum483 TS
    ON (rs.DIV_LOC_NBR = ts.TEMP_DIV_LOC_NBR
    AND rs.RCPT_QTY_SUMMARY_TYP_CD = ts.TEMP_RCPT_QTY_SUMMARY_TYP_CD
    AND rs.ZL_DIVN_NBR = ts.TEMP_ZL_DIVN_NBR
    AND rs.RECV_LOC_NBR = ts.TEMP_RECV_LOC_NBR
    AND rs.RECEIPT_TRACK_F = ts.TEMP_RECEIPT_TRACK_F
    AND rs.FOREIGN_F = ts.TEMP_FOREIGN_F
    AND rs.EXCLUDED_F = ts.TEMP_EXCLUDED_F
    AND rs.COMBINATION_F = ts.TEMP_COMBINATION_F
    AND rs.COLLATERAL_F = ts.TEMP_COLLATERAL_F)

    WHEN MATCHED THEN
    UPDATE SET rs.LAST_UPD_TS = ts.TEMP_LAST_UPD_TS,
    rs.LAST_UPD_USERID_V = ts.TEMP_LAST_UPD_USERID_V,
    rs.AMC_PERIOD_I = ts.TEMP_AMC_PERIOD_I,
    rs.RECORD_COUNT = ts.TEMP_RECORD_COUNT,
    rs.UNITS=TS.TEMP_TOT_UNIT ,
    rs.DOLLARS = TS.TEMP_DOLAR_AMT,
    rs.COLLATERAL_UNITS= ts.TEMP_COLLATERAL_UNITS,
    rs.CARTONS_COUNT = ts.TEMP_CARTONS_COUNT

    WHEN NOT MATCHED THEN
    INSERT (DIV_LOC_NBR,
    RCPT_QTY_SUMMARY_TYP_CD,
    CREATE_TS,CREATE_USERID_V,
    LAST_UPD_TS,LAST_UPD_USERID_V,
    ZL_DIVN_NBR,
    AMC_DAY_I,
    AMC_WEEK_I,
    AMC_PERIOD_I,
    AMC_YEAR_I,
    RECORD_COUNT,
    UNITS,
    DOLLARS,
    RECV_LOC_NBR,
    MRKNG_CD,
    RECEIPT_TRACK_F,
    FOREIGN_F,EXCLUDED_F,
    COMBINATION_F,
    COLLATERAL_F,
    COLLATERAL_UNITS,
    CARTONS_COUNT)
    VALUES(ts.TEMP_DIV_LOC_NBR,
    TS.TEMP_RCPT_QTY_SUMMARY_TYP_CD,
    TEMP_CREATE_TS,
    TEMP_CREATE_USERID_V,
    TEMP_LAST_UPD_TS,
    TEMP_LAST_UPD_USERID_V,
    TEMP_ZL_DIVN_NBR,
    TEMP_AMC_DAY_I,
    TEMP_AMC_WEEK_I,
    TEMP_AMC_PERIOD_I,
    TEMP_AMC_YEAR_I,
    TEMP_RECORD_COUNT,
    TEMP_TOT_UNIT,
    TEMP_DOLAR_AMT,
    TS.TEMP_RECV_LOC_NBR,
    TEMP_MRKNG_CD ,
    TS.TEMP_RECEIPT_TRACK_F,
    TEMP_FOREIGN_F ,
    TEMP_EXCLUDED_F ,
    TEMP_COMBINATION_F,
    TEMP_COLLATERAL_F,
    TEMP_COLLATERAL_UNITS,
    TEMP_CARTONS_COUNT);
    end;
     
  18. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi guys,

    I have used the DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.'); and got the number of rows inseted.
     
  19. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Venki,

    SQL%ROWCOUNT will only give you the no. of rows merged, and in your case, I suppose rows have only been inserted and not updated.
    There is no way to split or get the 2 counts separately: no. of rows inserted and no. of rows updated.

    If you want to get these figures, then you'll have to build that logic in your MERGE by adding an extra column in your target table and updating it with a flag to differentiate it from those inserted (where the flag would have a different value of course or null).
     
    Venkiibm likes this.
  20. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Rajen , I will try your approach.