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 |
Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
What are internal user account in oracle?
What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ?
cursor attributes are %isopen ,%notfound,%found,%rowcount but is any attributes there other than these? please tell me asap ..
What is transaction control statement and how many types of transaction control statement in Oracle?
What is the difference between "as" and "is" in an oracle stored procedure?
What is the dynamic sql in oracle?
What do you mean by a tablespace?
How to create a single index for multiple columns?
When a form is invoked with call_form, Does oracle forms issues a save point ?
What is bind variable in oracle 11g?
What is define in oracle?