Oracle: ORA-01843: not a valid month

  • Hi

    I have the following query which works fine in Oracle SQL Developer. When I try to use it in Microsoft PBI I get the following error "ORA-01843: not a valid month".

    Does anyone have any ideas on why?

    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

  • Well, considering that PL-SQL and T-SQL are different especially from a perspective of built-in functions, this will not work.

    I copied and pasted this code into SSMS, and checked the syntax. Here are the errors:

    Msg 195, Level 15, State 10, Line 11

    'substr' is not a recognized built-in function name.

    Msg 102, Level 15, State 1, Line 49

    Incorrect syntax near ','.

    Msg 195, Level 15, State 10, Line 53

    'to_char' is not a recognized built-in function name.

    Msg 102, Level 15, State 1, Line 55

    Incorrect syntax near 'sdt'.

    Msg 102, Level 15, State 1, Line 57

    Incorrect syntax near 'shift'.

    Msg 102, Level 15, State 1, Line 71

    Incorrect syntax near 'line'.

    Msg 102, Level 15, State 1, Line 121

    Incorrect syntax near ','.

    Msg 156, Level 15, State 1, Line 127

    Incorrect syntax near the keyword 'from'.

    Msg 102, Level 15, State 1, Line 127

    Incorrect syntax near 'sdt'.

    Msg 102, Level 15, State 1, Line 129

    Incorrect syntax near 'TEAM_MANAGER'.

    Msg 102, Level 15, State 1, Line 131

    Incorrect syntax near 'EMPLOYER'.

    Msg 195, Level 15, State 10, Line 149

    'to_char' is not a recognized built-in function name.

    Msg 102, Level 15, State 1, Line 203

    Incorrect syntax near 'TIME_QUIV'.

     

    subst in PL-SQL is SUBSTRING in T-SQL

    to_char would be CAST OR CONVERT in T-SQL

    These did not show as an error, but these are also specific to PL-SQL:

    extract(hour from(d))

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

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • You might get better answers if you made it more clear what your circumstances are and what you want to achieve. After all you posted your question in a forum that is dedicated to two ancient versions of SQL Server, i.e. SQL Server versions 7 and 2000 from the previous millenium. 🙂

    I'm assuming this isn't really related to those versions of SQL Server, but rather that you didn't find a forum with a better fit.

    Could you please clarify, if this is about a query you want to send from a Microsoft Power BI client to an Oracle database server?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply