I have a table -
CREATE TABLE SAMPLE_TAB
(
ICODE VARCHAR2(4 BYTE),
ILV NUMBER,
LCODE VARCHAR2(4 BYTE),
LLV NUMBER
)
data in the table is as follows -
insert into sample_tab values('1234', 1, '231', 1);
insert into sample_tab values('123', 1, '123', 1);
insert into sample_tab values('123', 1, '123', 1);
insert into sample_tab values('234', 1, '2345', 1);
I would like to fetch rows those have different values for ICODE, ILV and LCODE, LLV. Meaning, the combination of the ICODE and ILV should not be equal to the combination of LCODE and LLV.
I am using below query -
SELECT * FROM sample_tab
WHERE icode||ilv <> lcode||llv;
It is retrieving correct result but is the approach correct and is there a better way?
Thanks!
well, that isn't achieving the correct result! what if you have:
insert into sample_tab values ( '1', '23', '12', '3' );
????
you just want to simply:
select *
from sample_tab
where icode <> lcode
or ilv <> llv
or (icode is null and lcode is not null)
or (icode is not null and lcode is null)
or (ilv is null and llv is not null)
or (ilv is not null and llv is null)
you could use decode to simplify:
select *
from sample_tab
where decode( icode, lcode, 1, 0 ) = 0
or decode( ilv, llv, 1, 0 ) = 0
as decode treats nulls like a value. The decode(icode,lcode,1,0) is like a function that says:
...
if (icode = lcode /* even if both are null */
then
return 1;
else
return 0;
end if;
...
here is a full example:
ops$tkyte%ORA11GR2> select * from sample_tab;
ICOD ILV LCOD LLV
---- ---------- ---- ----------
1 23 12 3
1234 1 231 1
123 1 123 1
123 1 123 1
234 1 2345 1
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT * FROM sample_tab
2 WHERE icode||ilv <> lcode||llv;
ICOD ILV LCOD LLV
---- ---------- ---- ----------
1234 1 231 1
234 1 2345 1
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select *
2 from sample_tab
3 where icode <> lcode
4 or ilv <> llv
5 or (icode is null and lcode is not null)
6 or (icode is not null and lcode is null)
7 or (ilv is null and llv is not null)
8 or (ilv is not null and llv is null)
9 /
ICOD ILV LCOD LLV
---- ---------- ---- ----------
1 23 12 3
1234 1 231 1
234 1 2345 1
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select *
2 from sample_tab
3 where decode( icode, lcode, 1, 0 ) = 0
4 or decode( ilv, llv, 1, 0 ) = 0
5 /
ICOD ILV LCOD LLV
---- ---------- ---- ----------
1 23 12 3
1234 1 231 1
234 1 2345 1