Home » SQL & PL/SQL » SQL & PL/SQL » query to give all months
query to give all months [message #397489] |
Sat, 11 April 2009 05:41  |
manish.nankar
Messages: 10 Registered: December 2008
|
Junior Member |
|
|
hello friends,
i have come across a situation where this query should return all months but its returning only few months.
i have tried out join but it doesnt't work.can anyone help me.
you can also post solution on my mail id->manish.nankar@gmail.com
please refer to following query.
SELECT PRDN_JOBNO JOBNO,MON1,SUM(PRDN_MFPQTY) PREP_QTY,SUM(PRDN_SBQTY) FAB_QTY,mnth,PREP,FAB,BLASTING,GALV,SHIPPING
FROM JOB_DAILY_PRDN prdn,(select to_char(to_date('01-jan-2003'),'MON')mon1,to_char(to_date('01-jan-2003'),'Mm') mnth,
'PREP' PREP,'FAB' FAB,'BLASTING' BLASTING,'GALV' GALV,'SHIPPING' SHIPPING
from dual
union ALL
select to_char(to_date(nextmonth),'MON')mon1,to_char(to_date(nextmonth),'Mm') mnth,
'PREP' PREP,'FAB' FAB,'BLASTING' BLASTING,'GALV' GALV,'SHIPPING' SHIPPING
from
(
SELECT TO_CHAR( ADD_MONTHS('01-JAN-2003',LEVEL),'DD-MON-YYYY') Nextmonth
FROM DUAL
CONNECT BY LEVEL >= MONTHS_BETWEEN(TO_DATE('01-JAN-2003'),TO_DATE('01-dec-2003'))+1
)where rownum <=11) mnt
WHERE PRDN_JOBNO LIKE 'SS06360611%'
AND TO_CHAR(PRDN_DATE,'YYYYMM') (+)='2003'||mnth
AND PRDN_PMNO NOT LIKE 'B%'
GROUP BY PRDN_JOBNO,mnth,PREP,FAB,BLASTING,GALV,SHIPPING,MON1,TO_CHAR(PRDN_DATE,'MM')
ORDER BY MNTH ASC
|
|
|
Re: query to give all months [message #397494 is a reply to message #397489] |
Sat, 11 April 2009 09:36  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Post a working Test case: create table and insert statements along with the result you want with these data.
Your query returns nothing for me:
SQL> select to_date('01-jan-2003') from dual;
select to_date('01-jan-2003') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Regards
Michel
|
|
|
Goto Forum:
Current Time: Fri Feb 07 16:07:09 CST 2025
|