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