Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query doubt
SQL Query doubt [message #274303] Mon, 15 October 2007 08:30 Go to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

I have a query a shown below:

 Select to_char(hire_date,'Mon') Mon, count(*)num 
       from emp
       group by to_char(hire_date,'Month'); 


Output :
Mon num
JAN 2
OCT 4
DEC 8

But i want the output as:

Output :
Mon num
JAN 2
FEB 0
MAR 0
APR 0
MAY 0
JUN 0
JUL 0
AUG 0
SEP 0
OCT 0
NOV 4
DEC 8

How can i get this.

Thanks,
Srinivas

Re: SQL Query doubt [message #274305 is a reply to message #274303] Mon, 15 October 2007 08:46 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Outer join to a table that contains a list of all the months.
Re: SQL Query doubt [message #274307 is a reply to message #274303] Mon, 15 October 2007 08:54 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
I don't have any other table.
But can i do it using with data clause
Re: SQL Query doubt [message #274308 is a reply to message #274307] Mon, 15 October 2007 08:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
that'll do it, provided you mean the with clause

[Updated on: Mon, 15 October 2007 08:58]

Report message to a moderator

Re: SQL Query doubt [message #274310 is a reply to message #274303] Mon, 15 October 2007 08:58 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
i will use it,is there any other query to retrive the data,using one table.
Re: SQL Query doubt [message #274311 is a reply to message #274310] Mon, 15 October 2007 09:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
If you want to make life difficult for yourself, you could always look into the model clause. Do you have some objection to using the method I suggested?
Re: SQL Query doubt [message #274312 is a reply to message #274303] Mon, 15 October 2007 09:04 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
No body wants life to be difficult Razz .
I just wanted to know other differnt methods
Re: SQL Query doubt [message #274320 is a reply to message #274303] Mon, 15 October 2007 10:03 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

I tried with "with clause " it worked.
I was trying with the below query is getting error:

 select a.mon, count(b.cust_code)
 from ar_cust b, (SELECT to_char(to_Date(rownum,'mm'),'Mon') MON
 FROM ALL_OBJECTS
 WHERE ROWNUM <=12) A
  where to_char(b.create_dttm,'Mon')(+) = a.mon
  group by a.mon;


where to_char(b.create_dttm,'Mon')(+) = a.mon
*
ERROR at line 5:
ORA-00936: missing expression

[Updated on: Mon, 15 October 2007 10:03]

Report message to a moderator

Re: SQL Query doubt [message #274325 is a reply to message #274320] Mon, 15 October 2007 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
to_char(b.create_dttm(+),'Mon') = a.mon

Regards
Michel

[Updated on: Mon, 15 October 2007 10:25]

Report message to a moderator

Re: SQL Query doubt [message #274406 is a reply to message #274303] Mon, 15 October 2007 23:33 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
thanks
Re: SQL Query doubt [message #274407 is a reply to message #274303] Mon, 15 October 2007 23:40 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
But i am getting output as:

FEB 0
MAR 7
JAN 0
APR 0
MAY 4
JUL 0
DEC 0
OCT 0
NOV 0
JUN 0
AUG 0
SEP 0

But i want the output in sorted on mon like:

JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC

Thanks
Srinivas
Re: SQL Query doubt [message #274410 is a reply to message #274303] Tue, 16 October 2007 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
srinivas.k2005,
you post text which is indeterminate.

post CUT & PASTE showing actual code & results; so we can actually see what you are really doing & why you have problem.
Re: SQL Query doubt [message #274469 is a reply to message #274303] Tue, 16 October 2007 03:17 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Sorry..

SQL>  select a.mon, count(b.cust_code)
  2    from ar_cust b, (SELECT to_char(to_Date(rownum,'mm'),'Mon') MON
  3    FROM ALL_OBJECTS
  4    WHERE ROWNUM <=12) A
  5   where to_char(b.create_dttm(+),'Mon') = a.mon
  6   group by a.mon;

MON COUNT(B.CUST_CODE)
--- ------------------
Feb                  1
Jun                  1
Nov                  0
Sep                  0
Jul                  1
Oct               1328
Aug                  0
Jan                  0
Dec                  0
Apr                  5
Mar                  0

MON COUNT(B.CUST_CODE)
--- ------------------
May                  0

12 rows selected.


but i want the output as:

MON COUNT(B.CUST_CODE)
--- ------------------
Jan                  0
Feb                  1
Mar                  0
Apr                  5
May                  0
Jun                  1
Jul                  1
Aug                  0
Sep                  0
Oct               1328
Nov                  0

MON COUNT(B.CUST_CODE)
--- ------------------
Dec                  0

12 rows selected.


Thanks

Re: SQL Query doubt [message #274473 is a reply to message #274469] Tue, 16 October 2007 03:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
well for a start, if you want an ordered resultset, you need to include an order by clause.
Re: SQL Query doubt [message #274482 is a reply to message #274303] Tue, 16 October 2007 03:50 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Please see my question clearly,if a small order by clause would solve my problem ,i will be the happiest person in the world.
Hello can you tell me on what column will you order.
so that you will get the output as shown above.
Re: SQL Query doubt [message #274484 is a reply to message #274482] Tue, 16 October 2007 03:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Think.
What do you want the result to be ordered by?

hint: use to_date, using your mon column
Re: SQL Query doubt [message #274558 is a reply to message #274303] Tue, 16 October 2007 07:27 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Thanks Frank, your support helped me.

SQL> select a.mon, count(b.cust_code)
  2    from ar_cust b, (SELECT to_char(to_Date(rownum,'mm'),'Mon') MON
  3    FROM ALL_OBJECTS
  4     WHERE ROWNUM <=12) A
  5    where to_char(b.create_dttm(+),'Mon') = a.mon
  6    group by a.mon
  7  order by to_date(a.mon,'mm');

MON COUNT(B.CUST_CODE)
--- ------------------
Jan                  0
Feb                  1
Mar                  0
Apr                  5
May                  0
Jun                  1
Jul                  1
Aug                  0
Sep                  0
Oct               1328
Nov                  0

MON COUNT(B.CUST_CODE)
--- ------------------
Dec                  0

12 rows selected.



Re: SQL Query doubt [message #274577 is a reply to message #274558] Tue, 16 October 2007 08:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is not correct.
You column mon is NOT in 'mm' format.
Re: SQL Query doubt [message #274623 is a reply to message #274303] Tue, 16 October 2007 11:06 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
But i got the output correct without any error.
it is ordered based on below query and the output is what i expected.

Frank, Then what was the exact solution you hinted me

select to_date('JAN','mm') from dual;


Thanks,
Srinivas
Re: SQL Query doubt [message #274628 is a reply to message #274623] Tue, 16 October 2007 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_date('JAN','mm') from dual;
select to_date('JAN','mm') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

This is not what Frank "hinted" you, it "hinted" you this is not the good way (format model).

Regards
Michel
Re: SQL Query doubt [message #274634 is a reply to message #274303] Tue, 16 October 2007 11:53 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
This is really shocking for me.Will the version of oracle or any other settings matters?

Its working fine in my system.
Please see below.

SQL*Plus: Release 8.1.6.0.0 - Production on Tue Oct 16 22:27:48 2007

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select to_date('JAN','mm') from dual;

TO_DATE('
---------
01-JAN-07

SQL> 


Re: SQL Query doubt [message #274641 is a reply to message #274303] Tue, 16 October 2007 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>This is really shocking for me.Will the version of oracle or any other settings matters?

Yes, the version of oracle or any other settings matter.
Re: SQL Query doubt [message #274649 is a reply to message #274634] Tue, 16 October 2007 12:46 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
srinivas.k2005 wrote on Tue, 16 October 2007 12:53


SQL*Plus: Release 8.1.6.0.0 - Production on Tue Oct 16 22:27:48 2007

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options





You also should really upgrade to 10g client. Although 8i client did work with 10g when it was supported, it is no longer supported.
Re: SQL Query doubt [message #274661 is a reply to message #274634] Tue, 16 October 2007 13:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
srinivas.k2005 wrote on Tue, 16 October 2007 18:53

This is really shocking for me.Will the version of oracle or any other settings matters?

This has to do with nls settings. What you do is an implicit datatype conversion.
Don't you see anything odd in your code?
to_date('JAN', 'mm)?

3 characters at the lefthand side, only 2 at the righthand side?
Letters on the lefthand side, whereas mm stands for two digits?
Re: SQL Query doubt [message #274785 is a reply to message #274303] Wed, 17 October 2007 02:34 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Frank Can you post me the solution you had, which you hinted me.
Re: SQL Query doubt [message #274788 is a reply to message #274785] Wed, 17 October 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What point don't you understand?
Where are you stuck?

Regards
Michel
Re: SQL Query doubt [message #274792 is a reply to message #274303] Wed, 17 October 2007 03:01 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Michele this was my previous post.If the to_date i used was wrong how can use the order clause so that i will get my desired output.

SQL>  select a.mon, count(b.cust_code)
  2    from ar_cust b, (SELECT to_char(to_Date(rownum,'mm'),'Mon') MON
  3    FROM ALL_OBJECTS
  4    WHERE ROWNUM <=12) A
  5   where to_char(b.create_dttm(+),'Mon') = a.mon
  6   group by a.mon;

MON COUNT(B.CUST_CODE)
--- ------------------
Feb                  1
Jun                  1
Nov                  0
Sep                  0
Jul                  1
Oct               1328
Aug                  0
Jan                  0
Dec                  0
Apr                  5
Mar                  0

MON COUNT(B.CUST_CODE)
--- ------------------
May                  0

12 rows selected.


but i want the output as:


MON COUNT(B.CUST_CODE)
--- ------------------
Jan                  0
Feb                  1
Mar                  0
Apr                  5
May                  0
Jun                  1
Jul                  1
Aug                  0
Sep                  0
Oct               1328
Nov                  0

MON COUNT(B.CUST_CODE)
--- ------------------
Dec                  0

12 rows selected.


Thanks

[Updated on: Wed, 17 October 2007 03:01] by Moderator

Report message to a moderator

Re: SQL Query doubt [message #274796 is a reply to message #274792] Wed, 17 October 2007 03:04 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, let state it step by step:
- what is the purpose of to_date function?
- what is the meaning of MM format model in to_date function?
- what is wrong in "to_date('JAN', 'mm')"?

It should be easy to answer as you use it in your inline view.

Regards
Michel
Previous Topic: Integer number type
Next Topic: commit
Goto Forum:
  


Current Time: Sun Dec 04 18:46:05 CST 2016

Total time taken to generate the page: 0.04324 seconds