ORA-01843: not a valid month

Hi

I have the following query (119 lines long) which runs fine in Oracle SQL Developer and give me exactly what I need.

When I then try to use is in Microsoft PBI I get the following error "ORA-01843: not a valid month" The stage thing is there is month in the query.

Any ideas?

Thanks in advance.

with a as

(select

user_id,dstamp d,code c,

from_loc_id f

,to_loc_id t,sku_id,container_id cid,notes,update_qty u,v_order_sub_type_1

,v_order_type,substr(from_loc_id,0,6)||''||floor(to_char(dstamp,'sssss.ff')/300)ac

from dcsdba.inventory_transaction itl

where

dstamp >= to_timestamp(trunc(SYSDATE-(1/24)))

and

((from_loc_id like'PAC%'

and from_loc_id not like'PACKDAMAGE%'

and notes not like'4%'

and to_loc_id not like'ECOMIN'

and to_loc_id not like'SOROUT%'

and from_loc_id not like'PACPRO%'

and(container_id like'4%'or container_id like'PARK%'or container_id like'OFL%')

and to_loc_id not like'SYWSTG%')

or (from_loc_id like '%BULK%' and code ='Pick' and to_loc_id='CONTAINER')

)

)

,

pb as

(select user_id,F,row_number()over(partition by user_id order by d desc)row_check from a)

,

s_1 as

(select user_id,d,c,f,t,cid,notes,to_char(d,'HH24')as hrm,

case when extract(hour from d)+extract(minute from d)/60 <6.5 then trunc(d)-1 else trunc(d)end sdt,

case when extract(hour from(d))+extract(minute from(d))/60 >=18.5 or extract(hour from(d))+extract(minute from(d))/60 <6.5 then'Nights'else'Days'end shift,ac

,case when f in('PACKC3051','PACKC3052','PACKC3053','PACKC3054','PACKC3055','PACKC3061','PACKC3062','PACKC3063','PACKC3064','PACKC3065','PACKC3066','PACKC3067','PACKC3068','PACKC3069','PACKC3070')

then'PACKUG'

when substr(f,1,6)='PACKC3'then'PACKC3'

when substr(f,1,6)='C1BULK'then'PACKBL'

when substr(f,1,8)='PACKBA1A'then'PACKBA1A'

when substr(f,1,8)='PACKBA1H'then'PACKBA1H'

else substr(f,1,6)end line

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%' or (f like'%BULK%' and c='Pick')))and cid like'4%'then u end),0)ps

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'4%'and(v_order_sub_type_1='INTERNATIONAL'or v_order_type='DEMANDWARE')then u end),0)itpk

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'4%'and substr(a.notes,1,1)in('1','2')then u end),0)pfb

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'4%'and substr(a.notes,1,1)in('9')then u end),0)pfh

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'4%'and substr(notes,1,4)in('PARK')then u end),0)pfp

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'4%'and substr(a.notes,1,1)in('O')then u end),0)pfo

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'4%'and substr(a.notes,1,1)in('M')then u end),0)pfm

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'4%'and substr(a.notes,1,1)not in('O','P','1','2','9','M')then u end),0)pfot

,nvl(sum(case when(c='Repack'or(c='Pick'and(substr(f,1,6)='PACKC3'and t like'C3UGOUT%')or f like'PACKMZ%' or f like'PACKTH%'))and cid like'PARK%'then u end),0)psg

,nvl(sum(case when c='Off-line'and(cid like'OFL%'or cid like'INV%')then u end),0)ods

from a

GROUP BY

user_id,d,c,f,t,cid,notes,case when extract(hour from d)+ extract(minute from d)/60 <6.5 then trunc(d)-1 else trunc(d)end,

case when extract(hour from(d))+ extract(minute from(d))/60 >=18.5 or extract(hour from(d))+

extract(minute from(d))/60 <6.5 then'Nights'else'Days'end ,ac,

case when f in('PACKC3051','PACKC3052','PACKC3053','PACKC3054','PACKC3055','PACKC3061','PACKC3062','PACKC3063','PACKC3064','PACKC3065','PACKC3066','PACKC3067','PACKC3068','PACKC3069','PACKC3070')

then'PACKUG' when substr(f,1,6)='PACKC3'then'PACKC3' else  substr(f,1,6)end

)

,s2 as

(select s1.*,t2.name,t2.address1,t2.address2

from s_1 s1

left join(select user_id ,address1,address2,name from dcsdba.application_user)t2 on s1.user_id=t2.user_id

where s1.user_id in(select user_id from dcsdba.application_user)

)

,

s3 as

