Convert Oracle SQL syntax to MS SQL syntax – Part 2
SQL Query Manager ++ has functionality to convert Oracle SQL syntax to Ms SQL server SQL syntax.
If looking at the SQL statement below. This statement contains several parts that needs to be converted to be able to run as a SQL server syntax.
Some to mention is the functions substr, TO_CHAR, string concatenation ||, decode that is converted to case. And the Oracle Joining with =, =+ and += which is converted to Inner, Outer, Left Outer and Right outer Joins. SQL Query Manager ++ handles to convert all these differences and a lot of more complex queries.
create or replace view My_MaterialShortPicked as
Select a.T$PDNO «Prod Order», to_char(pr.T$CMDT,’yyyymmdd’) as CMDT_num, decode( extract (year from pr.T$CMDT ), ‘1970’ , ‘No’ , ‘Yes’ ) as «Completed», to_char(pr.T$CMDT,’dd.mm.yyyy’) as CMDT, a.T$PONO «Pos», a.T$OPNO «Operation», a.T$SITM «Item», a.T$REVI «Rev»,
c.T$DSCA «Item Descr», c.T$SEAK «SK1», c.T$SEAB «SK2»,
Case
when c.T$KITM=’1′ then ‘Pur’
when c.T$KITM=’2′ then ‘Manuf’
else
cast(c.T$KITM as CHAR)
end
as «Item Type»,
(select case
when i2.T$KITM=’5′ then ‘Pur’
when i2.T$KITM=’10’ then ‘Manuf’ end
from erpln6a.ttcibd010200 i2 where a.T$SITM=i2.T$ITEM and i2.T$EXDT > sysdate and i2.T$EFDT < sysdate ) as «Act Item Type»,
c.T$CITG as «Item Group»,
to_char(a.T$ALDT,’dd.mm.yyyy’) «Allocated», a.T$ISSU «Qty To Issue», a.T$NOUN «No Of Units», a.T$QUCS «Actual Qty», a.T$QUES «Estimated Qty», a.T$QUNE «Net Qty»,
b.T$CWAR WH, b.T$CUNI «Unit», b.T$QADS «Adv Qty Storage Unit», b.T$QADV «Adv Qty Inv Unit», decode(b.T$RLSD,1,’Yes’,’No’) «Released», b.T$PSEQ «Picking Seq»,
to_char(b.T$TIME,’dd.mm.yyyy’) «Pick Time»,
decode(b.T$PCKD,1,’Yes’,’No’) «Picked»,
pr.T$CWOC as WC,
case
when pr.t$opst=1 then ‘Planned’
when pr.t$opst=3 then ‘Ready’
when pr.t$opst=4 then ‘Started’
when pr.t$opst=5 then ‘Active’
when pr.t$opst=6 then ‘Blocked’
when pr.t$opst=7 then ‘Completed’
when pr.t$opst=8 then ‘Closed’
end as «Op status», pr.t$opst as «opst»,
case
when trunc(b.T$TIME)<=trunc(a.T$ALDT) then’True’
else ‘False’
end as «In Time»
FROM erpln6a.tticst001200 a, erpln6a.twhinh225200 b, erpln6a.ttcibd001200 c, erpln6a.ttisfc010200 pr
where a.T$PDNO= pr.T$PDNO and a.T$OPNO= pr.T$OPNO and
a.T$PDNO=b.T$ORNO(+) and a.T$PONO=b.T$PONO(+) and a.T$SITM=b.T$ITEM(+)
and a.T$SITM=c.T$ITEM and c.T$CITG not in (‘991400′,’991200’) and a.T$BFLS=2
order by a.T$PDNO, a.T$PONO
with read only
The Converted SQL looks like this:
create view my_materialshortpicked as
SELECT a.t_pdno [prod order], CONVERT(VARCHAR(8), pr.t_cmdt, 112) cmdt_num,
Case DATEPART(year, pr.t_cmdt)
When ‘1970’ then ‘no’
Else
‘yes’
End
[completed], CONVERT(VARCHAR(10), pr.t_cmdt, 104) cmdt, a.t_pono [pos], a.t_opno [operation], a.t_sitm [item], a.t_revi [rev], c.t_dsca [item descr], c.t_seak [sk1], c.t_seab [sk2],
Case
When c.t_kitm=’1′ then ‘Pur’
When c.t_kitm=’2′ then ‘Manuf’
Else
cast(c.t_kitm as char)
End
[item type],
(SELECT
Case
When i2.t_kitm=’5′ then ‘Pur’
When i2.t_kitm=’10’ then ‘Manuf’ end
FROM ttcibd010200 i2
WHERE a.t_sitm=i2.t_item and i2.t_exdt > getdate() and i2.t_efdt < getdate()) [act item type],
c.t_citg [item group], CONVERT(VARCHAR(10), a.t_aldt, 104) [allocated], a.t_issu [qty to issue], a.t_noun [no of units], a.t_qucs [actual qty], a.t_ques [estimated qty], a.t_qune [net qty], b.t_cwar wh, b.t_cuni [unit], b.t_qads [adv qty storage unit], b.t_qadv [adv qty inv unit],
Case b.t_rlsd
When 1 then ‘yes’
Else
‘no’
End
[released], b.t_pseq [picking seq],
CONVERT(VARCHAR(10), b.t_time, 104) [pick time],
Case b.t_pckd
When 1 then ‘yes’
Else
‘no’
End
[picked],
pr.t_cwoc wc,
Case
When pr.t_opst=1 then ‘Planned’
When pr.t_opst=3 then ‘Ready’
When pr.t_opst=4 then ‘Started’
When pr.t_opst=5 then ‘Active’
When pr.t_opst=6 then ‘Blocked’
When pr.t_opst=7 then ‘Completed’
When pr.t_opst=8 then ‘Closed’
End
[op status], pr.t_opst [opst],
Case
When Cast(b.t_time as Date)<=Cast(a.t_aldt as Date) then ‘True’
Else
‘False’
End
[in time]
FROM tticst001200 a INNER JOIN ttisfc010200 pr ON a.t_pdno=pr.t_pdno AND a.t_opno=pr.t_opno LEFT OUTER JOIN twhinh225200 b ON a.t_pdno=b.t_orno AND a.t_sitm=b.t_item AND a.t_pono=b.t_pono INNER JOIN ttcibd001200 c ON a.t_sitm=c.t_item
WHERE c.t_citg not in (‘991400′,’991200’) and a.t_bfls=2
![](https://queryworkspace.com/wp/wp-content/uploads/2022/11/ora_to_sql2.png)