SQL Query Manager ++ has functionality to convert Oracle SQL syntax to 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.

Select 

case 

when substr(a.T$ORNO,1,2)= ‘NO’  then ‘NOK’

when substr(a.T$ORNO,1,2)= ‘RO’  then ‘RON’

when substr(a.T$ORNO,1,2)= ‘PL’   then ‘PLN’

else ‘EUR’

end

as «Curr Home»,

‘SLS.’ || substr(a.T$ORNO,1,4) as «Serie ID»,

TO_CHAR(a.T$ODAT, ‘IW’) «Order Week»,

to_char(c.T$DDAT,’iw‘) as «Promised Week», to_char(c.T$DDAT,’yyyy‘) as «Promised Year»,

to_char(a.T$DLDT,’iw‘) as «Delivery Week», to_char(a.T$DLDT,’yyyy‘) as «Delivery Year»,

to_char(a.T$ODAT,’dd.mm.yyyy‘) as «Order Date», t$hdst as «Order Status», a.T$OFBP as «Customer ID»,  c.T$RUSO as «Rush Order»,

a.T$QOOR as «Order Qty», to_char(a.T$DLDT,’dd.mm.yyyy‘) as «Delivery Date», to_char(a.T$DDTA,’dd.mm.yyyy‘) as «Planned Delivery Date»,

to_char(a.T$INVD,’dd.mm.yyyy‘) as «Invoice Date», a.T$ORNO as «Order No», a.T$PONO as «Pos No», a.T$CPRJ || ‘ ‘  as «Project», a.T$ITEM as «Item»,

a.T$CWAR as «Warehouse», a.T$PRIC as Price, Round(a.T$COPR$1,2) as «Cost Price», a.T$DISC$1 as «Line Discount»,

a.T$RATS$1 «Currency Rate», a.T$RATF$1 as «Rate Factor», a.T$RATS$2 «Currency Rate 2»,

a.T$QIDL as Delivered, a.T$QBBO as «Back Order Qty», a.T$OLTP «Order Line Type», a.T$CUQS as «Sales Unit», c.T$CCUR as «Currency»,

to_char(c.T$DDAT,’dd.mm.yyyy‘) as «Promised Dely Date», to_char(c.T$ODAT,’dd.mm.yyyy‘) as «Order Date Head», c.T$CREP as «Sales Representative»,

a.T$TTYP «Trans Type», a.T$INVN «Invoice No»,

a.T$TTYP || to_char(a.T$INVN) as «InvID«, a.T$SCMP as «Invoicing Comp», Round(a.T$OAMT,2) as «Order Amnt«, Round(a.T$LDAM$1,2) as «Discount Amount»,

Round(a.T$OAMT * a.T$RATS$1,2)  as «Order Amnt HC»,

case 

when a.T$RATS$2< 0.1 then Round(a.T$OAMT/a.t$rats$1,2)

else

Round(a.T$OAMT/a.T$RATS$2*a.T$RATS$1,2)

end

 as «Order Amnt HC old»

, c.T$CPAY as «TOP»,  trunc(c.T$DDAT – a.T$DLDT) as «Status In Days»,

decode(to_char(a.t$INVD,’yyyy‘),’1970′,’20991231’, to_char(a.T$INVD,’yyyyMMdd‘)) as «InvDateNum«,

 ibd.t$kitm as kitm,  ibd.t$dsca «Item descr«,

c.T$CORN «Customer Order», c.T$COFC «Sls Office»

FROM erpln6a.ttdsls401200 a, erpln6a.ttdsls400200 c, erpln6a.ttcibd001200 ibd 

WHERE (a.T$ORNO=c.T$ORNO) AND

a.t$Item=ibd.t$item and

a.T$SQNB=(Select min(a2.T$SQNB) from erpln6a.ttdsls401200 a2 where a2.T$ORNO=a.T$ORNO and a2.T$PONO=a.T$PONO)

AND c.T$HDST Not IN (2,35,40)  AND a.t$clyn=2

and a.T$ORNO=’PL1000072′

The Converted SQL looks like this:

SELECT

 Case

 When substring(a.t_orno,1,2)=’NO’ then ‘NOK’

 When substring(a.t_orno,1,2)=’RO’ then ‘RON’

 When substring(a.t_orno,1,2)=’PL’ then ‘PLN’

 Else

‘EUR’

 End

«curr home», ‘sls.’ + substring(a.t_orno,1,4) «serie id», CONVERT(VARCHAR(3), a.t_odat) «order week», CONVERT(VARCHAR, DatePart(ww, c.t_ddat)) «promised week», CONVERT(VARCHAR, DatePart(Year, c.t_ddat)) «promised year», CONVERT(VARCHAR, DatePart(ww, a.t_dldt)) «delivery week», CONVERT(VARCHAR, DatePart(Year, a.t_dldt)) «delivery year», CONVERT(VARCHAR(10), a.t_odat, 104) «order date«, t_hdst «order status», a.t_ofbp «customer id», c.t_ruso «rush order», a.t_qoor «order qty», CONVERT(VARCHAR(10), a.t_dldt, 104) «delivery date», CONVERT(VARCHAR(10), a.t_ddta, 104) «planned delivery date», CONVERT(VARCHAR(10), a.t_invd, 104) «invoice date», a.t_orno «order no», a.t_pono «pos no», a.t_cprj + ‘ ‘ «project», a.t_item «item», a.t_cwar «warehouse», a.t_pric price, round(a.t_copr_1,2) «cost price», a.t_disc_1 «line discount», a.t_rats_1 «currency rate», a.t_ratf_1 «rate factor», a.t_rats_2 «currency rate 2», a.t_qidl delivered, a.t_qbbo «back order qty», a.t_oltp «order line type», a.t_cuqs «sales unit», c.t_ccur «currency», CONVERT(VARCHAR(10), c.t_ddat, 104) «promised dely date», CONVERT(VARCHAR(10), c.t_odat, 104) «order date head», c.t_crep «sales representative», a.t_ttyp «trans type», a.t_invn «invoice no», a.t_ttyp + CONVERT(VARCHAR, a.t_invn) «invid«, a.t_scmp «invoicing comp», round(a.t_oamt,2) «order amnt«, round(a.t_ldam_1,2) «discount amount», round(a.t_oamt * a.t_rats_1,2) «order amnt hc«,

 Case

 When a.t_rats_2< 0.1 then round(a.t_oamt/a.t_rats_1,2)

 Else

round(a.t_oamt/a.t_rats_2*a.t_rats_1,2)

 End

«order amnt hc old» , c.t_cpay «top», DATEDIFF(day,a.t_dldt,c.t_ddat) «status in days»,

 Case CONVERT(VARCHAR, DatePart(Year, a.t_invd))

 When ‘1970’ then ‘20991231’

 Else

 CONVERT(VARCHAR(8), a.t_invd, 112)

 End

 «invdatenum«, ibd.t_kitm kitm, ibd.t_dsca «item descr«, c.t_corn «customer order», c.t_cofc «sls office»

 FROM ttdsls401200 a INNER JOIN ttdsls400200 c ON a.t_orno=c.t_orno INNER JOIN ttcibd001200 ibd ON a.t_item=ibd.t_item

 WHERE a.t_sqnb=(SELECT min(a2.t_sqnb)

 FROM  ttdsls401200 a2

 WHERE a2.t_orno=a.t_orno and a2.t_pono=a.t_pono) and c.t_hdst not in (2,35,40) and a.t_clyn=2 and a.t_orno=’PL1000072′

Below you see the converted SQL is also verified and executed with SQL Query Manager ++