Home » SQL & PL/SQL » SQL & PL/SQL » select from sql (10.2.0.4,linux)
select from sql [message #419641] Mon, 24 August 2009 12:01 Go to next message
newsurfgal
Messages: 12
Registered: August 2009
Junior Member
Hi friends,

I need some help with this dynamic sql.. I've used case expression now. But we figured that we cannot use case for this..


CREATE OR REPLACE PROCEDURE bintap
  ( binseq       IN    catrep.catseq%TYPE
  , col_in       IN    catrep.col_in%TYPE
  , inv_no       IN    catrep.inv_no%TYPE,
  , rtn_code 	 IN    VARCHAR2
  )
 
AS
 
 cursor ...  
 
 BEGIN
 
   ... /* get values from the array */
  
     sql : = 'select' ||  arr(i).username || '  from  ' 
             || arr(i).bin_table_name || 'where rec_no = :inv_no'
             || ' AND '|| (case when rtn_code = 'SHIP' then uni_in else cust_in)|| ' = :col_in';
..

end bintap
/



In the above sql, we cannot use rtn_code = 'SHIP' to substitute the value for columnname for bin_table_name(uni_in or cust_in). We need to see if the columnname cust_in exists
in bin_table_name. If it exists then we have to use cust_in = :col_in else we have to use uni_in = :col_in.

Any suggestions or help, I'll appreciate it.

Thanks
Re: select from sql [message #419643 is a reply to message #419641] Mon, 24 August 2009 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestions or help, I'll appreciate it.

IF cust_in
THEN
WHERE_STR := 'cust_in = :col_in'
ELSE
WHERE_STR := 'uni_in = :col_in'
END IF;

Then use WHERE_STR inside "sql" string
Re: select from sql [message #419644 is a reply to message #419641] Mon, 24 August 2009 12:59 Go to previous messageGo to next message
newsurfgal
Messages: 12
Registered: August 2009
Junior Member
Blackswan,

I tried this:

CREATE OR REPLACE PROCEDURE bintap
  ( binseq       IN    catrep.catseq%TYPE
  , col_in       IN    catrep.col_in%TYPE
  , inv_no       IN    catrep.inv_no%TYPE,
  , rtn_code 	 IN    VARCHAR2
  )
 
AS
 
 cursor ...  
 
 BEGIN
  
   where_str varchar2(100);

   ... /* get values from the array */
   ...

   IF cust_in THEN 
     WHERE_STR := 'cust_in = :col_in';
   ELSE
     WHERE_STR := 'uni_in = :col_in';
   END IF;

  
     sql : = 'select' ||  arr(i).username || '  from  ' 
             || arr(i).bin_table_name || 'where rec_no = :inv_no'
             || ' AND '|| where_str;
..

end bintap
/

ERROR:
PLS-00201: identifier 'CUST_IN' must be declared



But, cust_in or uni_in are the columnnames for bin_table_name.
Will it work when I have if statement as 'if cust_in then' ?

Thank you



Re: select from sql [message #419645 is a reply to message #419641] Mon, 24 August 2009 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Will it work when I have if statement as 'if cust_in then' ?
You need to provide valid SQL syntax that meets the problem's requirements.
This is YOUR problem to solve; not mine.
I provided a framework for you to correctly implement.
Re: select from sql [message #419650 is a reply to message #419644] Mon, 24 August 2009 15:26 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
newsurfgal wrote on Mon, 24 August 2009 13:59

IF cust_in THEN



Pseudo code would be along the lines of if cust_in column exists, then use this else use the other.

if {something} then...
is valid code, as it is checking the Boolean value, but it will not work in this context (you cannot check the existence of a column in a table this way, or at least not with the code that is provided here).

Previous Topic: Automation Call procedure in SQLPlus to task it with Batch File
Next Topic: ora 4127 single subquery returns more than one row"
Goto Forum:
  


Current Time: Sun Dec 04 06:18:25 CST 2016

Total time taken to generate the page: 0.04306 seconds