Dealing with a lot of columns in a recursive cte

Question

I want to retrive sets of hierarchical structured data from a table and I found a solution for sql-server in this post. Porting the solution found there to oracle dosnt quite work. As it seems i have to specify an alias list in my case as follows:

WITH cte ( ID ) AS (
  SELECT  T.ID
  FROM SomeTable T
  WHERE T.ID = 1
  UNION ALL
  SELECT  T.ID
  FROM SomeTable T
  INNER JOIN cte C
  ON T.BASE_ID = C.ID
)
SELECT *
from cte;

The Problem i have now is that SomeTable has 200 columns and i need quite a lot of them. I cant just replace the selects in the with-statement with SELECT T.* as i then get the ORA-01789: query block has incorrect number of result columns error unless i add all columns to the alias list (which makes sense). That of course would be rather unwieldy for the given table.

Is there a way for oracle-sql to tell the cte to use all columns without the need to explicitly list them all?

*Porting the solution found there to oracle dosnt quite work.*: this query would probably work in oracle, what issues are you having with it? — GMB, Dec 05 '19 at 11:06
"*without the need to explicitly list them all*" no there isn't — , Dec 05 '19 at 11:15
A convenient way to produce a SELECT on many fields is to use DBA_TAB_COLUMNS table to produce the fieldlist. Something like that : SELECT column_name FROM DBA_ALL_COLUMNS WHERE table_name='SomeTable' ORDER BY column_id. Then you can just use string aggregation function to directly copy/paste the result in your big SELECT query — LostReality, Dec 05 '19 at 11:20

score 3 · Accepted Answer · answered Dec 05 '19 at 11:31

3

If you don't need the columns for the recursive definition, just join them afterwards:

select t.*
from cte join
     t
     on cte.id = t.id

answered Dec 05 '19 at 11:31

Gordon Linoff

1,242,037
58
646
786

i gues that one was to obvious for me... thanks a lot – Flowly Dec 05 '19 at 11:52

score 0 · Answer 2 · answered Dec 05 '19 at 11:14

0

Same number of columns required in order to perform a UNION ALL.

Look at Oracle reference: The UNION ALL, INTERSECT, MINUS Operators

answered Dec 05 '19 at 11:14

Vignesh Kumar A

27,863
13
63
115

score 0 · Answer 3 · answered Dec 05 '19 at 11:20

I assume SOMETABLE has 200 columns. I have created a With Clause having two expressions. 1st expression cte1 which will have all the columns. Second expression cte2 gets the data using 1st expression(cte1).

WITH cte1 AS (
SELECT T.*
FROM SOMETABLE T
WHERE T.ID = 1),
cte2 AS(SELECT C.*
FROM SOMETABLE T
INNER JOIN cte1 C ON T.BASE_ID = C.ID
)
SELECT * FROM cte2;

I hope this helps to get all the columns.

Dealing with a lot of columns in a recursive cte

3 Answers3