Home » SQL & PL/SQL » SQL & PL/SQL » Finding matching values (Oracle 11.2.0.4)
| Finding matching values [message #641649] |
Wed, 19 August 2015 11:19  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
I have data like below:
SQL> WITH ih
2 AS (SELECT 'XYZ' stage, 'IJK' step FROM DUAL
3 UNION ALL
4 SELECT 'XYZ' stage, 'MNO' step FROM DUAL
5 UNION ALL
6 SELECT 'XYZ' stage, 'ABC' step FROM DUAL),
7 FH
8 AS (SELECT 'XYZ' stage, 'IJK' step, 'TEST1' hld_reason
9 FROM DUAL
10 UNION ALL
11 SELECT 'XYZ' stage, NULL step, 'TEST2' hld_reason
12 FROM DUAL)
13 SELECT ih.stage, NVL (fh.step, ih.step) step, fh.hld_reason
14 FROM IH, FH
15 WHERE ih.stage = fh.stage AND ih.step = fh.step
16 UNION ALL
17 SELECT ih.stage, NVL (fh.step, ih.step) step, fh.hld_reason
18 FROM (SELECT stage, step FROM ih
19 MINUS
20 SELECT stage, step
21 FROM fh
22 WHERE step IS NOT NULL) ih,
23 fh
24 WHERE fh.stage = ih.stage AND fh.step IS NULL;
STA STE HLD_R
--- --- -----
XYZ IJK TEST1
XYZ ABC TEST2
XYZ MNO TEST2
SQL>
Can someone please try easy way to generate same data.
Rules are:
1. Get the matching rows on the stage, steps first in both the tables.
2. if step is null in fh table, get the rows by matching stage on all unmatched (stage, step) rows from ih table.
Thanks,
Manu
|
|
|
|
| Re: Finding matching values [message #641650 is a reply to message #641649] |
Wed, 19 August 2015 11:38  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Please validate the rules:
1. An inner join between the two tables to get matching rows.
UNION ALL with
2. An anti-join to get unmatched rows based on IS NULL condition on fh table.
|
|
|
|
|
|
|
|
| Re: Finding matching values [message #641749 is a reply to message #641746] |
Fri, 21 August 2015 09:30 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Sandeep,
It's not that I haven't already tried, and post the query just to get my work done.
Below is what I tried already, but to me it doesn't seems correct.
WITH ih
AS (SELECT 'XYZ' stage, 'IJK' step FROM DUAL
UNION ALL
SELECT 'XYZ' stage, 'MNO' step FROM DUAL
UNION ALL
SELECT 'XYZ' stage, 'ABC' step FROM DUAL),
FH
AS (SELECT 'XYZ' stage, 'IJK' step, 'TEST1' hld_reason FROM DUAL
UNION ALL
SELECT 'XYZ' stage, NULL step, 'TEST2' hld_reason
FROM DUAL)
SELECT ih.stage, ih.step, fh.hld_reason
FROM IH, FH
WHERE ih.stage = fh.stage
AND CASE
WHEN fh.step IS NOT NULL
THEN
ih.step
WHEN NOT EXISTS
(SELECT 1
FROM fh fh1
WHERE ih.stage = fh1.stage AND ih.step = fh1.step)
THEN
'-999'
END = NVL (fh.step, '-999');
I need someone opinion in form of the query one will make to get the same result.
Manu
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Finding matching values [message #641756 is a reply to message #641755] |
Fri, 21 August 2015 10:01 |
 |
sandeep_orafaq
Messages: 88
Registered: September 2014
|
Member |
|
|
manubatham20 wrote on Fri, 21 August 2015 09:55With those 3 columns, I have to select many other columns from ih table, so NO to union all.
OK.
then?
With your query you are not able to select many other columns? add those columns in your query.
Editing: And we do not see those columns anywhere in your original post. May be you should first put effort to tell the problem correctly.
[Updated on: Fri, 21 August 2015 10:04] Report message to a moderator |
|
|
|
| Re: Finding matching values [message #641898 is a reply to message #641649] |
Thu, 27 August 2015 01:18 |
 |
amarjot.rocky@gmail.com
Messages: 1
Registered: August 2015
Location: India
|
Junior Member |
|
|
Hi,
May be the below query can helps u -
WITH ih
AS (SELECT 'XYZ' stage, 'IJK' step FROM DUAL
UNION ALL
SELECT 'XYZ' stage, 'MNO' step FROM DUAL
UNION ALL
SELECT 'XYZ' stage, 'ABC' step FROM DUAL),
FH
AS (SELECT 'XYZ' stage, 'IJK' step, 'TEST1' hld_reason
FROM DUAL
UNION ALL
SELECT 'XYZ' stage, NULL step, 'TEST2' hld_reason
FROM DUAL)
select stage_1,
step,
hld_reason
from (SELECT ih.stage stage_1, fh.stage,
fh.step step_1,
ih.step,
--NVL (fh.step, ih.step) step,
fh.hld_reason,
case when (ih.stage = fh.stage AND ih.step = fh.step)
or ( fh.step IS NOT NULL)
then 1
else
null
end cnd
FROM IH, FH
where
fh.stage = ih.stage
)
where
cnd = 1
I have case in the select statement and after applied the filter.
Thanks..
|
|
|
|
| Re: Finding matching values [message #641902 is a reply to message #641898] |
Thu, 27 August 2015 02:01 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Compare your post with the same just adding code tags:
WITH ih
AS (SELECT 'XYZ' stage, 'IJK' step FROM DUAL
UNION ALL
SELECT 'XYZ' stage, 'MNO' step FROM DUAL
UNION ALL
SELECT 'XYZ' stage, 'ABC' step FROM DUAL),
FH
AS (SELECT 'XYZ' stage, 'IJK' step, 'TEST1' hld_reason
FROM DUAL
UNION ALL
SELECT 'XYZ' stage, NULL step, 'TEST2' hld_reason
FROM DUAL)
select stage_1,
step,
hld_reason
from (SELECT ih.stage stage_1, fh.stage,
fh.step step_1,
ih.step,
--NVL (fh.step, ih.step) step,
fh.hld_reason,
case when (ih.stage = fh.stage AND ih.step = fh.step)
or ( fh.step IS NOT NULL)
then 1
else
null
end cnd
FROM IH, FH
where
fh.stage = ih.stage
)
where
cnd = 1
The best way to get a formatted query is to use a SQL Formatter.
[Updated on: Thu, 27 August 2015 02:03] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 09:42:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
