September 28, 2021 at 10:31 am
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
September 28, 2021 at 1:38 pm
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/
September 28, 2021 at 5:55 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply