hi all i have the following formula, it is giving me 70% of the result i want would you please check where the mistake might be?
the formula is :
SELECT TO_CHAR(purinv.Inv_Date,'mm') MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR, (case when purch.Prod_Code = 'SP-20.2' then sum(purch.qty) else 0 end) as "SP-20.2", (case when purch.Prod_Code = 'SP-20.1' then sum(purch.qty) else 0 end) as "SP-20.1", (case when purch.Prod_Code = 'SP-20.6' then sum(purch.qty) else 0end) as "SP-20.6" from purinv, purch where purinv.Code = purch.Inv_Code and purch.Prod_Code in ('SP-20.2', 'SP-20.1', 'SP-20.6') group by TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') ,purch.Prod_Code order by TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') *************************************************************************** ******************************** the result i am getting:
MO YEAR SP-20.2 SP-20.1 SP-20.6 -- ---- ---------- ---------- ---------- 01 1998 0 140 0 01 1998 400 0 0 01 1998 0 0 80 01 1999 0 0 89 02 1999 0 163 0 02 1999 456 0 0 02 1999 0 0 86 03 1998 0 0 99 03 1999 0 0 81 04 1998 0 163 0 *************************************************************************** ****************************** the result i want : is to gather all 1998 for sp.20.2 lets say in one, so on so forth , what shoul be changed in the formula please?
the result i want is like such: first everything in 1998, then 1999
> hi all i have the following formula, it is giving me 70% of the result > i want would you please check where the mistake might be?
> the formula is :
> SELECT TO_CHAR(purinv.Inv_Date,'mm') > MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR, > (case when purch.Prod_Code = 'SP-20.2' then sum(purch.qty) else 0 end) > as "SP-20.2", > (case when purch.Prod_Code = 'SP-20.1' then sum(purch.qty) else 0 end) > as "SP-20.1", > (case when purch.Prod_Code = 'SP-20.6' then sum(purch.qty) else 0end) > as "SP-20.6" > from purinv, purch > where purinv.Code = purch.Inv_Code and purch.Prod_Code in ('SP-20.2', > 'SP-20.1', 'SP-20.6') > group by > TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') ,purch.Prod_Code > order by > TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') > *************************************************************************** ******************************** > the result i am getting:
> MO YEAR SP-20.2 SP-20.1 SP-20.6 > -- ---- ---------- ---------- ---------- > 01 1998 0 140 0 > 01 1998 400 0 0 > 01 1998 0 0 80 > 01 1999 0 0 89 > 02 1999 0 163 0 > 02 1999 456 0 0 > 02 1999 0 0 86 > 03 1998 0 0 99 > 03 1999 0 0 81 > 04 1998 0 163 0 > *************************************************************************** ****************************** > the result i want : > is to gather all 1998 for sp.20.2 lets say in one, so on so forth , > what shoul be changed in the formula please?
> the result i want is like such: > first everything in 1998, then 1999
you were right Mr. Hooper , one thing more please, the above formula as i edited it the way you told me, does the just that for let's say :
02 1998 0 0 0
this column and every column that has 0 products purchased does not show, while i want it to show i want the formula to show months from 1 -> 12, with the year and the num. of purchased products, even if it is 0, is that possible?
On Jan 4, 8:56 am, Totti <saliba.toufic.geo...@gmail.com> wrote:
> you were right Mr. Hooper , > one thing more please, the above formula as i edited it the way you > told me, does the just that for let's say :
> 02 1998 0 0 0
> this column and every column that has 0 products purchased does not > show, while i want it to show i want the formula to show months from 1 > -> 12, with the year and the num. of purchased products, even if it is > 0, is that possible?
You need to find some way of including rows that do not exist in your two tables. To do this, you probably need to perform a left outer join between a list of all months that should be included, and the current query result that you are receiving. For example: SELECT * FROM (SELECT ADD_MONTHS(TO_DATE('01/01/1998','MM/DD/YYYY'),ROWNUM-1) MY_DATE, ROWNUM COUNTER FROM DUAL CONNECT BY LEVEL<=24) C;
If you place your current query in an inline view and join it to the SQL statement above using a LEFT outer join, you will be able to include all months within the above range
For axample, assume that you are not performing TO_CHAR(purinv.Inv_Date,'mm') and TO_CHAR(purinv.Inv_Date,'YYYY') in your current query, but are instead returing the value of purinv.Inv_Date, and you alias your current query as MQ (short for my query). The final query might be formatted like this: SELECT TO_CHAR(C.MY_DATE,'YYYY') MONTH, TO_CHAR(C.MY_DATE,'YYYY') YEAR, ... (other columns of interest here) FROM (SELECT ADD_MONTHS(TO_DATE('01/01/1998','MM/DD/YYYY'),ROWNUM-1) MY_DATE, ROWNUM COUNTER FROM DUAL CONNECT BY LEVEL<=24) C, (... your current query here) MQ WHERE C.MY_DATE=MQ.INV_DATE(+);
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
> hi all i have the following formula, it is giving me 70% of the result > i want would you please check where the mistake might be?
> the formula is :
> SELECT TO_CHAR(purinv.Inv_Date,'mm') > MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR, > (case when purch.Prod_Code = 'SP-20.2' then sum(purch.qty) else 0 end) > as "SP-20.2", > (case when purch.Prod_Code = 'SP-20.1' then sum(purch.qty) else 0 end) > as "SP-20.1", > (case when purch.Prod_Code = 'SP-20.6' then sum(purch.qty) else 0end) > as "SP-20.6" > from purinv, purch > where purinv.Code = purch.Inv_Code and purch.Prod_Code in ('SP-20.2', > 'SP-20.1', 'SP-20.6') > group by > TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') ,purch.Prod_Code > order by > TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') > *************************************************************************** ******************************** > the result i am getting:
> MO YEAR SP-20.2 SP-20.1 SP-20.6 > -- ---- ---------- ---------- ---------- > 01 1998 0 140 0 > 01 1998 400 0 0 > 01 1998 0 0 80 > 01 1999 0 0 89 > 02 1999 0 163 0 > 02 1999 456 0 0 > 02 1999 0 0 86 > 03 1998 0 0 99 > 03 1999 0 0 81 > 04 1998 0 163 0 > *************************************************************************** ****************************** > the result i want : > is to gather all 1998 for sp.20.2 lets say in one, so on so forth , > what shoul be changed in the formula please?
> the result i want is like such: > first everything in 1998, then 1999
