5 Replies Latest reply: Apr 22, 2013 10:15 AM by Solomon Yakobson

# Single select statement - the best solution

Hi,

I am trying to find the best solution for the following problem
I have a table called HIERARCHY with 3 columns.

col1 col2 col3
------------------------
1
1 2
1 2 3
4 5
6 7 8

I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.
For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
However 4,5 and 6,7,8 are not valid as higher levels are not completed.
How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions would help?!
• ###### 1. Re: Single select statement - the best solution
Hi,

Here's one way:
``````SELECT     c.*
FROM           hierarchy  c
LEFT OUTER JOIN      hierarchy  p  ON   p.col1     = c.col1
AND  (   (    p.col2     IS NULL
AND  c.col3     IS NULL
)
OR  (    p.col2     = c.col2
AND  c.col3     IS NOT NULL
)
)
AND  p.col3     IS NULL
WHERE   p.col1     IS NULL
AND     COALESCE (c.col2, c.col3)     IS NOT NULL
;``````

If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
See the forum FAQ {message:id=9360002}

Edited by: Frank Kulash on Apr 22, 2013 10:58 AM
• ###### 2. Re: Single select statement - the best solution
One way to list offending rows:
``````with t as (
select 1 col1,null col2,null col3 from dual union all
select 1,2,null from dual union all
select 1,2,3 from dual union all
select 4,5,null from dual union all
select 6,7,8 from dual
)
select  *
from  t
minus
select  *
from  t
start with col1 is not null and col2 is null and col3 is null
connect by (col1 = prior col1 and col2 is not null and level = 2)
or (col2 = prior col2 and col3 is not null and level = 3)
/

COL1       COL2       COL3
---------- ---------- ----------
4          5
6          7          8

SQL> ``````
SY.
• ###### 3. Re: Single select statement - the best solution
May not be efficient, but my solution tries to generate the pascal triangle and then tried to do a minus/not in operation.

assuming your reference triangle as :
1
12
123
1234
12345
.......
``````WITH t AS
(SELECT 1 col1, NULL col2, NULL col3 FROM DUAL
UNION ALL
SELECT 1, 2, NULL FROM DUAL
UNION ALL
SELECT 1, 2, 3 FROM DUAL
UNION ALL
SELECT 4, 5, NULL FROM DUAL
UNION ALL
SELECT 6, 7, 8 FROM DUAL)
SELECT col1,
col2,
col3,
CASE
WHEN (col1 || col2 || col3) NOT IN
(SELECT SUBSTR (REPLACE (str, ','), 1, r) str
FROM (    SELECT ROWNUM r,
(    SELECT listagg (ROWNUM, ',')
WITHIN GROUP (ORDER BY 1)
FROM DUAL
CONNECT BY ROWNUM <= 5)
str
FROM DUAL
CONNECT BY ROWNUM <= 5) t) THEN
'NO'
ELSE
'YES'
END
flg
FROM t a;``````
Output:
``````COL1     COL2     COL3     FLG
--------------------------------------------------------
1               YES
1     2          YES
1     2     3     YES
4     5          NO
6     7     8     NO``````
Cheers,
Manik.

Edited by: Simplified the solution
• ###### 4. Re: Single select statement - the best solution
I added a couple more records is this how it should respond with this type of data
``````/* Formatted on 4/22/2013 11:06:32 AM (QP5 v5.185.11230.41888) */
WITH t
AS (SELECT 1 col1, NULL col2, NULL col3 FROM DUAL
UNION ALL
SELECT 1, 2, NULL FROM DUAL
UNION ALL
SELECT 1, 2, 3 FROM DUAL
UNION ALL
SELECT 4, 5, NULL FROM DUAL
UNION ALL
SELECT 6, 7, 8 FROM DUAL
UNION ALL
SELECT 11, NULL, NULL FROM DUAL
UNION ALL
SELECT 11, 13, NULL FROM DUAL
UNION ALL
SELECT 11, 15, 16 FROM DUAL
UNION ALL
SELECT 11, 13, 14 FROM DUAL
UNION ALL
SELECT 11, 12, NULL FROM DUAL),
t2
AS (SELECT t.*,
CASE
WHEN col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL
THEN
1
WHEN     col1 IS NOT NULL
AND col2 IS NOT NULL
AND col3 IS NULL
THEN
2
WHEN     col1 IS NOT NULL
AND col2 IS NOT NULL
AND col3 IS NOT NULL
THEN
3
END
lvl
FROM t)
SELECT col1,
col2,
col3,
CASE
WHEN lvl = 1
THEN
1
WHEN     lvl = 2
AND LAG (col1)
OVER (ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST) =
col1
THEN
1
WHEN     lvl = 3
AND LAG (col1)
OVER (ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST) =
col1
AND LAG (col2)
OVER (ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST) =
col2
THEN
1
ELSE
0
END
valid_in
FROM t2
ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST

COL1     COL2     COL3     VALID_IN
1               1
1     2          1
1     2     3     1
4     5          0
6     7     8     0
11               1
11     12          1
11     13          1
11     13     14     1
11     15     16     0``````
• ###### 5. Re: Single select statement - the best solution
Solomon Yakobson wrote:
One way to list offending rows:
Will not work for cases where not null,null,not null:
``````SQL> with t as (
2             select 1 col1,null col2,null col3 from dual union all
3             select 1,2,null from dual union all
4             select 1,2,3 from dual union all
5             select 4,5,null from dual union all
6             select 6,7,8 from dual union all
7             select 6,7,99 from dual union all
8             select 6,null,55 from dual union all
9             select 6,null,null from dual union all
10             select 9,null,10 from dual union all
11             select null,11,null from dual union all
12             select null,null,12 from dual union all
13             select null,null,null from dual
14            )
15   select  *
16     from  t
17  minus
18   select  *
19     from  t
20     start with col1 is not null and col2 is null and col3 is null
21     connect by (col1 = prior col1 and col2 is not null and level = 2)
22             or (col2 = prior col2 and col3 is not null and level = 3)
23  /

COL1       COL2       COL3
---------- ---------- ----------
4          5
6                    55
9                    10
11
12

6 rows selected.

SQL> ``````
Needs one more condition:
``````with t as (
select 1 col1,null col2,null col3 from dual union all
select 1,2,null from dual union all
select 1,2,3 from dual union all
select 4,5,null from dual union all
select 6,7,8 from dual union all
select 6,7,99 from dual union all
select 6,null,55 from dual union all
select 6,null,null from dual union all
select 9,null,10 from dual union all
select null,11,null from dual union all
select null,null,12 from dual union all
select null,null,null from dual
)
select  *
from  t
minus
select  *
from  t
start with col1 is not null and col2 is null and col3 is null
connect by (col1 = prior col1 and col2 is not null and col3 is null and level = 2)
or (col2 = prior col2 and col3 is not null and level = 3)
/

COL1       COL2       COL3
---------- ---------- ----------
4          5
6          7          8
6          7         99
6                    55
9                    10
11
12

8 rows selected.

SQL> ``````
SY.