Forum Stats

  • 3,876,212 Users
  • 2,267,082 Discussions
  • 7,912,473 Comments

Discussions

CTE Issue

3234063
3234063 Member Posts: 12
edited May 22, 2016 10:28AM in SQL & PL/SQL

I have a table with 65 fields. I need a select statement that will return data from a single record that contains three columns and 21 rows (the two missing fields are keys). I must be able to control the order of the rows.

output needs to look like this

field_x_1     field_x_2     field_x_3

field_A_1     field_A_2     field_A_3

field_Y_1     field_Y_2     field_Y_3

I have tried the following but it says there is an issue with the WITH.

WITH X AS (

WITH T AS (SELECT * FROM Table_Name WHERE FieldZ = '1' AND FieldY = '2')

SELECT Field_x_1 AS A, Field_x_2 AS B, Field_x_3 AS C, 1 AS R

FROM T

UNION ALL

SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R

FROM T

-- I would add 19 more select statements here

)

SELECT A, B, C

FROM X

ORDER BY R

I realize I could rewrite it to have my WHERE clause in it 21 times but that seems inefficient. If I only use one CTE I don't know how to guarantee the order without also outputting R.

Thanks,

Scott

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,956 Red Diamond
    edited May 18, 2016 7:40AM Answer ✓

    WITH T AS (SELECT  LEVEL L FROM DUAL CONNECT BY LEVEL <= 21)

    SELECT  CASE L

              WHEN 1 THEN field_x_1

              WHEN 2 THEN field_A_1

              WHEN 3 THEN field_Y_1

    ...

              WHEN 20 THEN ...

              ELSE ...

            END COL1,

            CASE L

              WHEN 1 THEN field_x_2

              WHEN 2 THEN field_A_2

              WHEN 3 THEN field_Y_2

    ...

              WHEN 20 THEN ...

              ELSE ...

            END COL2,

            CASE L

              WHEN 1 THEN field_x_3

              WHEN 2 THEN field_A_3

              WHEN 3 THEN field_Y_3

    ...

              WHEN 20 THEN ...

              ELSE ...

            END COL3

      FROM  Table_Name,

            T

      WHERE FieldZ = '1' AND FieldY = '2'

    /

    You will need explicit CAST if not all THEN expressions withing each CASE have same/compalible data type.

    SY.

    3234063
«1

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,956 Red Diamond
    edited May 18, 2016 7:40AM Answer ✓

    WITH T AS (SELECT  LEVEL L FROM DUAL CONNECT BY LEVEL <= 21)

    SELECT  CASE L

              WHEN 1 THEN field_x_1

              WHEN 2 THEN field_A_1

              WHEN 3 THEN field_Y_1

    ...

              WHEN 20 THEN ...

              ELSE ...

            END COL1,

            CASE L

              WHEN 1 THEN field_x_2

              WHEN 2 THEN field_A_2

              WHEN 3 THEN field_Y_2

    ...

              WHEN 20 THEN ...

              ELSE ...

            END COL2,

            CASE L

              WHEN 1 THEN field_x_3

              WHEN 2 THEN field_A_3

              WHEN 3 THEN field_Y_3

    ...

              WHEN 20 THEN ...

              ELSE ...

            END COL3

      FROM  Table_Name,

            T

      WHERE FieldZ = '1' AND FieldY = '2'

    /

    You will need explicit CAST if not all THEN expressions withing each CASE have same/compalible data type.

    SY.

    3234063
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,794 Red Diamond
    edited May 17, 2016 10:40PM

    Hi,

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

    Explain, using specific examples, how you get those results from that data.

    Simplify the problem as much as possible.  for example, instead of a problem involving 21 rows, post a problem that involves maybe 3 rows.  (Just explain that you really need 21, so people will give answers that can be tested with 3 rows, and then easily adapted to 21.)

    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ: 

  • 3234063
    3234063 Member Posts: 12
    edited May 21, 2016 9:59AM

    Solomon,

    I have tried your solution and it seems to work! Are the order of rows guaranteed to return in the right order? I don't see an "order by" clause that would guarantee that. I added "ORDER BY L" at the end and that seems to work.

    Thanks,

    Scott

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,956 Red Diamond
    edited May 21, 2016 11:13AM

    If you want rows ordered, then use ORDER BY L, assuming WHERE FieldZ = '1' AND FieldY = '2' returns only one row from your table. Otherwise, use ORDER BY TABLE_NAME.ROWID,L


    SY.

  • Unknown
    edited May 21, 2016 11:08AM

    No ordering is guaranteed unless you use ORDER BY.

  • Kalpataru
    Kalpataru Member Posts: 4,619 Bronze Crown
    edited May 21, 2016 11:37AM

    Check this thread may help you.

    Using CTE in Oracle

  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited May 22, 2016 5:20AM

    Please put the T CTE outside X and try.. like..

    WITH T AS (SELECT * FROM Table_Name WHERE FieldZ = '1' AND FieldY = '2'),

    X AS (

    SELECT Field_x_1 AS A, Field_x_2 AS B, Field_x_3 AS C, 1 AS R

    FROM T

    UNION ALL

    SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R

    FROM T

    -- I would add 19 more select statements here

    )

    SELECT A, B, C

    FROM X

    ORDER BY R

  • William Robertson
    William Robertson Member Posts: 9,570 Bronze Crown
    edited May 22, 2016 6:18AM

    fyi, records have fields, tables have columns.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,956 Red Diamond
    edited May 22, 2016 6:46AM

    Very ineffective. It will scan table 21 times.

    SY.

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 22, 2016 7:24AM
    Solomon Yakobson wrote:
    
    Very ineffective. It will scan table 21 times.
    
    SY.
    
    

    Oracle will materialize a CTE if it is referred to more than once:

    create table many_fields
      (fieldz varchar2(30), fieldy varchar2(30)
      ,field_x_1 number, field_x_2 number, field_x_3 number
      ,field_a_1 number, field_a_2 number, field_a_3 number
      ,field_y_1 number, field_y_2 number, field_y_3 number
      );
    
    explain plan for
    WITH T AS (SELECT * FROM many_fields WHERE FieldZ = '1' AND FieldY = '2'),
    X AS (
    SELECT Field_x_1 AS A, Field_x_2 AS B, Field_x_3 AS C, 1 AS R
    FROM T
    UNION ALL
    SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R
    FROM T
    UNION ALL
    SELECT Field_y_1, Field_y_2, Field_y_3, 3 AS R
    FROM T
    )
    SELECT A, B, C
    FROM X
    ORDER BY R
    /
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------
    Plan hash value: 244091491 --------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
    --------------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT          |                          |    3 |  126 |    9  (12)| 00:00:01 |
    |  1 |  TEMP TABLE TRANSFORMATION |                          |      |      |            |          |
    |  2 |  LOAD AS SELECT          | SYS_TEMP_0FD9D6679_62EC16 |      |      |            |          |
    |*  3 |    TABLE ACCESS FULL      | MANY_FIELDS              |    1 |  151 |    2  (0)| 00:00:01 |
    |  4 |  SORT ORDER BY            |                          |    3 |  126 |    7  (15)| 00:00:01 |
    |  5 |    VIEW                    |                          |    3 |  126 |    6  (0)| 00:00:01 |
    |  6 |    UNION-ALL              |                          |      |      |            |          |
    |  7 |      VIEW                  |                          |    1 |    39 |    2  (0)| 00:00:01 |
    |  8 |      TABLE ACCESS FULL    | SYS_TEMP_0FD9D6679_62EC16 |    1 |  151 |    2  (0)| 00:00:01 |
    |  9 |      VIEW                  |                          |    1 |    39 |    2  (0)| 00:00:01 |
    |  10 |      TABLE ACCESS FULL    | SYS_TEMP_0FD9D6679_62EC16 |    1 |  151 |    2  (0)| 00:00:01 |
    |  11 |      VIEW                  |                          |    1 |    39 |    2  (0)| 00:00:01 |
    |  12 |      TABLE ACCESS FULL    | SYS_TEMP_0FD9D6679_62EC16 |    1 |  151 |    2  (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
    ---------------------------------------------------   3 - filter("FIELDZ"='1' AND "FIELDY"='2') Note
    -----
      - dynamic statistics used: dynamic sampling (level=2)

    We are only reading the table once, from then on it is in PGA which is going to be much faster to access - very little impact.

This discussion has been closed.