ORA-01843: not a valid month

User_3KSKY

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

Frank Kulash

Hi, @User_3KSKY

Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

Simplify the problem. Eliminate all tables and columns that have nothing to do with the issue.

Never write, let alone post, unformatted code.

Frank Kulash

Hi, @User_3KSKY

"ORA-01843: not a valid month" is caused by a bad DATE or TIMESTAMP conversion, including implicit conversions (where you use some other data type where a DATE or TIMESTAMP is required). Check all the places where you are using DATEs or TIMESTAMPs. For example:

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

is a mistake. The first argument to TO_TIMESTAMP is supposed to be a string, but you're passing a DATE instead. (The value returned by TRUNC is a DATE.) Also, don't call TO_TIMESTAMP with only one argument; explicitly give a second argument, because the default may not be what you want or expect.