Home » SQL & PL/SQL » SQL & PL/SQL » Sql query for multiple parameters (Oracle 10g)
Sql query for multiple parameters [message #388471] Tue, 24 February 2009 23:55 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I am having a form in which i have to find results with multiple Branch ID's. Right now i am using select query in which find is with Single Branch ID. I have written a stored procedure for this. But i should not use any kind of stored proc. So Please let me know the solution asap.

The original line is : AND (@BRANCH_ID IS NULL OR EDC.BRANCH_ID = @BRANCH_ID)

But i need to find with multiple Branch ID's ( both integer and also strings ).

Thank you,
Gan
Re: Sql query for multiple parameters [message #388472 is a reply to message #388471] Tue, 24 February 2009 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.
Re: Sql query for multiple parameters [message #388482 is a reply to message #388471] Wed, 25 February 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter).

Regards
Michel
Re: Sql query for multiple parameters [message #388497 is a reply to message #388471] Wed, 25 February 2009 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is this SQL firing against an Oracle database?
Re: Sql query for multiple parameters [message #388503 is a reply to message #388497] Wed, 25 February 2009 00:58 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member

Ah.. Frank good point. I didn't noticed when i downloaded the text file.

Is this SQL firing against an Oracle database? 




Regards,
Hammer
Re: Sql query for multiple parameters [message #388505 is a reply to message #388471] Wed, 25 February 2009 00:59 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Why post such a complex query when your problem isn't that complex?

Basically your problem, as I understand it, is as follows:

SELECT EDC.*
FROM  EDCLASSES EDC
WHERE EDC.BRANCH_ID = @BRANCH_ID OR @BRANCH_ID IS NULL


This example restricts your input to either one value or just NULL.

For multiple brach_ids up to 3 possible values, you can use this:

SELECT EDC.*
FROM  EDCLASSES EDC
WHERE EDC.BRANCH_ID IN ( @BRANCH_ID_1 , @BRANCH_ID_2 , @BRANCH_ID_3 )


Depending on your real requirements a number of other valuable solutions are available.
Re: Sql query for multiple parameters [message #388510 is a reply to message #388505] Wed, 25 February 2009 01:16 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

Yes exactly my requirement is the same but i am not sure of how many branch values i will get from frond end. I have those values in an array list and i am sending that as a parameter to back end. So in my case i need a dynamic query which can search with "N" multiple values to a single parameter. And also in what way should i pass those parameters from front end ( can i pass them in an array list or shall i have to pass them in a string ?? )

Please let me know.

Regards
Re: Sql query for multiple parameters [message #388513 is a reply to message #388510] Wed, 25 February 2009 01:24 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
There is not enough information about your requirements/environment for me to give a solution that could fit your needs.

So many solutions are possible and all could be equally valuable.

For the "N" multiple values "problem" I've worked out following solutions:

- Create a dynamic query, using an IN-list
- Stored the parameters in a (temporary) table, and joined with this table
- PL/SQL : filled a collection and joined with this collection (cast to table)

Try one, see if it works for you.

But don't expect from us to deliver the query, I'm pretty sure you can write that one yourself Wink
Previous Topic: Dynamic Using Clause (merged 3)
Next Topic: insert data > 4000 bytes into clob datatype columns
Goto Forum:
  


Current Time: Sat Dec 03 20:36:06 CST 2016

Total time taken to generate the page: 0.10851 seconds