Re: Select some record

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 30 Dec 2007 19:31:17 -0800 (PST)
Message-ID: <f5ee774a-b905-40a7-85d8-ad72cffd73a1@i29g2000prf.googlegroups.com>

On Dec 29, 4:24 am, nova1..._at_gmail.com wrote:
> Dear All,
>
> I have problem, Please help.
>
> There is table called t:
>
> with t as (
>         select 1 Col1, 1 Col2, 1 Col3 from dual union all
>         select 1, 1, 2 from dual union all
>         select 1, 2, 1 from dual union all
>         select 1, 2, 2 from dual union all
>         select 2, 1, 1 from dual union all
>         select 2, 1, 2 from dual union all
>         select 2, 2, 1 from dual union all
>         select 2, 2, 2 from dual union all
>         select 2, 1, 1 from dual union all
>         select 2, 1, 2 from dual union all
>         select 2, 2, 1 from dual union all
>         select 2, 2, 2 from dual union all
>         select NULL, NULL, NULL from dual union all
>         select 3, 3, NULL from dual union all
>         select 4, NULL, NULL from dual union all
>         select 5, 5, NULL from dual union all
>         select 5, 6, NULL from dual union all
>         select 6, 5, NULL from dual union all
>         select 6, 6, NULL from dual
> )
>
> Table (  t  )
> Col1    Col2    Col3
> 1       1       1
> 1       1       2
> 1       2       1
> 1       2       2
> 2       1       1
> 2       1       2
> 2       2       1
> 2       2       2
> 2       1       1
> 2       1       2
> 2       2       1
> 2       2       2
>
> 3       3
> 4
> 5       5
> 5       6
> 6       5
> 6       6
>
> Question: how can do like this result?
>
> The result should be like this:
>
> Table (  result  )
> Col1    Col2    Col3
> 1       1       1
> 2       2       2
>
> 3       3
> 4
> 5       5
> 6       6
>
> Note: Make sure the empty cells are included.

Since there is no response to Mark's request for clarification, it appears to me that the OP would like to include those rows where the value in COL1 is the same as the values in COL2 and COL3, and allowing nulls in the last column, the last two columns, or all three columns. If we translate NULL values to an unlikely value, such as -999999, using NVL, a simple WHERE clause should be sufficient. For example: with t as (

        select 1 Col1, 1 Col2, 1 Col3 from dual union all
        select 1, 1, 2 from dual union all
        select 1, 2, 1 from dual union all
        select 1, 2, 2 from dual union all
        select 2, 1, 1 from dual union all
        select 2, 1, 2 from dual union all
        select 2, 2, 1 from dual union all
        select 2, 2, 2 from dual union all
        select NULL, NULL, NULL from dual union all
        select 3, 3, NULL from dual union all
        select 4, NULL, NULL from dual union all
        select 5, 5, NULL from dual union all
        select 5, 6, NULL from dual union all
        select 6, 5, NULL from dual union all
        select 6, 6, NULL from dual

)
SELECT
  COL1,
  COL2,
  COL3
FROM
  T
WHERE
  NVL(COL1,-999999)=NVL(COL2,NVL(COL1,-999999))   AND
NVL(COL2,NVL(COL1,-999999))=NVL(COL3,NVL(COL2,NVL(COL1,-999999)));       COL1 COL2 COL3
---------- ---------- ----------

         1          1          1
         2          2          2

         3          3
         4
         5          5
         6          6

7 rows selected.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Dec 30 2007 - 21:31:17 CST