Hi all,
Can any one give answer for this question.
Suppose im having employee table with fields, eno, ename,
dept, address1, address2, address3.
In address field employee can fill only address1 or address2
or address3... at a time he can fill three address fields.
now i want all employee names who filled only one address
field.. Plz its urjent can any one give querry.. Thanks in
advance.
Answers were Sorted based on User's Feedback
Answer / jigar
Select ename from employee
where
to_char(nvl2(address1,'1','0')||nvl2(address2,'1','0')||nvl2(address3,'1','0'))
in ('100','010','001')
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / nathan
WITH temp AS
(SELECT 1 ID, 'delhi' add1, 'mumbai' add2, 'guj' add3
FROM DUAL
UNION ALL
SELECT 2 ID, NULL add1, 'mumbai' add2, NULL add3
FROM DUAL
UNION ALL
SELECT 3 ID, 'delhi' add1, NULL add2, 'guj' add3
FROM DUAL)
SELECT *
FROM (SELECT ID, NVL (add1, 0) + NVL (add2, 0) + NVL
(add3, 0) address
FROM (SELECT ID, DECODE (add1, NULL, NULL, 1) add1,
DECODE (add2, NULL, NULL, 1) add2,
DECODE (add3, NULL, NULL, 1) add3
FROM temp))
WHERE address = 1;
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / babuli
select ename from emp where (address1 is not null or address2
is not null or address3 is not null) and
((address1 is null and address2 is null ) or (address1 is
null and address3 is null ) or (address2 is null and address3
is null ))
Is This Answer Correct ? | 1 Yes | 3 No |
Answer / ajit
select *from addr1 where add1 is not null and add2 is null
and add3 is null or add1 is null and add2 is not null and
add3 is null or add1 is null and add2 is null
and add3 is not null
Is This Answer Correct ? | 0 Yes | 3 No |
How do I connect to oracle database?
ABOUT IDENTITY?
t1 col1 col2 nishi 5000 lucky 6700 akash 7000 i want that a query that when i insert 7000 it will show me data already present and data will not insert. if data is not present it will insert.
Can objects of the same Schema reside in different tablespaces.?
What is an oracle wallet?
What is oracle analytical function?
Describe varray?
What is catalog in Oracle?
How can I get column names from a table in oracle?
Difference between hot backup vs. Cold backup?
What is the difference between pre-select and pre-query?
How do we know whether an index is created on a table ???