T-SQL to PL/SQL

[GHolden]

Hi, I am a SQL Server developer. I have the following T-SQL script that needs to be converted to run on Oracle. I don't have access to an Oracle system and have no experience of PL/SQL.

I would be grateful if anyone could highlight any other things that look like they won't work in Oracle (I don't expect anyone to rewrite it just but if anything looks glaringly wrong would be handy to know), as I need to send this code untested (and will get aggro if it doesn't work first time).

I know that I will have to change the ISNULL(x,y) to NVL(x,y).

Any help much appreciated.

SELECT 
	PROJ.NAME AS PROJECTNAME,
	CI.PROJECTNUMBER,
	CI.CATEGORYCODE,
	ISNULL(CI.VALUE_NON_VAT,0) AUTHORISATION,
	ISNULL(O.NETORDERVALUE,0) ORDERS,
	ISNULL(P.NETPAYMENTS,0) ORDERSPAID,
	ISNULL((O.NETORDERVALUE - P.NETPAYMENTS),0) AS ORDERSOUTSTANDING,
	ISNULL((CI.VALUE_NON_VAT - O.NETORDERVALUE),0) AS BALANCEOFBUDGET 
FROM
	IMPPROJECTS PROJ
	LEFT JOIN
	(SELECT
		PROJECTNUMBER,
		CATEGORYCODE,
		SUM (VALUE_NON_VAT) VALUE_NON_VAT
		FROM
		(
			SELECT 
				PROJECTNUMBER,
				CATEGORYCODE,
				ISNULL(VALUE_NON_VAT,0) VALUE_NON_VAT
			FROM IMPCONTRACTITEMS
			UNION ALL
			SELECT 
				PROJECTNUMBER,
				CATEGORYCODE,
				ISNULL(VALUE_NON_VAT,0) VALUE_NON_VAT
			FROM IMPSUBCONTRACTITEMS
		)AS ICI 
		GROUP BY ICI.PROJECTNUMBER, ICI.CATEGORYCODE
	) AS CI
	ON PROJ.PROJECTNUMBER = CI.PROJECTNUMBER
	LEFT JOIN
	(
		SELECT 
			PROJECTNUMBER,
			CATEGORYCODE,
			SUM(ISNULL(NETTVALUE,0)) AS NETORDERVALUE
		FROM IMPORDERS
		GROUP BY 
			PROJECTNUMBER,
			CATEGORYCODE
	) AS O
	ON CI.PROJECTNUMBER = O.PROJECTNUMBER
		AND CI.CATEGORYCODE = O.CATEGORYCODE
	LEFT JOIN
	(
		SELECT 
			PAY.PROJECTNUMBER,
			O.CATEGORYCODE,
			SUM(ISNULL(PAY.NETTVALUATION,0)) AS NETPAYMENTS
		FROM 
			IMPPAYMENTS PAY
				LEFT JOIN IMPORDERS O
					ON PAY.ORDERNO = O.ORDERNO
		GROUP BY
			PAY.PROJECTNUMBER,
			O.CATEGORYCODE
	) AS P
	ON CI.PROJECTNUMBER = P.PROJECTNUMBER
		AND CI.CATEGORYCODE = P.CATEGORYCODE
WHERE CI.PROJECTNUMBER = (ISNULL(@PNO,CI.PROJECTNUMBER))
	AND PROJ.NAME = (ISNULL(@PNA,PROJ.NAME))
ORDER BY CI.PROJECTNUMBER

There are two ways to write error-free programs; only the third one works.

 

[Thargy]

as an oracle DBA who's had to convert t-sql to PL/SQL for an entire system, I can only offer two viable paths to achieve what you want.

1) buy a translation tool and watch it totally wreck your code
2) write a functional spec for the code, and get someone who can do PL/SQL to code it properly.

As with any RDBMS, one can only get the best from the technology, if one understands it to a certain degree. The notion that software can automatically translate T-SQL to well-optimised PL/SQL is risible.

N.B. I make no judgements about the merits of either Oracle or Sql Server as databases, I just observe the reality. Both are excellent, both have their weaknesses.

If you only have one or two procedures, then fine, use a tool. Otherwise, quit wasting your employer's time and money and employ someone to do it properly.

If you only have one or two routines, a TT-er (such as me) would probably do it for nothing, but don't count on it.

Precisely how big is this problem?

T

Grinding away at things Oracular