SQL GroupBy YYYYMM over IAL fails with '(full) year must be netween -4713 and +9999 and not be 0'

@DevBob

Parse your code into this forum as text as well of screenshots. 

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_yearmonth

select 
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