| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-21-11, 02:29
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 4
|
|
|
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?
|
|
11-23-11, 03:26
|
|
Registered User
|
|
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 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!
|
|
11-23-11, 04:33
|
|
Registered User
|
|
Join Date: Sep 2011
Location: Pont l'Abbé, Brittany, France
Posts: 183
|
|
|
|
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.
|
|
11-23-11, 05:15
|
|
Registered User
|
|
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
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. |
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Stored Procedure Problem
