OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Urgent Query

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

Urgent Query [message #22818]

Wed, 30 October 2002 23:18

PRASHANT SHEKHAR
Messages: 13
Registered: October 2002

Junior Member

One of my query gives

SELECT to_char(dt,'Mon') month,
to_char(dt,'YYYY') year,
SUM(VaLue) VALUE
FROM trade
WHERE serial_no = '4320'
and dt > '01-Nov-2001'
and dt < '01-May-2002'
group by to_char(dt,'Mon'),to_char(dt,'YYYY')

me the follwing result
MON YEAR VALUE
--- ---- ----------
Nov 2001 8768
Mar 2002 1050
Apr 2002 24440

But I want the following Result
MON YEAR VALUE
--- ---- ----------
Nov 2001 8768
Dec 2001 0
Jan 2002 0
Feb 2002 0
Mar 2002 1050
Apr 2002 24440

Report message to a moderator

Re: Urgent Query [message #22819 is a reply to message #22818]

Wed, 30 October 2002 23:44

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Try this:

SELECT sel.mon
     , sel.year
     , sel.value
  FROM (<B>*your_select*</B>) sel
 ORDER BY to_date(sel.year,'YYYY')
        , to_date(sel.month,'MON')

HTH,
MHE

Report message to a moderator

Re: Urgent Query [message #22822 is a reply to message #22818]

Thu, 31 October 2002 01:07

PRASHANT SHEKHAR
Messages: 13
Registered: October 2002

Junior Member

Maaher,

I tried this , it gives the same result
MON YEAR VALUE
--- ---- ----------
nov 2001 8768
mar 2002 1050
apr 2002 24440

There are no entry for Dec 2001, Jan 2002, Feb 2002
( If there is no entry then display the successive month with value 0 )
Output should be as

MON YEAR VALUE
--- ---- ----------
nov 2001 8768
dec 2001 0
jan 2002 0
feb 2002 0
mar 2002 1050
apr 2002 24440

Report message to a moderator

Re: Urgent Query [message #22868 is a reply to message #22818]

Sat, 02 November 2002 19:19

Su
Messages: 154
Registered: April 2002

Senior Member

Oracle does not output any value which it did not find in the table. So you can not expect ORACLE to produce such a value like that. Either you have to write your own function to do that or a virtual table manipulating to do such things.
Good luck :)

Report message to a moderator

Previous Topic:	How to insert a special character
Next Topic:	How to judge a columne's datatype is number or not ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 28 16:28:18 CDT 2024