Home » SQL & PL/SQL » SQL & PL/SQL » USING IN with Variable
icon14.gif  USING IN with Variable [message #272353] Thu, 04 October 2007 12:45 Go to next message
shyamjha
Messages: 11
Registered: September 2006
Location: Mumbai
Junior Member

Hi All,
Is it possiable to write a PL/SQL Query as below:

DECLARE v_Branch VARCHAR2(200);
v_test varchar(5);
BEGIN
v_Branch:='(''LA1'',''VN1'',''LE1'',''RT1'')';

SELECT DISTINCT 1 into v_test from sc_dash_raw
where FROM_BRANCH IN v_Branch;

Dbms_Output.put_line(v_test);
End;
/

I do not want to store query in a variable. When I am running the above query its giving me the error "NO DATA FOUND".

Early reponse will be highly apprecited.

Thanks in Advance.

Thanks,
Shyam

Re: USING IN with Variable [message #272356 is a reply to message #272353] Thu, 04 October 2007 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
search this forum for "dynamic SQL"
Re: USING IN with Variable [message #272358 is a reply to message #272353] Thu, 04 October 2007 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
goto asktom and search for "varying in list"

Regards
Michel
Re: USING IN with Variable [message #273051 is a reply to message #272353] Mon, 08 October 2007 23:10 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
SQL Snippets: SQL Techniques Tutorials - Varying IN Lists
Re: USING IN with Variable [message #273085 is a reply to message #273051] Tue, 09 October 2007 01:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I like the MODEL examples you give (still a bit of a mystery to me) but you missed one option mentioned here by Mr. Kyte. Perhaps worth investigating...

MHE
Re: USING IN with Variable [message #273312 is a reply to message #273085] Tue, 09 October 2007 23:16 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Thanks for the feedback Maarten. I considered that parameterized view solution when I first wrote the tutorial. Can't remember why I decided against using it though. After taking a second look I agree with you. It's worth mentioning. I'll put it on the to-do list.

--
Joe Fuda
SQL Snippets
Previous Topic: ORA-01722: invalid number
Next Topic: Multiple 'OF' clauses in a trigger?
Goto Forum:
  


Current Time: Sun Dec 04 10:26:58 CST 2016

Total time taken to generate the page: 0.18459 seconds