We’re creating a QuickReport where we want transaction data by month going across the row.
We created an accumulating IAL grouped by YYYYMM which works great.
Then creating a 2nd IAL over the first to total each YYYYMM’s Qty into it’s own month column (January, February, March, etc) and when we run that in SQL Query Tool it fails with this error:
We’ve ensured all rows have valid dates.
Parse your code into this forum as text as well of screenshots.
I would try something like this instead…
these two fields will bring back the very beginning of the year and the very last seecond of the year
select
trunc(sysdate, 'YEAR') begin_year,
add_months(trunc(sysdate, 'YEAR'), 12)-1/24/60/60 last_second_year
from dual
so…
and OP_START_DATE between trunc(sysdate, 'YEAR') and add_months(trunc(sysdate, 'YEAR'), 12)-1/24/60/60
That worked! Thx!
Here’s the final 2 queries, followed by the results:
select
rega1app.INVENTORY_PRODUCT_FAMILY_API.GET_DESCRIPTION(PART_PRODUCT_FAMILY) as Product_Family,
rega1app.COMMODITY_GROUP_API.GET_DESCRIPTION(ip.SECOND_COMMODITY) as Second_Commodity,
cmo.PART_NO as Part,
trunc(to_char(OP_FINISH_DATE,'yyyymm')) finish_yearmonth,
revised_qty_due as Total_Qty_Due
from REGA1APP.CRP_MACH_OPERATION2 cmo
left join REGA1APP.INVENTORY_PART ip on cmo.part_no = ip.part_no and cmo.contract = ip.contract
where
operation_no = '999'
and cmo.contract in ('W', 'E22')
and OP_FINISH_DATE between trunc(sysdate, 'YEAR') and add_months(trunc(sysdate, 'YEAR'), 12)-1/24/60/60
and department = '455'
and work_center_no = '45502'
order by Product_Family, Second_Commodity, cmo.PART_NO, cmo.contract, finish_yearmonthselect
Part_Product_Family,
Second_Commodity,
Part,
sum(case when finish_yearmonth like '202001' then Total_Qty_Due else 0 end) as January,
sum(case when finish_yearmonth like '202002' then Total_Qty_Due else 0 end) as February,
sum(case when finish_yearmonth like '202003' then Total_Qty_Due else 0 end) as March,
sum(case when finish_yearmonth like '202004' then Total_Qty_Due else 0 end) as April,
sum(case when finish_yearmonth like '202005' then Total_Qty_Due else 0 end) as May,
sum(case when finish_yearmonth like '202006' then Total_Qty_Due else 0 end) as June,
sum(case when finish_yearmonth like '202007' then Total_Qty_Due else 0 end) as July,
sum(case when finish_yearmonth like '202008' then Total_Qty_Due else 0 end) as August,
sum(case when finish_yearmonth like '202009' then Total_Qty_Due else 0 end) as September,
sum(case when finish_yearmonth like '202010' then Total_Qty_Due else 0 end) as October,
sum(case when finish_yearmonth like '202011' then Total_Qty_Due else 0 end) as November,
sum(case when finish_yearmonth like '202012' then Total_Qty_Due else 0 end) as December,
sum(Total_qty_due) as Total_Issues
from ifsinfo.RW_PART_OPER_SUM_ACCUM
group by Part_Product_Family, Second_Commodity, Part 
Glad to have helped.
Anything else you know where I am
You might want to modify your quick report SQL statement as you are hard-coding the 2020 values. So in 11 months time it will need changing again - which will no doubt be forgotten about!
Since your IAL only runs for the current year you could just use a substring?
sum(case when substr(finish_yearmonth,5,2) ='01' then Total_Qty_Due else 0 end) as January,Otherwise if someone runs the report next year they will see 0 in every month and then just one total value in the Part Total Column
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.