williamwlk
asked on
How to take out inconsistant values in SQL
[STAB]
SNAME STYPE SKEY SVAL
service-001 web KEY1 1000
service-001 web KEY2 2000
service-001 web KEY3 3000
service-001 web KEY4 4000
service-002 web KEY1 2000
service-002 web KEY2 1000
service-002 web KEY3 3000
service-002 web KEY4 4000
service-003 web KEY1 1000
service-003 web KEY2 2000
service-003 web KEY3 1000
service-003 web KEY4 2000
service-004 web KEY1 4000
service-004 web KEY2 2000
service-004 web KEY3 3000
service-004 web KEY4 5000
service-005 web KEY1 1000
service-005 web KEY2 2000
service-005 web KEY3 3000
service-005 web KEY4 4000
i'd like to know how I can get the inconsitant SVAL for the SKEY using SQL.
Here is the expected result:
service-002 web KEY1 2000
service-002 web KEY2 1000
service-003 web KEY3 1000
service-003 web KEY4 2000
service-004 web KEY1 4000
service-004 web KEY4 5000
In Excel, I created a new column concatenating SKEY+SVAL and I remove duplicates on that column. And then, I leave out the first consistent set [of service-001]. And I got the above expected result.
Thanks and regards,
W
SNAME STYPE SKEY SVAL
service-001 web KEY1 1000
service-001 web KEY2 2000
service-001 web KEY3 3000
service-001 web KEY4 4000
service-002 web KEY1 2000
service-002 web KEY2 1000
service-002 web KEY3 3000
service-002 web KEY4 4000
service-003 web KEY1 1000
service-003 web KEY2 2000
service-003 web KEY3 1000
service-003 web KEY4 2000
service-004 web KEY1 4000
service-004 web KEY2 2000
service-004 web KEY3 3000
service-004 web KEY4 5000
service-005 web KEY1 1000
service-005 web KEY2 2000
service-005 web KEY3 3000
service-005 web KEY4 4000
i'd like to know how I can get the inconsitant SVAL for the SKEY using SQL.
Here is the expected result:
service-002 web KEY1 2000
service-002 web KEY2 1000
service-003 web KEY3 1000
service-003 web KEY4 2000
service-004 web KEY1 4000
service-004 web KEY4 5000
In Excel, I created a new column concatenating SKEY+SVAL and I remove duplicates on that column. And then, I leave out the first consistent set [of service-001]. And I got the above expected result.
Thanks and regards,
W
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the ideas. I would like to explain a bit.
Hello sdstuber,
KEY[1-4] are just exemplary. In my case, SKEY can be IP, PORT, LOGPATH, LOGLEVEL, etc.
SVAL can be anything as well not just 1000,2000,3000,4000.
Hello pratima_mcs,
I did not mean to say the following is correct:
KEY1 1000
KEY2 2000
KEY3 3000
Key4 4000
As long as one KEY has the same value, I'd consider consistent. If KEY1 has different value across the table, then, it is considered inconsistent.
Anyway, can I do it without creating a new keyval table?
W
Hello sdstuber,
KEY[1-4] are just exemplary. In my case, SKEY can be IP, PORT, LOGPATH, LOGLEVEL, etc.
SVAL can be anything as well not just 1000,2000,3000,4000.
Hello pratima_mcs,
I did not mean to say the following is correct:
KEY1 1000
KEY2 2000
KEY3 3000
Key4 4000
As long as one KEY has the same value, I'd consider consistent. If KEY1 has different value across the table, then, it is considered inconsistent.
Anyway, can I do it without creating a new keyval table?
W
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It looks OK to me, tosse!
However, I'd like to catch up some keys that are not present it 'service-001'.
Eg.
service-001 web KEY1 1000
service-001 web KEY2 2000
service-001 web KEY3 3000
service-001 web KEY4 4000
service-002 web KEY1 2000
service-002 web KEY2 1000
service-002 web KEY3 3000
service-002 web KEY4 4000
service-002 web KEY5 4000
service-003 web KEY1 1000
service-003 web KEY2 2000
service-003 web KEY3 1000
service-003 web KEY4 2000
service-003 web KEY8 2000
NOT ONLY VALUE DIFF BUT ALSO MISSING KEY(S)?
However, I'd like to catch up some keys that are not present it 'service-001'.
Eg.
service-001 web KEY1 1000
service-001 web KEY2 2000
service-001 web KEY3 3000
service-001 web KEY4 4000
service-002 web KEY1 2000
service-002 web KEY2 1000
service-002 web KEY3 3000
service-002 web KEY4 4000
service-002 web KEY5 4000
service-003 web KEY1 1000
service-003 web KEY2 2000
service-003 web KEY3 1000
service-003 web KEY4 2000
service-003 web KEY8 2000
NOT ONLY VALUE DIFF BUT ALSO MISSING KEY(S)?
ASKER
service-002 web KEY5 4000
service-003 web KEY8 2000
I'd like to identify the above as they don't appear in service-001.
Thanks and regards,
W
service-003 web KEY8 2000
I'd like to identify the above as they don't appear in service-001.
Thanks and regards,
W
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Buds! It works flawlessly. I am a Linux Engineer but heavily have to use Oracle SQL. Thanks again. Have a good day.
You're welcome!
KEY1 1000
KEY2 2000
KEY3 3000
Key4 4000
This are the correct values, you want teh result where this is not matching?
If yes
then try one this
create on new table having two fileds Key and Val , save tablename KeyVal
lokks like ths i
Key Val
KEY1 1000
KEY2 2000
KEY3 3000
Key4 4000
Join this table with orignal table like below ,you will get incosistent resultset
Select * from STAB
where Not Exists in
(
Select S.* from STAB S
Inner join KeyVal K on S.SKEY = K.Key and S.SVAl=L.Val)