(select

case when extract(hour from D)+ extract(minute from D)/60 <6.5 then to_char(trunc(D)-1,'yyyy-mm-dd')else to_char(trunc(d),'yyyy-mm-dd')end sdt

,SHIFT,USER_ID,NAME,hrm,LINE,case when ADDRESS1 is null then'1 -Agency'else ADDRESS1 end TEAM_MANAGER

,case when ADDRESS1 is null then'1 -Agency'else'MandS'end EMPLOYER,PS AS SIN,MIN(D)over(partition by SHIFT,USER_ID,NAME,hrm,LINE)as FIRST_TASK

,to_char(D,'dd/mm/yyyy HH24:')||'00:00'as RECORD_TIME

,MAX(D)over(partition by SHIFT,USER_ID,NAME,hrm,LINE)as LAST_TASK,CID,itpk,ac,pfb,pfh,pfp,pfo,pfm,pfot,psg,ods

from s2

)

,

s4 as

(select sdt,SHIFT,USER_ID,NAME,LINE,hrm,TEAM_MANAGER,EMPLOYER,case when sum(count(distinct ac)*5)over(partition by sdt,SHIFT,hrm,USER_ID)>60 then

count(distinct ac)*5 -(sum(count(distinct ac)*5)over(partition by sdt,SHIFT,hrm,USER_ID)-60)/ count(hrm)over(partition by sdt,SHIFT,hrm,USER_ID)

else count(distinct ac)*5 end ACTIVE_TIME,SUM(SIN)AS SINGLES_ON_LINE,to_char(MIN(FIRST_TASK),'HH24:MI:SS')AS FIRST_TASK,to_char(MIN(LAST_TASK),'HH24:MI:SS')AS LAST_TASK

,COUNT(DISTINCT CID)AS TOTAL_PARCELS

,SUM(itpk)AS INTERNATIONAL_SINGLES

,SUM(pfb)AS PACKED_FROM_BOX

,SUM(pfh)AS PACKED_FROM_HANG

,SUM(pfp)AS PACKED_FROM_PARK

,SUM(pfo)AS PACKED_FROM_OFFLINE

,SUM(pfm)AS PACKED_FROM_MMWB

,SUM(pfot)AS PACKED_FROM_OTHER

,SUM(psg)AS PARKED_SINGLES

,SUM(ods)AS OFFLINE_SINGLES

,RECORD_TIME

,CASE WHEN LINE='PACKHB'then round(SUM(SIN)/61,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKHC'then round(SUM(SIN)/61,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKMB'then round(SUM(SIN)/101,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKMC'then round(SUM(SIN)/91,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKBB'then round(SUM(SIN)/136,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKBC'then round(SUM(SIN)/96,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKUG'then round(SUM(SIN)/30,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKC3'then round(SUM(SIN)/30,2)*60

WHEN LINE='PACKC1'then round(SUM(SIN)/30,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKMZ'then round(SUM(SIN)/85,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKMA'then round(SUM(SIN)/56,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKTH'then round(SUM(SIN)/30,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKBA1A'then round(SUM(SIN)/224,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKBA1H'then round(SUM(SIN)/139,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

WHEN LINE='PACKBL'then round(SUM(SIN)/400,2)*case when(hrm=6 or hrm =18)then 30 else 60 end

else 0 end TIME_QUIV

from s3

group by sdt,SHIFT,USER_ID,NAME,LINE,hrm,RECORD_TIME,TEAM_MANAGER,EMPLOYER

)

select

sdt as SHIFTDATE

,SHIFT,s4.USER_ID,NAME,LINE,F LAST_BENCH,hrm as HOURNUM,TEAM_MANAGER,EMPLOYER

,case when hrm in('06','18') and line <> 'PACKC3' then ACTIVE_TIME /2 else ACTIVE_TIME end active_time

,case when hrm in('06','18') and line <> 'PACKC3' then case when count(hrm)over(partition by s4.user_id,sdt,shift,hrm)=1 then 60 -active_time

else(60 -sum(active_time)over(partition by s4.user_id,sdt,shift,hrm))/count(hrm)over(partition by s4.user_id,sdt,shift,hrm)

end /2 else

case when count(hrm)over(partition by s4.user_id,sdt,shift,hrm)=1 then 60 -active_time

else(60 -sum(active_time)over(partition by s4.user_id,sdt,shift,hrm))/count(hrm)over(partition by s4.user_id,sdt,shift,hrm)

end end idletime

,SINGLES_ON_LINE,FIRST_TASK,LAST_TASK,TOTAL_PARCELS,INTERNATIONAL_SINGLES,PACKED_FROM_BOX,PACKED_FROM_HANG,PACKED_FROM_PARK,PACKED_FROM_OFFLINE

,PACKED_FROM_MMWB,PACKED_FROM_OTHER,PARKED_SINGLES,OFFLINE_SINGLES,RECORD_TIME,TIME_QUIV

from s4

left join(select*from pb where row_check =1)x on s4.user_id=x.user_id