OraFAQ Forum: Reports & Discoverer » problem in srw.do

Home » Developer & Programmer » Reports & Discoverer » problem in srw.do_sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in srw.do_sql [message #88095]

Thu, 28 February 2002 00:23

Deepak Bisht
Messages: 13
Registered: October 2001

Junior Member

Dear FAQuser,
i'm using Oracle8.0.5 as backend and Dev2000 reports 3.0 as frontend.i'm facing problem while running create view sql through SRW.DO_SQL written in AfterPform funtion or After parameter form, where i want to use 'WHERE' condition in which i want to pass values from parameter form.in parameter form user will input SECTORCODE and BILL_DATE. while running reports it gives me error 'Invalid column name ' '.
sectorcode is having length 40 (char type) and billdate is having length 10(DATE type dd-mm-yyyy).
Please tell me proper wayout of this problem.

The Function i'm using is written below.

Function AfterPForm return boolean is
tempsec varchar2(40);
B_date date;
begin

tempsec := :sectorcode;
B_date := :billdate;

SRW.Do_SQL ( 'Create or replace view ADD1 as
Select consumer_no from CONSUMERMSTR WHERE substr(consumer_no,1,3) = ' || substr(tempsec,1,3) ||
' Minus Select consumer_no from billbook where bill_date = '||b_date );

return (TRUE);
end;

Report message to a moderator

Re: problem in srw.do_sql [message #88143 is a reply to message #88095]

Fri, 12 April 2002 13:16

Bob MacArt
Messages: 1
Registered: April 2002

Junior Member

I have just finished working on something similar. Try putting your Create statement into a variable first. ie. v_create varchar2(500); then code
SRW.Do_SQL(v_create); Before you run the Do_SQL use the SRW.MESSAGE(2,v_create) and you will see the values you have in the v_create variable.

One thing you have to be careful about is qhere you put your quotes. Try running the above in an anonymous block and display the v_create to make sure you have a valid statement created.

Below is my code, although not the same it works and maybe you can get something from it.

function AfterPForm return boolean is
v_sql_statement varchar2(4000);
v_from_statement varchar2(4000);
v_sel_statement varchar2(500);
v_create_table varchar2(500);
v_temp_table_name varchar2(50);
v_drop_table VARCHAR2(75);

begin
SELECT a.incur_date_begin, a.incur_date_end,
a.processed_date_begin,
a.processed_date_end,
a.rpt_note,
a.rpt_title,
a.reqt_id, b.rpt_id,
c.rpt_name, b.from_where_stmt
INTO :p_incurd_from_dt, :p_incurd_to_dt,:p_pd_from_dt, :p_pd_to_dt,
:p_rpt_note, :p_rpt_title, :p_reqt_id, :p_rpt_id, :p_rpt_name, v_from_statement
FROM user_reqt a, srs.rpt_reqt b, srs.rpt_sys c
WHERE a.reqt_id = :p_reqt_id and
b.reqt_id = :p_reqt_id and
b.rpt_id = c.rpt_id;

If :p_rpt_note is NULL Then
:p_asterisk := ' ';
Else
:p_asterisk := '*';
End If;
--get_heading_params is a procedure located in Program Units
get_heading_params(:p_reqt_id, 'GROUP', :p_groups);
get_heading_params(:p_reqt_id, 'CLASS PLAN', :p_cls_pln);

--here is where we create our temporary table
v_temp_table_name := ('SRS.EXPERIENCE_RESULTS_TABLE_'||TO_CHAR(:P_REQT_ID));
v_create_table := 'CREATE TABLE '||v_temp_table_name|| ' (MDVRX_DESCN VARCHAR2(255), PROC_YYMM NUMBER(6),
CASE_CNT NUMBER, DAY_CNT NUMBER, PAID_AMT NUMBER, INCOME_AMT NUMBER, CNTRCTS_CNT NUMBER, MBR_CNT NUMBER) PCTFREE 5
PCTUSED 75';

SRW.DO_SQL(V_CREATE_TABLE);

--Now that we have the temporary table name assign to lexical parameter :p_from <-- this goes in query as &p_from
If :p_reqt_id is null then
:p_from := 'WHERE 1 = 1';
Else
:p_from := (' EXPERIENCE_RESULTS_TABLE_'||TO_CHAR(:P_REQT_ID));
End If;

--This next set of statements parses the SQL statement that creates data for
--the EXPERIENCE_RESULTS_TABLE. The v_sel_statement is the static outer most SELECT
--that shouldn't change.
v_sel_statement := 'INSERT INTO '||v_temp_table_name||' (MDVRX_DESCN, PROC_YYMM, CASE_CNT, DAY_CNT, PAID_AMT, INCOME_AMT, CNTRCTS_CNT, MBR_CNT)
SELECT MDVRX_DECSN,PROC_YYMM,SUM(CASE_CNT),SUM(DAY_CNT),SUM(PAID_AMT),SUM(INCOME_AMT),SUM(CNTRCTS),SUM(MBR_CNT) ';
v_from_statement := rtrim(v_from_statement);
v_sql_statement := rtrim(v_sel_statement)||chr(10)||rtrim(v_from_statement);

SRW.DO_SQL (v_sql_statement);
COMMIT;

return (TRUE);
end;

Report message to a moderator

Previous Topic:	Re: How To Open .Eps file which is send as an attachment when you select mail option of the generate
Next Topic:	Ignorance is no excuse...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 16:17:08 CST 2024