Home » SQL & PL/SQL » SQL & PL/SQL » Problem in Date - Group by
Problem in Date - Group by [message #249920] Fri, 06 July 2007 09:40 Go to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

I need a query to bring the data group by a date column

If the date fall under sat or sun that date should consider as mon and should come in o/p along with monday data

i have a query but that does not works

select to_char(decode(TO_CHAR(completiontime,'d'),7,completiontime+2,1,completiontime+1,completiontime),'YYYY-MM-DD'), rpad(mt.rtxntype,25,' ') as email_category
FROM itps.master_txn mt, itps.cust_serv_outcome cso
WHERE pub_desc = 'ABC'

GROUP BY TO_CHAR(cso.completiontime, 'YYYY-MM-DD'), mt.rtxntype

ORDER BY TO_CHAR(cso.completiontime, 'YYYY-MM-DD') DESC, mt.rtxntype;

Please post your ideas

Thanks in advance

Imran
Re: Problem in Date - Group by [message #249942 is a reply to message #249920] Fri, 06 July 2007 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming you use an english family language:
select case 
       when to_char(mydate,'Day')='Sat' then to_char(mydate+2,'YYYY-MM-DD')
       when to_char(mydate,'Day')='Sun' then to_char(mydate+1,'YYYY-MM-DD')
       else to_char(mydate,'YYYY-MM-DD')
       end mydate,
       ... 
from ...
where ...
group by case 
         when to_char(mydate,'Day')='Sat' then to_char(mydate+2,'YYYY-MM-DD')
         when to_char(mydate,'Day')='Sun' then to_char(mydate+1,'YYYY-MM-DD')
         else to_char(mydate,'YYYY-MM-DD')
         end 
/

Regards
Michel

[Updated on: Fri, 06 July 2007 10:18]

Report message to a moderator

Re: Problem in Date - Group by [message #249992 is a reply to message #249920] Fri, 06 July 2007 12:54 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Imran_Chennai wrote on Fri, 06 July 2007 10:40

select to_char(decode(TO_CHAR(completiontime,'d'),7,completiontime+2,1,completiontime+1,completiontime),'YYYY-MM-DD'), rpad(mt.rtxntype,25,' ') as email_category
FROM itps.master_txn mt, itps.cust_serv_outcome cso
WHERE pub_desc = 'ABC'

GROUP BY TO_CHAR(cso.completiontime, 'YYYY-MM-DD'), mt.rtxntype

ORDER BY TO_CHAR(cso.completiontime, 'YYYY-MM-DD') DESC, mt.rtxntype;



Go with Michel's answer, but just to let you know, if you do a TO_CHAR, it obviously returns a character string, not a number, so you cannot have a 7 or 1 as the comparison values.
Additionally, you have to group by values you SELECT. Your GROUP BY clause does not match your SELECT statements.
Re: Problem in Date - Group by [message #250116 is a reply to message #249992] Sun, 08 July 2007 02:07 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
joy_division wrote on Fri, 06 July 2007 12:54

...but just to let you know, if you do a TO_CHAR, it obviously returns a character string, not a number, so you cannot have a 7 or 1 as the comparison values.



To put it mildly - you are wrong.
Pls, read about IMPLICIT CONVERSION in Oracle.
SQL> select decode('7',7,'true','false') from dual;

DECODE('7',7,'TRUE','FALSE')
----------------------------
true

SQL> 


BTW
to_char(date,'d') is nls_territory dependent
to_char(date,'day') is also nls dependent, you can use this code only in english.

So if you want to NLS independently find out the week day of the date - you better use for example julian date:

select decode(mod(to_char(dt, 'j'), 7), 5, dt + 2, 6, dt + 1, dt),
       to_char(dt,'DAY') old_week_day,
       to_char(decode(mod(to_char(dt, 'j'), 7), 5, dt + 2, 6, dt + 1, dt),'DAY') new_week_day
  from (select sysdate + level dt from dual connect by level < 10)


@author
joy_division was right when said that for making your GROUP BY clause work correctly you should place there expressions from your SELECT list.

For your better understanding - small example:

SQL> with t as (select 1 num from dual union all
  2             select 2 num from dual union all
  3             select 3 num from dual)
  4             select decode(num,1,2,num) new_num from t
  5              group by num
  6  /

   NEW_NUM
----------
         2
         2
         3

SQL> 
SQL> with t as (select 1 num from dual union all
  2             select 2 num from dual union all
  3             select 3 num from dual)
  4             select decode(num,1,2,num) new_num from t
  5              group by decode(num,1,2,num)
  6  /

   NEW_NUM
----------
         2
         3

SQL> 


and the last: when your SELECT and GROUP BY lists are the same - GROUP BY is equivalent to DISTINCT.
Re: Problem in Date - Group by [message #250284 is a reply to message #250116] Mon, 09 July 2007 08:17 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Volder wrote on Sun, 08 July 2007 03:07
joy_division wrote on Fri, 06 July 2007 12:54

...but just to let you know, if you do a TO_CHAR, it obviously returns a character string, not a number, so you cannot have a 7 or 1 as the comparison values.



To put it mildly - you are wrong.
Pls, read about IMPLICIT CONVERSION in Oracle.
[code]



Yes, with implicit conversion it will work, but do you want to rely on implicit conversion? It's sloppy.
Re: Problem in Date - Group by [message #250326 is a reply to message #250284] Mon, 09 July 2007 11:41 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
joy_division wrote on Mon, 09 July 2007 08:17
... but do you want to rely on implicit conversion? It's sloppy.



sounds like a ROT.
And if it is always sloppy - what for it is embeded in the Oracle? I mean implicit conversion.

I agree with you, that you shouldn't rely on implicit conversion in that case (because NLS parameters can be changed):

SQL> select * from dual
  2   where trunc(sysdate)='09-JUL-2007'
  3  /

DUMMY
-----
X

SQL>  alter session set NLS_DATE_LANGUAGE='RUSSIAN';

Session altered

SQL> 
SQL> select * from dual
  2   where trunc(sysdate)='09-JUL-2007'
  3  /

select * from dual
 where trunc(sysdate)='09-JUL-2007'

ORA-01858: a non-numeric character was found where a numeric was expected

SQL>


but can you give me an example when code like the following won't work:
SQL> select decode(mod(to_char(sysdate,'j'),7),0,'today is MONDAY') from dual;

DECODE(MOD(TO_CHAR(SYSDATE,'J'
------------------------------
today is MONDAY

SQL>


of course I can write
SQL> select decode(mod(to_number(to_char(sysdate,'j')),7),0,'today is MONDAY') from dual;

DECODE(MOD(TO_NUMBER(TO_CHAR(S
------------------------------
today is MONDAY

SQL> 


But do you really think that without TO_NUMBER() such a code would be sloppy here?
Re: Problem in Date - Group by [message #250337 is a reply to message #250326] Mon, 09 July 2007 12:49 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I can't think of one of the top of my head, but maybe one of regulars can come up with one.
It's just good programming practice not to rely on implicit conversion. Who knows if it will always work the same in future releases.

[Updated on: Fri, 13 July 2007 08:04]

Report message to a moderator

Re: Problem in Date - Group by [message #250343 is a reply to message #250337] Mon, 09 July 2007 13:05 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
joy_division wrote on Mon, 09 July 2007 12:49
He who must not be named:

I can't think of one of the top of my head, but maybe one of regulars can come up with one.
It's just good programming practice not to rely on implicit conversion. Who knows if it will always work the same in future releases.



well, I think Oracle would provide compatibility with previous versions. Other way - it would be also possible that explicit conversion can also be changed. Cool

Of course, I'm speaking about documented features.
Because non-documented they can really change - and they do, e.g. in versions previous to 10q GROUP BY clause implicitly provided ORDER BY on the same columns, and in 10g it is not so.
So if you put it in the code in 9i, on 10g it wouldn't work properly.
But implicit conversion is documented. And of course it depends on the situation.
I gave you examples.
Sometimes you can rely on implicit conversion also, IMHO.
Re: Problem in Date - Group by [message #250344 is a reply to message #250337] Mon, 09 July 2007 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I fully agree with you.
If you want to use string use char family datatype.
If you want to use number (calculate on it) use number datatype.
If you need to convert from one to other one use convert function.
Implicit conversion is evil. It works on ONE release and never guarantee to work on the next one or if other parameters change.

Regards
Michel
Re: Problem in Date - Group by [message #250427 is a reply to message #250344] Tue, 10 July 2007 00:51 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I have no doubt implicit conversion from integer to char vice versa will work in future releases.
I just think it's good coding practice to do explicit conversions. Maybe it's overdone, maybe it even is a religion, but although I am aware of the fact that it is not actually necessary, I would promote it amongst juniors (even force them to)

[Edit: typo]

[Updated on: Tue, 10 July 2007 00:52]

Report message to a moderator

Previous Topic: mixing rows
Next Topic: User created datatype as OUT param in procedure - how do we read it?
Goto Forum:
  


Current Time: Sat Dec 10 16:34:42 CST 2016

Total time taken to generate the page: 0.09010 seconds