CTE Issue

3234063 · May 17, 2016 10:16PM

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

Solomon Yakobson · May 17, 2016 10:31PM

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.

Solomon Yakobson · May 17, 2016 10:31PM

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.

Frank Kulash · 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 · 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 · May 21, 2016 11:07AM

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 · May 21, 2016 11:08AM

No ordering is guaranteed unless you use ORDER BY.

Kalpataru · May 21, 2016 11:37AM

Check this thread may help you.

Using CTE in Oracle

Vysakh Suresh - 3035408 · 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 · May 22, 2016 6:18AM

fyi, records have fields, tables have columns.

Solomon Yakobson · May 22, 2016 6:46AM

Very ineffective. It will scan table 21 times.

SY.

AndrewSayer · 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.

Forum Stats

Quick Links

Categories

CTE Issue

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

CTE Issue

Best Answer

Answers