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