Home » SQL & PL/SQL » SQL & PL/SQL » sql help
sql help [message #232270] Fri, 20 April 2007 00:04 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
I wrote this query, I am trying without Sunday records. That time my query not working properly.plase help me.

SELECT * FROM (SELECT THEDATE, TO_CHAR( THEDATE,'day') AS srinu  FROM (SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE
FROM   ( SELECT 1 just_a_column
         FROM dual
         CONNECT BY LEVEL <= 365
       ))) WHERE  srinu NOT IN( 'sunday')
Re: sql help [message #232274 is a reply to message #232270] Fri, 20 April 2007 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(trunc(sysdate,'year')+rownum-1,'DD/MM/YYYY') "Date",
  2         to_char(trunc(sysdate,'year')+rownum-1,'Day') "Day"
  3  from dual
  4  where to_char(trunc(sysdate,'year')+rownum-1,'Day','NLS_DATE_LANGUAGE=''AMERICAN''') != 'Sunday'
  5  connect by level <= 365
  6  /
Date       Day
---------- --------
01/01/2007 Lundi
02/01/2007 Mardi
03/01/2007 Mercredi
04/01/2007 Jeudi
05/01/2007 Vendredi
06/01/2007 Samedi
07/01/2007 Dimanche
08/01/2007 Lundi
09/01/2007 Mardi
10/01/2007 Mercredi
11/01/2007 Jeudi
12/01/2007 Vendredi
13/01/2007 Samedi
14/01/2007 Dimanche
15/01/2007 Lundi
16/01/2007 Mardi
17/01/2007 Mercredi
...

Regards
Michel
Re: sql help [message #232277 is a reply to message #232274] Fri, 20 April 2007 00:23 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
see ur query output. but i dont need sunday
SELECT TO_CHAR(TRUNC(SYSDATE,'year')+ROWNUM-1,'DD/MM/YYYY') "Date",
          TO_CHAR(TRUNC(SYSDATE,'year')+ROWNUM-1,'Day') "Day"
    FROM dual
    WHERE TO_CHAR(TRUNC(SYSDATE,'year')+ROWNUM-1,'Day','NLS_DATE_LANGUAGE=''AMERICAN''') != 'Sunday'
    CONNECT BY LEVEL <= 365

Date	Day

01/01/2007	Monday   
02/01/2007	Tuesday  
03/01/2007	Wednesday
04/01/2007	Thursday 
05/01/2007	Friday   
06/01/2007	Saturday 
07/01/2007	Sunday   
08/01/2007	Monday   
09/01/2007	Tuesday  
10/01/2007	Wednesday
11/01/2007	Thursday 
12/01/2007	Friday   
13/01/2007	Saturday 
14/01/2007	Sunday   
15/01/2007	Monday   
16/01/2007	Tuesday  
17/01/2007	Wednesday
18/01/2007	Thursday 
19/01/2007	Friday   
20/01/2007	Saturday 
..........................
.......................
Re: sql help [message #232281 is a reply to message #232277] Fri, 20 April 2007 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Write it too fast but easy to correct.
Can you try to correct it? Good exercise.

Regards
Michel
Re: sql help [message #232283 is a reply to message #232277] Fri, 20 April 2007 01:06 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i know
my query and u's query same results.

i tried several times. i am not getting.

thanks
srinivas
Re: sql help [message #232285 is a reply to message #232283] Fri, 20 April 2007 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A little effort, almost just one line to move and a small correction in to_char.
There are 2 bugs in my query. Why doesn't it work?

Regards
Michel
Re: sql help [message #232290 is a reply to message #232285] Fri, 20 April 2007 01:22 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i dont know what u'r saying

thanks
srinivas
Re: sql help [message #232293 is a reply to message #232290] Fri, 20 April 2007 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try to modify my query?
How?

Regards
Michel
Re: sql help [message #232302 is a reply to message #232293] Fri, 20 April 2007 01:33 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi,

once agian

u's query and my query same results.

no diff

thanks
srinivas
Re: sql help [message #232307 is a reply to message #232302] Fri, 20 April 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again there are 2 bugs in my query.
Try to modify it and you get a solution.
As long as you don't modify it you get the same result!

Regards
Michel
Re: sql help [message #232308 is a reply to message #232307] Fri, 20 April 2007 01:50 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

hello
see this query its work

triming problem ok
	
SELECT * FROM (SELECT THEDATE, TO_CHAR(THEDATE,'day') AS srinu  FROM (SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE
FROM   ( SELECT 1 just_a_column
         FROM dual
         CONNECT BY LEVEL <= 365
       )))  	   
	   WHERE  trim(srinu) != 'sunday'


thanks
srinivas
Re: sql help [message #232310 is a reply to message #232308] Fri, 20 April 2007 01:56 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, give you mine:
SQL> select * from (
  2  select to_char(trunc(sysdate,'year')+rownum-1,'DD/MM/YYYY') "Date",
  3         to_char(trunc(sysdate,'year')+rownum-1,'fmDay','NLS_DATE_LANGUAGE=''AMERICAN''') "Day"
  4  from dual
  5  connect by level <= 365
  6  )
  7  where "Day" != 'Sunday'
  8  /
Date       Day
---------- ---------
01/01/2007 Monday
02/01/2007 Tuesday
03/01/2007 Wednesday
04/01/2007 Thursday
05/01/2007 Friday
06/01/2007 Saturday
08/01/2007 Monday
09/01/2007 Tuesday
10/01/2007 Wednesday
11/01/2007 Thursday
12/01/2007 Friday
13/01/2007 Saturday
15/01/2007 Monday
16/01/2007 Tuesday
17/01/2007 Wednesday
18/01/2007 Thursday
...

Regards
Michel
Previous Topic: Execute Immediate with DML error: column not allowed here
Next Topic: multi table insert query
Goto Forum:
  


Current Time: Mon Dec 05 15:21:44 CST 2016

Total time taken to generate the page: 0.05196 seconds