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 Go to next message
manish.nankar
Messages: 8
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 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: data to excel
Next Topic: Not Null then Pass Value
Goto Forum:
  


Current Time: Sun Dec 11 06:26:35 CST 2016

Total time taken to generate the page: 0.13469 seconds