OraFAQ Forum: SQL & PL/SQL » with clause logic

Home » SQL & PL/SQL » SQL & PL/SQL » with clause logic

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

with clause logic [message #649724]

Mon, 04 April 2016 15:59

sivaparam
Messages: 16
Registered: March 2005

Junior Member

Hi there,
Could you please explain the logic of this sql?

WITH n( n ) AS
(
SELECT 1 FROM dual
UNION ALL
SELECT n+1 FROM n WHERE n<=9999
)
SELECT * FROM n;

Report message to a moderator

Re: with clause logic [message #649727 is a reply to message #649724]

Mon, 04 April 2016 18:21

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

This is row number generation using recursive subquery factoring. It generates the values 1 through 10,000 with one value per row. The letter n has been used as both a subquery alias and a column alias. I will try to explain it one piece at a time, using a smaller simpler example that generates the numbers 1 through 3 using n as a column alias and t as a subquery alias.

-- The following selects individual numbers, one per row, one row per query:

SCOTT@orcl> SELECT 1 n FROM dual t;

         N
----------
         1

1 row selected.

SCOTT@orcl> SELECT 2 n FROM dual t;

         N
----------
         2

1 row selected.

SCOTT@orcl> SELECT 3 n FROM dual t;

         N
----------
         3

1 row selected.

-- The following selects the same 3 numbers but uses UNION ALL to join the results:

SCOTT@orcl> SELECT 1 n FROM dual t
  2  UNION ALL
  3  SELECT 2 n FROM dual t
  4  UNION ALL
  5  SELECT 3 n FROM dual t;

         N
----------
         1
         2
         3

3 rows selected.

The following uses the above query as a sub-query and selects from it:

SCOTT@orcl> SELECT *
  2  FROM   (SELECT 1 n FROM dual
  3  	     UNION ALL
  4  	     SELECT 2 n FROM dual
  5  	     UNION ALL
  6  	     SELECT 3 n FROM dual) t;

         N
----------
         1
         2
         3

3 rows selected.

The following uses a WITH clause, also known as a sub-query factoring clause.
It is like declaring a subquery before the query instead of within it.
It produces the same result as the query above.

SCOTT@orcl> WITH t(n) AS
  2    (SELECT 1 FROM dual
  3  	UNION ALL
  4  	SELECT 2 FROM dual
  5  	UNION ALL
  6  	SELECT 3 FROM dual)
  7  SELECT * FROM t;

         N
----------
         1
         2
         3

3 rows selected.

The following uses the recursive subquery factoring feature of the subquery factoring clause
by using "SELECT n+1 FROM t WHERE n<=2) to select from itself multiple times,
adding one to the previous value of n each time until it reaches 2+1, which is 3,
producing the same results as the query above.

 
SCOTT@orcl> WITH t(n) AS
  2    (SELECT 1 FROM dual
  3  	UNION ALL
  4  	SELECT n+1 FROM t WHERE n<=2)
  5  SELECT * FROM t;

         N
----------
         1
         2
         3

3 rows selected.

The following substitutes 9 for 2, producing 10 rows. If you use 9999 instead it would produce 10,000 rows, so you can see where it would save a lot of lines of code, instead of doing 10,000 queries with union all between each.

SCOTT@orcl> WITH t(n) AS
  2    (SELECT 1 FROM dual
  3  	UNION ALL
  4  	SELECT n+1 FROM t WHERE n<=9)
  5  SELECT * FROM t;

         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Row number generation is commonly used for various things and this is one of various methods. Recursive subquery factoring also has other usages besides row number generation.

[Updated on: Tue, 05 April 2016 01:01]

Report message to a moderator

Re: with clause logic [message #649733 is a reply to message #649724]

Tue, 05 April 2016 00:32

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

@sivaparam,

Please read How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Report message to a moderator

Re: with clause logic [message #650070 is a reply to message #649724]

Wed, 13 April 2016 23:53

lakshmis
Messages: 102
Registered: November 2008
Location: India

Senior Member

Many Thanks Barbara for the effort taken to explain this clearly !!!

Report message to a moderator

Re: with clause logic [message #650176 is a reply to message #650070]

Mon, 18 April 2016 12:41

Bill B
Messages: 1971
Registered: December 2004

Senior Member

For anyone using this method and having an older database, it is not valid until oracle 11g R2

[Updated on: Mon, 18 April 2016 12:42]

Report message to a moderator

Re: with clause logic [message #650355 is a reply to message #650176]

Sat, 23 April 2016 05:38

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

is this valid with number data type only ?

SQL> with t(n) as
  2  (
  3  select '1-MAR-2016' from dual
  4  UNION ALL
  5  SELECT TO_CHAR(N+1) FROM T WHERE N<='20-MAR-2016'
  6  )
  7  select * from t
  8  /
 
with t(n) as
(
select '1-MAR-2016' from dual
UNION ALL
SELECT TO_CHAR(N+1) FROM T WHERE N<='20-MAR-2016'
)
select * from t
 
ORA-01722: invalid number
 
SQL>

Report message to a moderator

Re: with clause logic [message #650356 is a reply to message #650355]

Sat, 23 April 2016 05:41

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Explain how you add a number to a string.

Report message to a moderator

Re: with clause logic [message #650357 is a reply to message #650356]

Sat, 23 April 2016 05:43

Bill B
Messages: 1971
Registered: December 2004

Senior Member

And you are not selecting a date. You are selecting a string. Head up on to_date and use a format mask

[Updated on: Sat, 23 April 2016 05:43]

Report message to a moderator

Re: with clause logic [message #650358 is a reply to message #650357]

Sat, 23 April 2016 05:55

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

My Bad . I was trying with to_date and to_char both .

SQL> with t(n) as
  2  (
  3  select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
  4  UNION ALL
  5  SELECT TO_DATE(N,'DD-MON-YYYY')+1 FROM T WHERE N<=TO_DATE('20-MAR-2016','DD-MON-YYYY')
  6  )
  7  select * from t
  8  /
 
with t(n) as
(
select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
UNION ALL
SELECT TO_DATE(N,'DD-MON-YYYY')+1 FROM T WHERE N<=TO_DATE('20-MAR-2016','DD-MON-YYYY')
)
select * from t
 
ORA-01790: expression must have same datatype as corresponding expression
 
SQL> select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual;
 
TO_DATE('1-MAR-2016','DD-MON-Y
------------------------------
3/1/2016
 
SQL> select TO_DATE('1-MAR-2016','DD-MON-YYYY')+1 from dual;
 
TO_DATE('1-MAR-2016','DD-MON-Y
------------------------------
3/2/2016
 
SQL>

Report message to a moderator

Re: with clause logic [message #650359 is a reply to message #650358]

Sat, 23 April 2016 05:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Applying TO_DATE to a date is quite silly don't you think?

Report message to a moderator

Re: with clause logic [message #650360 is a reply to message #650359]

Sat, 23 April 2016 06:01

Bill B
Messages: 1971
Registered: December 2004

Senior Member

And it can cause an error depending on the default nls setting. Since a to_date on a date does an implicit conversation to string before it converts back to a date

Report message to a moderator

Re: with clause logic [message #650361 is a reply to message #650360]

Sat, 23 April 2016 06:18

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

SQL> SELECT * FROM (
  2  SELECT TO_DATE('1-MAR-2016','DD-MON-YYYY')+LEVEL DT FROM DUAL CONNECT BY LEVEL <=12
  3  ) WHERE DT <=TO_DATE('10-MAR-2016','DD-MON-YYYY')
  4  /
 
DT
-----------
3/2/2016
3/3/2016
3/4/2016
3/5/2016
3/6/2016
3/7/2016
3/8/2016
3/9/2016
3/10/2016
 
9 rows selected
 
SQL> 
SQL> SELECT * FROM (
  2  SELECT TO_DATE('1-MAR-2016','DD-MON-YYYY')+LEVEL DT FROM DUAL CONNECT BY LEVEL <=12
  3  ) WHERE DT <='10-MAR-2016'
  4  /
 
DT
-----------
3/2/2016
3/3/2016
3/4/2016
3/5/2016
3/6/2016
3/7/2016
3/8/2016
3/9/2016
3/10/2016
 
9 rows selected
 
SQL> 
SQL> with t(n) as
  2  (
  3  select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
  4  UNION ALL
  5  SELECT TO_DATE(N,'DD-MON-YYYY')+1 FROM T WHERE N<='20-MAR-2016'
  6  )
  7  select * from t
  8  /
 
with t(n) as
(
select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
UNION ALL
SELECT TO_DATE(N,'DD-MON-YYYY')+1 FROM T WHERE N<='20-MAR-2016'
)
select * from t
 
ORA-01790: expression must have same datatype as corresponding expression
 
SQL>

Report message to a moderator

Re: with clause logic [message #650366 is a reply to message #650361]

Sat, 23 April 2016 07:24

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Sat, 23 April 2016 12:58

Applying TO_DATE to a date is quite silly don't you think?

Report message to a moderator

Re: with clause logic [message #650370 is a reply to message #650361]

Sat, 23 April 2016 12:56

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

When you "SELECT TO_DATE ('01-MAR-2016', 'DD-MON-YYYY')" that is the starting value of the column n with data type date. When you add one to n you need to just use n+1, because n is already a date. Please see the demonstration below.

SCOTT@orcl> WITH t(n) AS
  2    (SELECT TO_DATE ('01-MAR-2016', 'DD-MON-YYYY') FROM dual
  3  	UNION ALL
  4  	SELECT n+1 FROM t WHERE n <= TO_DATE ('20-MAR-2016', 'DD-MON-YYYY'))
  5  SELECT * FROM t
  6  /

N
---------------
Tue 01-Mar-2016
Wed 02-Mar-2016
Thu 03-Mar-2016
Fri 04-Mar-2016
Sat 05-Mar-2016
Sun 06-Mar-2016
Mon 07-Mar-2016
Tue 08-Mar-2016
Wed 09-Mar-2016
Thu 10-Mar-2016
Fri 11-Mar-2016
Sat 12-Mar-2016
Sun 13-Mar-2016
Mon 14-Mar-2016
Tue 15-Mar-2016
Wed 16-Mar-2016
Thu 17-Mar-2016
Fri 18-Mar-2016
Sat 19-Mar-2016
Sun 20-Mar-2016
Mon 21-Mar-2016

21 rows selected.

Report message to a moderator

Re: with clause logic [message #650371 is a reply to message #650370]

Sat, 23 April 2016 13:19

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

still giving the same error to me:
NLS_RDBMS_VERSION 11.2.0.1.0

SQL> with t(n) as
  2  (
  3  select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
  4  UNION ALL
  5  SELECT n+1 FROM t WHERE n<=TO_DATE('20-MAR-2016','DD-MON-YYYY')
  6  )
  7  select * from t
  8  /
 
with t(n) as
(
select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
UNION ALL
SELECT n+1 FROM t WHERE n<=TO_DATE('20-MAR-2016','DD-MON-YYYY')
)
select * from t
 
ORA-01790: expression must have same datatype as corresponding expression
 
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter ='NLS_DATE_FORMAT';
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR
 
SQL>

[Updated on: Sat, 23 April 2016 13:28]

Report message to a moderator

Re: with clause logic [message #650372 is a reply to message #650371]

Sat, 23 April 2016 13:31

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

live4learn,

Please post the results of

select banner from v$version;

Report message to a moderator

Re: with clause logic [message #650373 is a reply to message #650372]

Sat, 23 April 2016 13:41

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

SQL> select banner from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 
SQL>

I tried altering session parameter for nls_date_formate:

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY';
 
Session altered
 
SQL> 
SQL> with t(n) as
  2  (
  3  select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
  4  UNION ALL
  5  SELECT n+1 FROM t WHERE n <= TO_DATE('20-MAR-2016','DD-MON-YYYY')
  6  )
  7  select * from t
  8  /
 
with t(n) as
(
select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
UNION ALL
SELECT n+1 FROM t WHERE n <= TO_DATE('20-MAR-2016','DD-MON-YYYY')
)
select * from t
 
ORA-01790: expression must have same datatype as corresponding expression
 
SQL>

[Updated on: Sat, 23 April 2016 13:45]

Report message to a moderator

Re: with clause logic [message #650374 is a reply to message #650373]

Sat, 23 April 2016 13:45

Bill B
Messages: 1971
Registered: December 2004

Senior Member

That's why. The version of with you are using isn't available until Oracle 12. Not a problem though. You can do the exact same result using a row generator

Report message to a moderator

Re: with clause logic [message #650375 is a reply to message #650374]

Sat, 23 April 2016 13:50

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

The version of with you are using isn't available until Oracle 12.

Really?

SQL> alter session set nls_date_language=american;

Session altered.

SQL> alter session set nls_date_format='DD-MON-RR';

Session altered.

SQL> with t(n) as
  2  (
  3  select TO_DATE('1-MAR-2016','DD-MON-YYYY') from dual
  4  UNION ALL
  5  SELECT n+1 FROM t WHERE n<=TO_DATE('20-MAR-2016','DD-MON-YYYY')
  6  )
  7  select * from t
  8  /
N
---------
01-MAR-16
02-MAR-16
03-MAR-16
04-MAR-16
05-MAR-16
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
13-MAR-16
14-MAR-16
15-MAR-16
16-MAR-16
17-MAR-16
18-MAR-16
19-MAR-16
20-MAR-16
21-MAR-16

21 rows selected.

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 32-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Report message to a moderator

Re: with clause logic [message #650376 is a reply to message #650375]

Sat, 23 April 2016 13:51

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Yes, recursive subquery factoring was available in 11.2.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#BCEJGIBG

So, the version isn't the problem.

live4learn,

Are you running it from SQL*Plus or from something else?

If not running the code from SQL*Plus, then please try it from SQL*Plus. If you are running the code from SQL*Plus, then please post the results of:

show spparameter compatible

Also, please post the results of:

SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

[Updated on: Sat, 23 April 2016 14:04]

Report message to a moderator

Re: with clause logic [message #650377 is a reply to message #650376]

Sat, 23 April 2016 14:09

Bill B
Messages: 1971
Registered: December 2004

Senior Member

I stand corrected. I was under the impression that it was an Oracle 12c feature. Oh will isn't the first or last time I was wrong.

Report message to a moderator

Re: with clause logic [message #650378 is a reply to message #650376]

Sat, 23 April 2016 14:14

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Running from sql*plus . working fine with numbers.

SQL> show parameters compatible;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
 
SQL> with t(n) as (
  2  select 2 from dual
  3  union all
  4  select n+1 from t where n <=5)
  5  select * from t;
 
         N
----------
         2
         3
         4
         5
         6
 
SQL> 

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';
 
NAME                                                                             VALUE                                                                            DESCRIPTION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
compatible                                                                       11.2.0.0.0                                                                       Database will be completely compatible with this software version
 
SQL> 
SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-YYYY
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
 
17 rows selected
 
SQL>

[Updated on: Sat, 23 April 2016 14:21]

Report message to a moderator

Re: with clause logic [message #650379 is a reply to message #650378]

Sat, 23 April 2016 14:19

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

I am unable to reproduce your error or figure out what might be wrong, but I only have a 12c database to test with, not 11g. Since Michel has the same version that you do, perhaps he can help compare what is different between your system and his that might make the difference.

Report message to a moderator

Re: with clause logic [message #650380 is a reply to message #650379]

Sat, 23 April 2016 14:22

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Yeah..Thanks all for your time and effort .

Report message to a moderator

Re: with clause logic [message #650381 is a reply to message #650380]

Sat, 23 April 2016 14:35

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

A little web research reveals that it may be a bug in that version that may be overcome by casting, as shown below. You are using 11.2.0.0.0. and Michel is using 11.2.0.4.0, apparently a patched version which is enough to make the difference.

WITH t(n) AS
  (SELECT CAST (TO_DATE ('1-MAR-2016', 'DD-MON-YYYY') AS DATE) n FROM dual
   UNION ALL
   SELECT CAST (n+1 AS DATE) FROM t WHERE n <= TO_DATE ('20-MAR-2016', 'DD-MON-YYYY'))
SELECT * FROM t;

Report message to a moderator

Re: with clause logic [message #650382 is a reply to message #650381]

Sat, 23 April 2016 14:46

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

It got executed but going on endlessly ..

SQL> WITH t(n) AS
  2    (SELECT CAST (TO_DATE ('1-MAR-2016', 'DD-MON-YYYY') AS DATE) n FROM dual
  3     UNION ALL
  4     SELECT CAST (n+1 AS DATE) FROM t WHERE n <= TO_DATE ('20-MAR-2016', 'DD-MON-YYYY'))
  5  SELECT * FROM t;
 
N
-----------
3/1/2016
2/29/2016
2/28/2016
...
...
9/3/2003
9/2/2003
 
4565 rows selected -- until I force closed here

Report message to a moderator

Re: with clause logic [message #650383 is a reply to message #650382]

Sat, 23 April 2016 14:48

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Also, results are in reverse order ..

Report message to a moderator

Re: with clause logic [message #650384 is a reply to message #650383]

Sat, 23 April 2016 14:53

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

At least that's progress. Try also casting the date in the where clause.

WITH t(n) AS
  (SELECT CAST (TO_DATE ('1-MAR-2016', 'DD-MON-YYYY') AS DATE) n FROM dual
   UNION ALL
   SELECT CAST (n+1 AS DATE) FROM t WHERE n <= CAST (TO_DATE ('20-MAR-2016', 'DD-MON-YYYY') AS DATE))
SELECT * FROM t;

[Updated on: Sat, 23 April 2016 15:03]

Report message to a moderator

Re: with clause logic [message #650385 is a reply to message #650384]

Sat, 23 April 2016 15:02

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Tried this also ..same result. Thanks Barbara.

Report message to a moderator

Re: with clause logic [message #650386 is a reply to message #650385]

Sat, 23 April 2016 15:03

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Please also try reversing the date order by starting with 20-MAR, subtracting and ending with 01-MAR.

WITH t(n) AS
  (SELECT CAST (TO_DATE ('20-MAR-2016', 'DD-MON-YYYY') AS DATE) n FROM dual
   UNION ALL
   SELECT CAST (n-1 AS DATE) FROM t WHERE n > CAST (TO_DATE ('01-MAR-2016', 'DD-MON-YYYY') AS DATE))
SELECT * FROM t;

Report message to a moderator

Re: with clause logic [message #650388 is a reply to message #650383]

Sat, 23 April 2016 17:02

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

live4learn wrote on Sat, 23 April 2016 15:48

Also, results are in reverse order ..

Only ORDER BY guarantees order.

SY.

Report message to a moderator

Re: with clause logic [message #650389 is a reply to message #650388]

Sat, 23 April 2016 17:17

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Solomon Yakobson wrote on Sat, 23 April 2016 15:02

live4learn wrote on Sat, 23 April 2016 15:48
Also, results are in reverse order ..

Only ORDER BY guarantees order.

SY.

Yes, but I was suggesting reversing the order as a method of working around the bug in order to get the results without producing endless rows, as in one of the solutions here:

http://stackoverflow.com/questions/2586653/recursive-sql-giving-ora-01790/2851318#2851318

Report message to a moderator

Re: with clause logic [message #650390 is a reply to message #650389]

Sat, 23 April 2016 21:45

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Still, I'd try

WITH t(n) AS
  (SELECT CAST (TO_DATE ('1-MAR-2016', 'DD-MON-YYYY') AS DATE) n FROM dual
   UNION ALL
   SELECT CAST (n+1 AS DATE) FROM t WHERE n <= TO_DATE ('20-MAR-2016', 'DD-MON-YYYY'))
SEARCH DEPTH FIRST BY n SET ord_n
SELECT * FROM t
ORDER BY ord_n;

SY.

[Updated on: Sat, 23 April 2016 21:46]

Report message to a moderator

Re: with clause logic [message #650406 is a reply to message #650390]

Sun, 24 April 2016 06:55

Bill B
Messages: 1971
Registered: December 2004

Senior Member

One thing your site should look into is have your dba install the latest patch set on your database. This would fix a lot of problems. I'm not saying patch to fix your casting problem. I'm saying patch because your site should be up to date.

Report message to a moderator

Previous Topic:	need to get row count for database with 20 k tables
Next Topic:	Nested cursor expressions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 11:35:15 CDT 2024