MS SQL to Oracle SQL.

[maddave]

I am currently migrating a lot of SQL procedures that used to run on MS SQL and are now being accessed from Oracle 9i using Hetrogenous Connectivity.

The following is the MS SQL select statment, but I am having real trouble getting it to work in Oracle. Its the inner join section that is causing the headache and the syntax used in Oracle which is different to MS SQL:

SELECT 
    'Scala' "System"
    ,"_GL060103"."Emission Period" "EP" 
    ,"_GL060103"."Client Contact Num" "Contract"
    ,sum("_GL060103"."Amount") "Scala_VIC"
FROM 
    "iScalaDB"."dbo"."_GL030106_CC" "_GL030103_CC" 
       	INNER JOIN ("iScalaDB"."dbo"."_GL030106_VT" "_GL030103_VT" 
	INNER JOIN "iScalaDB"."dbo"."_GL060106" "_GL060103" 
		ON "_GL030103_VT"."Acc Dim"="_GL060103"."Product")
          	ON "_GL030103_CC"."Acc Dim"="_GL060103"."Client Contact Num"
WHERE
    not "_GL060103"."Account" in (N'80000000',N'70500000',N'46110001') /* Take out Stats, Income and Old accounts */
    and "_GL060103"."Client Contact Num" <> N'100000' /*Ignore NASS Accounts */
    and "_GL060103"."Emission Period" <> '' /*Ignore blank EPs */
    and "_GL060103"."Product" in (N'080',N'088') /* Select Scala CCC & CCP Voucher products */
    and "_GL060103"."Emission Period" >= N'0444'
GROUP BY
    "_GL060103"."Emission Period"
    ,"_GL060103"."Client Contact Num"
HAVING
    sum("_GL060103"."Amount") <> 0
ORDER BY
    "_GL060103"."Emission Period"
    ,"_GL060103"."Client Contact Num"

I have changed each table name for the relevent database link in oracle and can select data from each table, but I cant work out how to rewrite the join properley.

Any help would be great.

 

[SantaMufasa]

I am having real trouble getting it to work in Oracle.

Unfortunately, Dave, my crystal ball is rather cloudy today and I can't see (from Utah, USA) your screen in the UK. Can you please describe more clearly for me (preferrably in the form of a screen copy and paste) what real trouble you are having?...Is the trouble a) syntax errors, b) run-time errors, c) logic errors (i.e., results, but incorrect results)?


Also, for future reference, (but certainly not an issue here) unless you are following a shop coding standard, typical Oracle database design/creation follows a coding convention of formulating object/column names that does not require enclosure in double quotes...it simply reduces the "annoyance level" for both coders and readers.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.