how to use USING clause for dynamically appended where clauses [message #442455] |
Mon, 08 February 2010 04:12 |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
we are writing the procedure in the manned described above.but i am confused with the single and double quotes.when to put single and when to use double quotes while appending dynamically adding where conditions.
i think instead of this we can write USING clause.how to use USING clause for the above procedure?
procedure testprod(p_fdate in Varchar2,
p_tdate in varchar2,
p_type in varchar2) IS
where_clause VARCHAR2(32567) := '';
BEGIN
where_clause := where_clause || ' AND (trunc(t.invoice_date) BETWEEN ''' ||
p_fdate || ''' AND ''' || p_tdate || ''')';
where_clause := where_clause || ' AND T.STATUS IN (''' ||
upper(p_type) || ''')';
execute immediate 'INSERT INTO GT_TEMP
(PARTY,
REFERENCE
)
(SELECT
DECODE(T.STATUS,
''SALES'',
T.RECEIVER,
T.SENDER) PARTYID,
T.REFERENCE
FROM DOCUMENT T
WHERE T.CHARGE_STATUS IN (''INVOICED'', ''PARTLY_INVOICED'')
' || where_clause || ')';
END;
|
|
|
|
|
|
|
|
Re: how to use USING clause for dynamically appended where clauses [message #442534 is a reply to message #442455] |
Mon, 08 February 2010 22:48 |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
Michel,
can i write like this.
procedure testprod(p_fdate in Varchar2,
p_tdate in varchar2,
p_type in varchar2) IS
where_clause VARCHAR2(32567) := '';
SQL1 VARCHAR2(32567);
BEGIN
SQL1 := 'INSERT INTO GT_TEMP (PARTY, REFERENCE)';
SQL1 := SQL1||'(SELECT DECODE(T.STATUS,''SALES'',T.RECEIVER,T.SENDER) PARTYID,T.REFERENCE PARTYID,T.REFERENCE FROM DOCUMENT T';
SQL1 := SQL1||'WHERE T.CHARGE_STATUS IN (''INVOICED'', ''PARTLY_INVOICED'')';
SQL1 := SQL1||' AND (trunc(t.invoice_date) BETWEEN :P_DATE1 AND :P_DATE2';
SQL1 := SQL1||' AND T.STATUS IN ('':P_TYPE'')';
EXECUTE IMMEDATE SQL1 USING P_FDATE,P_TDATE,P_TYPE;
END;
|
|
|
|
|
|
|