How to take out inconsistant values in SQL

williamwlk
williamwlk used Ask the Experts™
on
[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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
I assume by inconsistent you mean

where you have  KEYx  and y000   x != y

is that correct?

 if so, try this...

select * from yourtable where sval not like regexp_substr(skey,'[0-9]+$') || '%'
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)

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

How about this:

SELECT
  t.*
FROM
  (SELECT
    skey
    ,sval
  FROM
    stab
  WHERE
    sname = 'service-001'
  ) first_val
INNER JOIN
  stab t
ON
  t.skey = first_val.skey
AND
  t.sval <> first_val.sval
;

Open in new window


This will give you all records from the table where the SVAL is not the same as the SVALof the first record of the given SKEY (first record being defined as SNAME = 'service-001').

Author

Commented:
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)?

Author

Commented:
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
Try this:

SELECT
	sname
	,stype
	,skey
	,sval
FROM
  (SELECT
    t.*
  	,MAX(CASE WHEN sname = 'service-001' THEN 1 ELSE 0 END) OVER(PARTITION BY skey) AS svc1
  FROM
    stab t
  ) asd
WHERE
	svc1 = 0

Open in new window

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial