Home » SQL & PL/SQL » SQL & PL/SQL » QUERY (2 Merged)
QUERY (2 Merged) [message #563415] Mon, 13 August 2012 10:56 Go to next message
vaibhav15211
Messages: 20
Registered: August 2012
Location: Hyderabad
Junior Member
I have a table with multiple columns.
For the first two columns: column1 and column2, If column1 has a value then column2 is NULL and if column2 has a value then column1 is NULL.
I am receiving one value from a JAVA interface which has both column1 and column2 as option,user selects one.
Now if the option selected is of type column1,I have to select corresponding row and vice versa. Please suggest a solution.
Re: QUERY [message #563417 is a reply to message #563415] Mon, 13 August 2012 11:06 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: QUERY (2 Merged) [message #563421 is a reply to message #563415] Mon, 13 August 2012 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from mytable where column1||column2 = value

Regards
Michel
Re: QUERY (2 Merged) [message #563423 is a reply to message #563421] Mon, 13 August 2012 11:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Michel Cadot wrote on Mon, 13 August 2012 12:33
select * from mytable where column1||column2 = value


That would return rows where column1 = value and rows where column2 = value. I think it should be something like

select  *
  from  mytable
  where    (
                :column_type = 1
            and
                column1 = :value
           )
        or column2 = :value
/


SY.

[Updated on: Mon, 13 August 2012 11:41]

Report message to a moderator

Re: QUERY (2 Merged) [message #563424 is a reply to message #563423] Mon, 13 August 2012 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That would return rows where column1 = value and rows where column2 = value


This is what I understood from the question.

Regards
Michel
Re: QUERY (2 Merged) [message #563425 is a reply to message #563424] Mon, 13 August 2012 12:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
OP wrote "Now if the option selected is of type column1,I have to select corresponding row and vice versa."

SY.
Re: QUERY (2 Merged) [message #563465 is a reply to message #563425] Tue, 14 August 2012 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So, the conclusion for OP is:
Quote:
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Smile

Regards
Michel
Re: QUERY (2 Merged) [message #563516 is a reply to message #563465] Tue, 14 August 2012 10:25 Go to previous messageGo to next message
vaibhav15211
Messages: 20
Registered: August 2012
Location: Hyderabad
Junior Member
./fa/10359/0/let us consider the attached image as table structure.
Now if I receive 'A' as value from JAVA interface my query should fetch me US and English US, while if I receive 'C' query should return me CHINA and Simplified Chinese.
All this in one query.
Please suggest how??
Re: QUERY (2 Merged) [message #563520 is a reply to message #563516] Tue, 14 August 2012 10:57 Go to previous messageGo to next message
Flyby
Messages: 146
Registered: March 2011
Location: Belgium
Senior Member
select column3,column4
from tableX
where (column1=:p1 and column2 is null) or (column2=:p1 and column1 is null);

select column3,column4
from tableX
where coalesce(column1,column2)=:p1;

select column3,column4,'c1' selectcol
from tableX
where column1=:p1
and :p2='c1'
union all
select column3,column4,'c2' selectcol
from tableX
where column2=:p1
and :p2='c2'

Is it possible to have the same value in Column1 and Column2 but for different records? Like record 1 column1 "A", record 2 column2 "A"...
Re: QUERY (2 Merged) [message #563553 is a reply to message #563516] Tue, 14 August 2012 13:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
And what if you have:

COLUMN1 COLUMN2 COLUMN3 COLUMN4
------- ------- ------- -------
A               US      English US
        A       AU      German AU


and you receive 'A' as value from JAVA interface?

SY.
Re: QUERY (2 Merged) [message #563576 is a reply to message #563553] Wed, 15 August 2012 04:38 Go to previous messageGo to next message
vaibhav15211
Messages: 20
Registered: August 2012
Location: Hyderabad
Junior Member
Thanks Flyby for the solutions.
@Flyby and Solomon:both column1 and column2 will have different and unique values so that is not a problem Smile
Re: QUERY (2 Merged) [message #563577 is a reply to message #563576] Wed, 15 August 2012 05:19 Go to previous message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
vaibhav15211 wrote on Wed, 15 August 2012 05:38
Thanks Flyby for the solutions.
Solomon:both column1 and column2 will have different and unique values so that is not a problem Smile


This doesn't make much sense. You said: if column1 is not null then column2 is null and vice versa. No you added if some row has column1 = 'A' there can't be a row with column2 = 'A'. Based on that there is absolutely no need for two columns. All you need is one column with value of nvl(column1,column2).

SY.
Previous Topic: data types to store large integer values
Next Topic: sum of a column got from joining multiple tables
Goto Forum:
  


Current Time: Sat Oct 25 03:13:12 CDT 2014

Total time taken to generate the page: 0.08667 seconds