OraFAQ Forum: SQL & PL/SQL » WITH clause working in 10g but not in 11g (XE)

Home » SQL & PL/SQL » SQL & PL/SQL » WITH clause working in 10g but not in 11g (XE) (Oracle 10g and 11g)

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

WITH clause working in 10g but not in 11g (XE) [message #644969]

Mon, 23 November 2015 00:18

Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata

Junior Member

Hi Friends,

I'm trying to get all the Fridays since Jan-2000 till today.
Now there can be two approaches as far as I know
a) Using a CONNECT BY with dual.
b) Using a Recursive query using WITH - I'm facing issues here. Confused

Query using WITH

with dte (dat) as
(select sysdate dat from dual
 union all
 select dat-1 from dte where dat>to_date('Jan-01-2000','Mon-DD-YYYY'))
select to_char(dat,'DD-MM-YYYY Day') from dte where to_char(dat,'DY') in ('FRI');

Description: Get the current date using sysdate.
Run a recursive union on it inside WITH until the date is > my desired old date (which is Jan 2000 here).

Issue: This query works fine in 10g. But in 11g (XE edition) it gives me the error "ORA-01790: expression must have same datatype as corresponding expression"
As far as I'm guessing its cuz oracle doesnt recognize the Column alias "dat" as a date field. But am not sure.
Can anyone tell me why is this so?

Anyone with access to 11g full edition can you please try and tell if its cuz of the XE thing?

Thank You
Rishab

[Updated on: Mon, 23 November 2015 01:20]

Report message to a moderator

Re: WITH clause working in 10g but not in 11g (XE) [message #644976 is a reply to message #644969]

Mon, 23 November 2015 01:29

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

Senior Member
Account Moderator

Quote:

This query works fine in 10g.

It can't, recursive queries have been introduced in 11g.

In addition, it "works" in 11g:

SQL> with dte (dat) as
  2  (select sysdate dat from dual
  3   union all
  4   select dat-1 from dte where dat>to_date('Jan-01-2000','Mon-DD-YYYY'))
  5  select to_char(dat,'DD-MM-YYYY Day') from dte where to_char(dat,'DY') in ('FRI');
TO_CHAR(DAT,'DD-MM-Y
--------------------
20-11-2015 Friday
13-11-2015 Friday
06-11-2015 Friday
30-10-2015 Friday
23-10-2015 Friday
16-10-2015 Friday
09-10-2015 Friday
02-10-2015 Friday
25-09-2015 Friday
18-09-2015 Friday
11-09-2015 Friday
04-09-2015 Friday
28-08-2015 Friday
...
03-03-2000 Friday
25-02-2000 Friday
18-02-2000 Friday
11-02-2000 Friday
04-02-2000 Friday
28-01-2000 Friday
21-01-2000 Friday
14-01-2000 Friday
07-01-2000 Friday
31-12-1999 Friday

830 rows selected.

Report message to a moderator

Re: WITH clause working in 10g but not in 11g (XE) [message #644982 is a reply to message #644976]

Mon, 23 November 2015 01:41

Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata

Junior Member

So basically its the 11g XE thing?

Report message to a moderator

Re: WITH clause working in 10g but not in 11g (XE) [message #644983 is a reply to message #644982]

Mon, 23 November 2015 02:04

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

Senior Member
Account Moderator

I don't know as I don't know what you actually have.
Use SQL*Plus and copy and paste your session as I did adding the following command:

def _O_VERSION

Report message to a moderator

Previous Topic:	Sysdate to_char DAY conversion not comparable
Next Topic:	opposite of listagg

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 21:26:11 CDT 2024