Complex situation in a stored procedure ...

[request]

I have a stored procedure where i have the following query:

SELECT COUNT(*)
into cust_count
FROM cust_acct_item
WHERE (sobp_me = substr(sobp_in,1,4) or sobp_me like ltrim(rtrim(substr(sobp_in,1,4))) or sobp_me is null)
AND (sobp_le = substr(sobp_in,5,3) or sobp_le like ltrim(rtrim(substr(sobp_in,5,3))) or sobp_le is null)
AND (sobp_ae = substr(sobp_in,8,3) or sobp_ae like ltrim(rtrim(substr(sobp_in,8,3))) or sobp_ae is null)
AND (cr_facct= facct_in or cr_facct like ltrim(rtrim(facct_in)) or cr_facct is null)
AND (cr_orgc = orgc_in or cr_orgc like ltrim(rtrim(orgc_in)) or cr_orgc is null);

Where sobp_in,facct_in,orgc_in are the input parameters to the stored procedure.

Now I have to modify this query in such a way that
IF any of these input parameters have the value, then I have to use the value . e.g
if orgc_in has the value, then use the condition cr_orgc = orgc_in .
ELSE use null or any values for comparison.
e.g. cr_orgc like orgc_in or cr_orgc is null.

How can I modify the query above to do this.

Please help .Thanks a ton.

 

[request]

Can anyone please help me with the following:

I have a select statement where the WHERE clause changes depending on what the input parameter is.

Now, I will check the value of input parameter in the beginning and create the WHERE clause.

If input_parm is null then
test_str = 'WHERE table_name.field_name IS NULL OR table_name.field_name like '%';
else
test_str = 'WHERE table_name.field_name = input_parm'
end if;

Then I want to use the string created above in the SELECT clause as follows:

SELECT count(*)
FROM cust_acct_item
test_str

Please tell me how exactly can this be implemented.

Please help.

Thanks/

 

[vijaychin]

Put the entire select in a variable and execute it using 'execute immediate'. refer to the documentation for the usage. its quite simple.

 

[request]

I cannot use EXECUTE IMMEDIATE since it is a syntax for Oracle 8i and I have ORacle 7.

I have my query as follows:

SELECT ltrim(rtrim(substr(sobp_in,1,4)))
INTO sobp_me_in
FROM DUAL;

IF sobp_me_in = '%' then
sobp_me_str := ' WHERE sobp_me is null OR sobp_me like '||chr(39)||'%'||chr(39);
ELSE
sobp_me_str := ' WHERE sobp_me = '||chr(39)||sobp_me_in||chr(39);
END IF;

SELECT COUNT(*)
INTO cust_count
FROM cust_acct_item sobp_me_str;

However, in SELECT it is not taking sobp_me_str.

Please help. Thanks.

 

[Bimalaggarwal]

Hi, you can form your dynamic select clause into the variable l_stmt,
and execute the code as follows :
-----------------------------------

declare
curs integer;
l pls_integer;
l_stmt varchar2(2000) ;
begin

if <param 1 > is not null then
l_stmt := ....
else...
...
end if;

curs := dbms_sql.open_cursor;
dbms_sql.parse(curs, l_stmt, dbms_sql.v7);
l := dbms_sql.execute(curs);
dbms_sql.close_cursor(curs);


exception
when others then
if dbms_sql.is_open(curs) then dbms_sql.close_cursor(curs); end if;
end ;

 

[jee]

Do you realize you submitted your question to the Oracle 8/8i forum? There is another forum for Oracle 7.