Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Stored Procedure Problem

    I have the following stored procedure:

    Code:
    CREATE PROCEDURE contract_activation_incentives_report (comm_plan varchar(20), dealer_id varchar(8), reg_status char(1), report_type varchar(5), start_date varchar(10), end_date varchar(10)) 
        RETURNING varchar(8), varchar(12), date, varchar(20), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2), varchar(12), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2);
        DEFINE rep_dealer varchar(8); DEFINE rep_ds_msisdn varchar(12); DEFINE rep_trans_date date; DEFINE rep_dealsh varchar(20); DEFINE rep_conbon decimal(16,2); DEFINE rep_simsupp decimal(16,2); DEFINE rep_hwsupp decimal(16,2); DEFINE rep_redsub decimal(16,2);     
        DEFINE rep_vspsupp decimal(16,2); DEFINE rep_status varchar(12); DEFINE rep_addsupp decimal(16,2); DEFINE rep_other decimal(16,2); DEFINE grossupp decimal(16,2); DEFINE sbd_package_code decimal(16,2);
        (SELECT *  FROM ds_incentive_dt WHERE ds_trans_date >= '01-04-08' AND ds_trans_date <= '30-04-08' AND ds_dealer_id = 'VDSRS' ) INTO TEMP temp_ds_incentive_dt WITH NO LOG;
        (SELECT ds_msisdn||ds_trans_date||ds_vsp_ref||ds_dealer_id  convalueconbon FROM temp_ds_incentive_dt WHERE ds_inc_cateGory = 'ACTBON'  AND ds_inc_type = 'CONBON') INTO TEMP temp_conbonexists WITH NO LOG;
        (SELECT ds_msisdn||ds_trans_date||ds_vsp_ref||ds_dealer_id convalueall  FROM temp_ds_incentive_dt, dealer_master  WHERE ds_dealer_id = dealer_id  AND ds_msisdn||ds_trans_date||ds_vsp_ref||ds_dealer_id  IN  (SELECT convalueconbon   FROM temp_conbonexists)) INTO TEMP temp_all WITH NO LOG;
        (SELECT ds_dealer_id rep_dealer, ds_msisdn rep_ds_msisdn, ds_trans_date rep_trans_date, ds_vsp_ref rep_dealsh , ds_msisdn||ds_trans_date||ds_vsp_ref||ds_dealer_id rep_convalue, ds_value rep_conbon, ds_status rep_status, sbd_package_code  FROM temp_ds_incentive_dt, sbd_sub_dets WHERE ds_inc_cateGory = 'ACTBON'  AND sbd_dialling_no = ds_msisdn  AND ds_inc_type = 'CONBON'  AND ds_msisdn||ds_trans_date||ds_vsp_ref||ds_dealer_id IN  (SELECT convalueall   FROM temp_all)  AND ds_msisdn||ds_trans_date||ds_vsp_ref||ds_dealer_id  IN  (SELECT convalueconbon  FROM temp_conbonexists)) INTO TEMP temp_withc WITH NO LOG;
        (SELECT rep_dealer, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_convalue, rep_conbon,ds_value rep_simsupp, sbd_sub_dets.sbd_package_code  FROM temp_withc, OUTER temp_ds_incentive_dt, OUTER sbd_sub_dets  WHERE Rep_ds_msisdn = sbd_dialling_no AND ds_inc_cateGory = 'ACTBON'  AND ds_inc_type = 'SIMSUPP'  AND rep_ds_msisdn = ds_msisdn  AND rep_trans_date = ds_trans_date  AND rep_dealsh = ds_vsp_ref)  INTO TEMP temp_withcs WITH NO LOG;
        (SELECT rep_dealer,rep_ds_msisdn,rep_trans_date,rep_dealsh , rep_convalue, rep_conbon,rep_simsupp,ds_value rep_hwsupp, sbd_package_code  FROM temp_withcs, OUTER temp_ds_incentive_dt  WHERE ds_inc_cateGory = 'ACTBON'  AND ds_inc_type = 'HWSUPP'  AND rep_ds_msisdn = ds_msisdn  AND rep_trans_date = ds_trans_date  AND  rep_dealsh = ds_vsp_ref)  INTO TEMP temp_withcsh WITH NO LOG;
        (SELECT rep_dealer, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_convalue, rep_conbon,rep_simsupp, rep_hwsupp , ds_value rep_redsub,sbd_package_code  FROM temp_withcsh, OUTER temp_ds_incentive_dt  WHERE ds_inc_cateGory = 'ACTBON'  AND ds_inc_type = 'REDSUB'  AND rep_ds_msisdn = ds_msisdn  AND rep_trans_date = ds_trans_date  AND rep_dealsh = ds_vsp_ref)  INTO TEMP temp_withcshr WITH NO LOG;
        (SELECT rep_dealer, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_convalue, ds_status rep_status, rep_simsupp, rep_hwsupp, rep_redsub, rep_conbon, sbd_package_code,SUM(ds_value) rep_vspsupp  FROM temp_withcshr, OUTER temp_ds_incentive_dt  WHERE ds_inc_cateGory = 'ACTBON'  AND ds_inc_type in ('VSPSUPP','OBF','SWAP')  AND rep_dealer = ds_dealer_id AND rep_ds_msisdn = ds_msisdn  AND rep_trans_date = ds_trans_date  GROUP BY 1,2,3,4,5,6,7,8,9,10,11)  INTO TEMP temp_withcshrvo WITH NO LOG;
        (SELECT rep_dealer, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_convalue, rep_conbon, rep_status, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp,ds_value rep_addsupp, sbd_package_code  FROM temp_withcshrvo, OUTER temp_ds_incentive_dt  WHERE ds_inc_cateGory = 'ACTBON'  AND ds_inc_type = 'ADDSUPP'  AND rep_ds_msisdn = ds_msisdn  AND rep_trans_date = ds_trans_date  AND  rep_dealsh = ds_vsp_ref)  INTO TEMP temp_withcshrvoa WITH NO LOG;
        (SELECT rep_dealer,rep_ds_msisdn,rep_trans_date,rep_dealsh ,rep_conbon, rep_status, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_addsupp, sbd_package_code, SUM(ds_value) rep_other  FROM temp_withcshrvoa, OUTER temp_ds_incentive_dt  WHERE ds_inc_cateGory = 'ACTBON'  AND (ds_inc_type = 'ADJUST' OR ds_inc_type = 'CORRECT')  AND rep_ds_msisdn = ds_msisdn  AND rep_trans_date = ds_trans_date  AND rep_dealsh = ds_vsp_ref  GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12) INTO TEMP temp_withcshrvoao WITH NO LOG;
        FOREACH c1 FOR
            SELECT rep_dealer, name rep_name, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_conbon, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_status, rep_addsupp, rep_other, +(NVL(rep_conbon,0) + NVL(rep_simsupp,0) + NVL(rep_hwsupp,0) + NVL(rep_redsub,0) + NVL(rep_vspsupp,0) + NVL(rep_addsupp,0) + NVL(rep_other,0)) grossupp,sbd_package_code 
                INTO  rep_dealer, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_conbon, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_status, rep_addsupp, rep_other, grossupp, sbd_package_code
                FROM temp_withcshrvoao, OUTER(dealer_master, OUTER slcustm) WHERE rep_dealer = dealer_id AND sub_acc_no = customer Order by rep_trans_date,rep_ds_msisdn
            RETURN rep_dealer, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_conbon, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_status, rep_addsupp, rep_other, grossupp, sbd_package_code WITH resume;
        END FOREACH;
    END PROCEDURE;
    But when I run it:

    Code:
    SELECT * FROM TABLE(contract_activation_incentives_report('', '', '', '', '', '')) (rep_dealer, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_conbon, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_status, rep_addsupp, rep_other, grossupp, sbd_package_code)
    I get the following error:

    >[Error] Script lines: 1-1 --------------------------
    Virtual column must have explicit name.

    I googled the error and found that it occurs when not providing explicit names on columns in your select statement that are used in aggregate functions. Unless I missed it I see no missing names for columns in the select statment...

    Any ideas as to what is going wrong?

  2. #2
    Join Date
    Nov 2011
    Posts
    4
    I've made progress - the original error was due to the defined variables having the same names as the column names. I renamed those variables and that error went away.

    I am now not getting any data out of the stored procedure - I'm not sure if the structure of my foreach loop is correct - can someone have a look and tell me if they can see anything wrong:

    Code:
    CREATE PROCEDURE test_contract_activation_incentives_report()
        RETURNING varchar(8), varchar(15), varchar(12), date, varchar(20), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2), varchar(12), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2);
        DEFINE a_rep_dealer varchar(8); DEFINE a_rep_name varchar(50); DEFINE a_rep_ds_msisdn varchar(12); DEFINE a_rep_trans_date date; DEFINE a_rep_dealsh varchar(20); DEFINE a_rep_conbon decimal(16,2); DEFINE a_rep_simsupp decimal(16,2); DEFINE a_rep_hwsupp decimal(16,2); DEFINE a_rep_redsub decimal(16,2);
        DEFINE a_rep_vspsupp decimal(16,2); DEFINE a_rep_status varchar(12); DEFINE a_rep_addsupp decimal(16,2); DEFINE a_rep_other decimal(16,2); DEFINE a_grossupp decimal(16,2); DEFINE a_sbd_package_code decimal(16,2);
       
        set debug file to 'debug.txt';
    	trace on;
    
        trace 'Before for each';
        FOREACH C1 FOR
            SELECT rep_dealer, name rep_name, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_conbon, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_status, rep_addsupp, rep_other, +(NVL(rep_conbon,0) + NVL(rep_simsupp,0) + NVL(rep_hwsupp,0) + NVL(rep_redsub,0) + NVL(rep_vspsupp,0) + NVL(rep_addsupp,0) + NVL(rep_other,0)) grossupp, sbd_package_code
                INTO  a_rep_dealer, a_rep_name, a_rep_ds_msisdn, a_rep_trans_date, a_rep_dealsh, a_rep_conbon, a_rep_simsupp, a_rep_hwsupp, a_rep_redsub, a_rep_vspsupp, a_rep_status, a_rep_addsupp, a_rep_other, a_grossupp, a_sbd_package_code
                FROM temp_withcshrvoao a, OUTER(dealer_master b, OUTER slcustm c) 
                WHERE a.rep_dealer = b.dealer_id AND b.sub_acc_no = c.customer 
                ORDER BY rep_trans_date, rep_ds_msisdn
        trace 'foreach';
            RETURN a_rep_dealer, a_rep_name, a_rep_ds_msisdn, a_rep_trans_date, a_rep_dealsh, a_rep_conbon, a_rep_simsupp, a_rep_hwsupp, a_rep_redsub, a_rep_vspsupp, a_rep_status, a_rep_addsupp, a_rep_other, a_grossupp, a_sbd_package_code WITH resume;
        END FOREACH;
        trace 'After foreach';
    END PROCEDURE;
    I've tried the above with
    Code:
    C1 FOR
    amd without - I'm not sure what this does and what the difference is but either way no data is returned.

    If I run all the queries that create the temp tables and execute this select statement outside of the foreach structure I get over 8000 records.

    Any ideas would be highly appreciated!

  3. #3
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,


    to understand what happens, debug your SP.
    Code:
    set debug file to "/tmp/yourfile.txt";
    trace on;
    Your SP code.
    the debug will start where "trace on" is set. You can stop it
    with "trace off" ;
    Read the file "/tmp/yourfile.txt" to understand what happens.

    Don't forget to remove the trace when your issue is resolved.

  4. #4
    Join Date
    Nov 2011
    Posts
    4
    Hi - I tried that but there doesn't seem to be any relevant information regarding the lack of data. This is incredibly frustrating... Any way to get more debug information?

    I setup this test procedure:

    Code:
    CREATE PROCEDURE test_contract_activation_incentives_report()
        RETURNING varchar(8), varchar(15), varchar(12), date, varchar(20), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2), varchar(12), decimal(16,2), decimal(16,2), decimal(16,2), decimal(16,2);
        DEFINE a_rep_dealer varchar(8); DEFINE a_rep_name varchar(50); DEFINE a_rep_ds_msisdn varchar(12); DEFINE a_rep_trans_date date; DEFINE a_rep_dealsh varchar(20); DEFINE a_rep_conbon decimal(16,2); DEFINE a_rep_simsupp decimal(16,2); DEFINE a_rep_hwsupp decimal(16,2); DEFINE a_rep_redsub decimal(16,2);
        DEFINE a_rep_vspsupp decimal(16,2); DEFINE a_rep_status varchar(12); DEFINE a_rep_addsupp decimal(16,2); DEFINE a_rep_other decimal(16,2); DEFINE a_grossupp decimal(16,2); DEFINE a_sbd_package_code decimal(16,2);
       
        set debug file to 'debug.txt';
    	trace on;
    
        trace 'Before for each';
        FOREACH C1 FOR
            SELECT rep_dealer, name, rep_ds_msisdn, rep_trans_date, rep_dealsh, rep_conbon, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_status, rep_addsupp, rep_other, +(NVL(rep_conbon,0) + NVL(rep_simsupp,0) + NVL(rep_hwsupp,0) + NVL(rep_redsub,0) + NVL(rep_vspsupp,0) + NVL(rep_addsupp,0) + NVL(rep_other,0)) grossupp, sbd_package_code
                INTO  a_rep_dealer, a_rep_name, a_rep_ds_msisdn, a_rep_trans_date, a_rep_dealsh, a_rep_conbon, a_rep_simsupp, a_rep_hwsupp, a_rep_redsub, a_rep_vspsupp, a_rep_status, a_rep_addsupp, a_rep_other, a_grossupp, a_sbd_package_code
                FROM temp_withcshrvoao a, OUTER(dealer_master b, OUTER slcustm c) 
                WHERE a.rep_dealer = b.dealer_id AND b.sub_acc_no = c.customer 
                ORDER BY rep_trans_date, rep_ds_msisdn
            RETURN a_rep_dealer, a_rep_name, a_rep_ds_msisdn, a_rep_trans_date, a_rep_dealsh, a_rep_conbon, a_rep_simsupp, a_rep_hwsupp, a_rep_redsub, a_rep_vspsupp, a_rep_status, a_rep_addsupp, a_rep_other, a_grossupp, a_sbd_package_code WITH resume;
        END FOREACH;
        trace 'After foreach';
    END PROCEDURE;
    Here is the contents of my trace file:

    Code:
    trace expression :Before for each
    
    "0$c1" is select rep_dealer, name, rep_ds_msisdn, rep_trans_date, rep_dealsh, re
    p_conbon, rep_simsupp, rep_hwsupp, rep_redsub, rep_vspsupp, rep_status, rep_adds
    upp, rep_other, (+ (+ (+ (+ (+ (+ (+ (nvl rep_conbon, 0), (nvl rep_simsupp, 0)),
     (nvl rep_hwsupp, 0)), (nvl rep_redsub, 0)), (nvl rep_vspsupp, 0)), (nvl rep_add
    supp, 0)), (nvl rep_other, 0))) as grossupp, sbd_package_code
      from temp_withcshrvoao as a, outer(dealer_master as b, outer(slcustm as c))
      where (and (= a.rep_dealer, b.dealer_id), (= b.sub_acc_no, c.customer))
      order by rep_trans_date, rep_ds_msisdn
    select cursor iteration.
    end cursor
    trace expression :After foreach
    
    procedure test_contract_activation_incentives_report returned no data
    race expression :Before for each
    
    start select cursor.
    procedure test_contract_activation_incentives_report returned no data
    Quote Originally Posted by begooden-it View Post
    Hi,


    to understand what happens, debug your SP.
    Code:
    set debug file to "/tmp/yourfile.txt";
    trace on;
    Your SP code.
    the debug will start where "trace on" is set. You can stop it
    with "trace off" ;
    Read the file "/tmp/yourfile.txt" to understand what happens.

    Don't forget to remove the trace when your issue is resolved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •