OraFAQ Forum: SQL & PL/SQL » QUERY (2 Merged)

Home » SQL & PL/SQL » SQL & PL/SQL » QUERY (2 Merged)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

QUERY (2 Merged) [message #563415]

Mon, 13 August 2012 10:56

vaibhav15211
Messages: 13
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.

Report message to a moderator

Re: QUERY [message #563417 is a reply to message #563415]

Mon, 13 August 2012 11:06

BlackSwan
Messages: 20099
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/

Report message to a moderator

Re: QUERY (2 Merged) [message #563421 is a reply to message #563415]

Mon, 13 August 2012 11:33

Michel Cadot
Messages: 54171
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select * from mytable where column1||column2 = value

Regards
Michel

Report message to a moderator

Re: QUERY (2 Merged) [message #563423 is a reply to message #563421]

Mon, 13 August 2012 11:40

Solomon Yakobson
Messages: 1398
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

Michel Cadot
Messages: 54171
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

Report message to a moderator

Re: QUERY (2 Merged) [message #563425 is a reply to message #563424]

Mon, 13 August 2012 12:10

Solomon Yakobson
Messages: 1398
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.

Report message to a moderator

Re: QUERY (2 Merged) [message #563465 is a reply to message #563425]

Tue, 14 August 2012 01:30

Michel Cadot
Messages: 54171
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.

Regards
Michel

Report message to a moderator

Re: QUERY (2 Merged) [message #563516 is a reply to message #563465]

Tue, 14 August 2012 10:25

vaibhav15211
Messages: 13
Registered: August 2012
Location: Hyderabad

Junior Member

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??

Attachment: table structure.jpg
(Size: 23.98KB, Downloaded 94 times)

Report message to a moderator

Re: QUERY (2 Merged) [message #563520 is a reply to message #563516]

Tue, 14 August 2012 10:57

Flyby
Messages: 120
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"...

Report message to a moderator

Re: QUERY (2 Merged) [message #563553 is a reply to message #563516]

Tue, 14 August 2012 13:41

Solomon Yakobson
Messages: 1398
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.

Report message to a moderator

Re: QUERY (2 Merged) [message #563576 is a reply to message #563553]

Wed, 15 August 2012 04:38

vaibhav15211
Messages: 13
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

Report message to a moderator

Re: QUERY (2 Merged) [message #563577 is a reply to message #563576]

Wed, 15 August 2012 05:19

Solomon Yakobson
Messages: 1398
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

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 00:52:11 CDT 2013

Total time taken to generate the page: 0.28387 seconds