Avatar of williamwlk
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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Christoffer Swanström

8/22/2022 - Mon
SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Pratima

did you mean to say
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)
williamwlk

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
ASKER CERTIFIED SOLUTION
Christoffer Swanström

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
williamwlk

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)?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
williamwlk

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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
williamwlk

ASKER
Thanks Buds! It works flawlessly. I am a Linux Engineer but heavily have to use Oracle SQL. Thanks again. Have a good day.
Christoffer Swanström

You're welcome!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.