MS SQL now run in Oracle SQL, inner joins problem.

[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.

 

[r937]

  FROM "iScalaDB"."dbo"."_GL060106" 
                        "_GL060103"
INNER 
  JOIN "iScalaDB"."dbo"."_GL030106_VT" 
                        "_GL030103_VT"
    ON "_GL030103_VT"."Acc Dim"
     = "_GL060103"."Product"
INNER 
  JOIN "iScalaDB"."dbo"."_GL030106_CC" 
                        "_GL030103_CC"
    ON "_GL030103_CC"."Acc Dim"
     = "_GL060103"."Client Contact Num"

r937.com | rudy.ca

 

[maddave]

Thank you so much, you have made me see the wood for the trees! The MS SQL database I am working on has table names named so confusingly it is very difficult to decipher what is what. Take the code:

"iScalaDB"."dbo"."_GL030106_CC" "_GL030103_CC"

There is a table called "_GL030103_CC" in the database which I was assuming was being used for part of the join, when really its just being used to name the "_GL030106_CC" table in the query! Gah!

Anyway, thanks for your help. Unfortunatley, it doesn't return any results, when running the query under MS SQL it returns 115 rows, but Im sure I can work on that!

Thanks.