1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

need help rewriting query in Oracle 10g

Discussion in 'SQL PL/SQL' started by zwheeler, Dec 20, 2010.

  1. zwheeler

    zwheeler Guest

    Hi
    I have the following query that i wrote in SQL Server and it works perfectly, i tried to copy the query to Oracle 10g and it failed. I have done some research on some of the syntax and it appears that the From clause in and Update statement is not allowed.

    Any assitance would be greatly appreciated as I have to rewrite and run in ETL process by wednesday.

    Thanks in advance
    ---------------------------THIS QUERY IN SQL SERVER WORKS GREAT----------

    --mtr_net_operating_income
    UPDATE a SET mtr_net_operating_income = b.net_operating_income
    FROM stg_afs_header a
    INNER JOIN (-- NOI
    --226: 5000T,total revenue
    --316: 6263T Total Administrative Expenses
    --331: 6400T Total Utilities Expense
    --338: 6500T Total Operating and Maintenance Expenses
    --354: 6700T Total Taxes and Insurance
    --422: S1000-020 Total of 12 monthly deposits in the audit year into the Replacement Reserve account...
    --373: 6900 Nursing Homes/Assisted Living/ Board & Care/Other Elderly Care Expenses
    SELECT DISTINCT an.project_afs_header_id, an.submission_project_id,
    net_operating_income = CAST(IsNull([5000T],0)
    - IsNull([6263T],0)
    - IsNull([6400T],0)
    - IsNull([6500T],0)
    - IsNull([6700T],0)
    - IsNull([S1000-020],0)
    - IsNull([6900],0) as decimal(12,2))
    FROM project_afs_account_number an
    LEFT JOIN (--this sub has some null/zero values due to including non-valid AFS
    SELECT project_afs_header_id, submission_project_id, account_value [5000T]
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 226 AND account_value <> 0
    ) noi0 ON an.project_afs_header_id = noi0.project_afs_header_id AND an.submission_project_id = noi0.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6263T]
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 316
    ) noi1 ON an.project_afs_header_id = noi1.project_afs_header_id AND an.submission_project_id = noi1.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6400T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 331
    ) noi2 ON an.project_afs_header_id = noi2.project_afs_header_id AND an.submission_project_id = noi2.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6500T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 338
    ) noi3 ON an.project_afs_header_id = noi3.project_afs_header_id AND an.submission_project_id = noi3.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6700T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 354
    ) noi4 ON an.project_afs_header_id = noi4.project_afs_header_id AND an.submission_project_id = noi4.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6900]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 373
    ) noi5 ON an.project_afs_header_id = noi5.project_afs_header_id AND an.submission_project_id = noi5.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [S1000-020]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 422
    ) noi6 ON an.project_afs_header_id = noi6.project_afs_header_id AND an.submission_project_id = noi6.submission_project_id
    ) as b ON a.fk_afs_header_id = b.project_afs_header_id AND a.fk_afs_project_id = b.submission_project_id
    WHERE ind_is_combined_afs = 'N'
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    UPDATE stg_afs_header SET mtr_net_operating_income = (SELECT b.net_operating_income
    FROM stg_afs_header a
    INNER JOIN (-- NOI
    --226: 5000T,total revenue
    --316: 6263T Total Administrative Expenses
    --331: 6400T Total Utilities Expense
    --338: 6500T Total Operating and Maintenance Expenses
    --354: 6700T Total Taxes and Insurance
    --422: S1000-020 Total of 12 monthly deposits in the audit year into the Replacement Reserve account...
    --373: 6900 Nursing Homes/Assisted Living/ Board & Care/Other Elderly Care Expenses
    SELECT DISTINCT an.project_afs_header_id, an.submission_project_id,
    net_operating_income = CAST(IsNull([5000T],0)
    - IsNull([6263T],0)
    - IsNull([6400T],0)
    - IsNull([6500T],0)
    - IsNull([6700T],0)
    - IsNull([S1000-020],0)
    - IsNull([6900],0) AS DECIMAL(12,2))
    FROM project_afs_account_number an
    LEFT JOIN (--this sub has some null/zero values due to including non-valid AFS
    SELECT project_afs_header_id, submission_project_id, account_value [5000T]
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 226 AND account_value <> 0
    ) noi0 ON an.project_afs_header_id = noi0.project_afs_header_id AND an.submission_project_id = noi0.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6263T]
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 316
    ) noi1 ON an.project_afs_header_id = noi1.project_afs_header_id AND an.submission_project_id = noi1.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6400T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 331
    ) noi2 ON an.project_afs_header_id = noi2.project_afs_header_id AND an.submission_project_id = noi2.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6500T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 338
    ) noi3 ON an.project_afs_header_id = noi3.project_afs_header_id AND an.submission_project_id = noi3.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6700T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 354
    ) noi4 ON an.project_afs_header_id = noi4.project_afs_header_id AND an.submission_project_id = noi4.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [6900]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 373
    ) noi5 ON an.project_afs_header_id = noi5.project_afs_header_id AND an.submission_project_id = noi5.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, IsNull(account_value,0) [S1000-020]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 422
    ) noi6 ON an.project_afs_header_id = noi6.project_afs_header_id AND an.submission_project_id = noi6.submission_project_id
    ) AS b ON a.fk_afs_header_id = b.project_afs_header_id AND a.fk_afs_project_id = b.submission_project_id
    WHERE ind_is_combined_afs = 'N');
    See if that gives you the same results.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    By the way,

    What is IsNull and Cast ? CAST syntax looks strange .
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It is the SQL Server equivalent of NVL. I overlooked that in my first response, and the CAST syntax should be fine as he's casting the result of a large subtraction to a decimal:

    Code (SQL):
     
    UPDATE a SET mtr_net_operating_income = b.net_operating_income
    FROM stg_afs_header a
    INNER JOIN (-- NOI
    --226: 5000T,total revenue
    --316: 6263T Total Administrative Expenses
    --331: 6400T Total Utilities Expense
    --338: 6500T Total Operating and Maintenance Expenses
    --354: 6700T Total Taxes and Insurance
    --422: S1000-020 Total of 12 monthly deposits in the audit year into the Replacement Reserve account...
    --373: 6900 Nursing Homes/Assisted Living/ Board & Care/Other Elderly Care Expenses
    SELECT DISTINCT an.project_afs_header_id, an.submission_project_id,
    net_operating_income = CAST(NVL([5000T],0)
    - NVL([6263T],0)
    - NVL([6400T],0)
    - NVL([6500T],0)
    - NVL([6700T],0)
    - NVL([S1000-020],0)
    - NVL([6900],0) AS DECIMAL(12,2))
    FROM project_afs_account_number an
    LEFT JOIN (--this sub has some null/zero values due to including non-valid AFS
    SELECT project_afs_header_id, submission_project_id, account_value [5000T]
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 226 AND account_value <> 0
    ) noi0 ON an.project_afs_header_id = noi0.project_afs_header_id AND an.submission_project_id = noi0.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) [6263T]
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 316
    ) noi1 ON an.project_afs_header_id = noi1.project_afs_header_id AND an.submission_project_id = noi1.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) [6400T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 331
    ) noi2 ON an.project_afs_header_id = noi2.project_afs_header_id AND an.submission_project_id = noi2.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) [6500T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 338
    ) noi3 ON an.project_afs_header_id = noi3.project_afs_header_id AND an.submission_project_id = noi3.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) [6700T]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 354
    ) noi4 ON an.project_afs_header_id = noi4.project_afs_header_id AND an.submission_project_id = noi4.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) [6900]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 373
    ) noi5 ON an.project_afs_header_id = noi5.project_afs_header_id AND an.submission_project_id = noi5.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) [S1000-020]
    FROM project_afs_account_number
    WHERE account_number_ref_id = 422
    ) noi6 ON an.project_afs_header_id = noi6.project_afs_header_id AND an.submission_project_id = noi6.submission_project_id
    ) AS b ON a.fk_afs_header_id = b.project_afs_header_id AND a.fk_afs_project_id = b.submission_project_id
    WHERE ind_is_combined_afs = 'N'

     
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India


    There are still some Syntax Issues.

    like

    Code (SQL):
     CAST(NVL([5000T],0)
    Code (SQL):
     account_value [5000T]
    Code (SQL):
     NVL(account_value,0) [6700T]
    Code (SQL):
     NVL(account_value,0) [6900]
    Code (SQL):
     NVL(account_value,0) [S1000-020]
    and the Cast Syntax is as this in Oracle
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Tested, this works in Oracle 10.2.0.4:

    Code (SQL):
     
    UPDATE stg_afs_header SET mtr_net_operating_income = (SELECT b.net_operating_income
    FROM stg_afs_header a
    INNER JOIN (-- NOI
    --226: 5000T,total revenue
    --316: 6263T Total Administrative Expenses
    --331: 6400T Total Utilities Expense
    --338: 6500T Total Operating and Maintenance Expenses
    --354: 6700T Total Taxes and Insurance
    --422: S1000-020 Total of 12 monthly deposits in the audit year into the Replacement Reserve account...
    --373: 6900 Nursing Homes/Assisted Living/ Board & Care/Other Elderly Care Expenses
    SELECT DISTINCT an.project_afs_header_id, an.submission_project_id,
    net_operating_income = CAST(NVL("[5000T]",0)
    - NVL("[6263T]",0)
    - NVL("[6400T]",0)
    - NVL("[6500T]",0)
    - NVL("[6700T]",0)
    - NVL("[S1000-020]",0)
    - NVL("[6900]",0) AS DECIMAL(12,2))
    FROM project_afs_account_number an
    LEFT JOIN (--this sub has some null/zero values due to including non-valid AFS
    SELECT project_afs_header_id, submission_project_id, account_value "[5000T]"
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 226 AND account_value <> 0
    ) noi0 ON an.project_afs_header_id = noi0.project_afs_header_id AND an.submission_project_id = noi0.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) "[6263T]"
    FROM project_afs_account_number an
    WHERE account_number_ref_id = 316
    ) noi1 ON an.project_afs_header_id = noi1.project_afs_header_id AND an.submission_project_id = noi1.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) "[6400T]"
    FROM project_afs_account_number
    WHERE account_number_ref_id = 331
    ) noi2 ON an.project_afs_header_id = noi2.project_afs_header_id AND an.submission_project_id = noi2.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) "[6500T]"
    FROM project_afs_account_number
    WHERE account_number_ref_id = 338
    ) noi3 ON an.project_afs_header_id = noi3.project_afs_header_id AND an.submission_project_id = noi3.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) "[6700T]"
    FROM project_afs_account_number
    WHERE account_number_ref_id = 354
    ) noi4 ON an.project_afs_header_id = noi4.project_afs_header_id AND an.submission_project_id = noi4.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) "[6900]"
    FROM project_afs_account_number
    WHERE account_number_ref_id = 373
    ) noi5 ON an.project_afs_header_id = noi5.project_afs_header_id AND an.submission_project_id = noi5.submission_project_id
    LEFT JOIN (
    SELECT project_afs_header_id, submission_project_id, NVL(account_value,0) "[S1000-020]"
    FROM project_afs_account_number
    WHERE account_number_ref_id = 422
    ) noi6 ON an.project_afs_header_id = noi6.project_afs_header_id AND an.submission_project_id = noi6.submission_project_id
    ) AS b ON a.fk_afs_header_id = b.project_afs_header_id AND a.fk_afs_project_id = b.submission_project_id
    WHERE ind_is_combined_afs = 'N');
     
    The decimal datatype is converted implicitly to number and the cast syntax, as written, does not throw an error.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    net_operating_income = CAST(NVL("[5000T]",0)  
     
    Cast Syntax is wrong


    Code (SQL):

    [orcl11gdb@SVRV37 ~]$ sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production ON Fri Jan 7 11:53:09 2011

    Copyright (c) 1982, 2009, Oracle.  ALL rights reserved.

    Enter user-name: dmig
    Enter password:

    Connected TO:
    Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options

    SQL> SELECT CAST(NVL("[5000T]",0)  FROM dual;
    SELECT CAST(NVL("[5000T]",0)  FROM dual
                                  *
    ERROR at line 1:
    ORA-00905: missing keyword


    SQL> SELECT CAST(NVL("[5000T]",0))  FROM dual;
    SELECT CAST(NVL("[5000T]",0))  FROM dual
                                *
    ERROR at line 1:
    ORA-00905: missing keyword


    SQL> SELECT CAST(NVL("DUMMY",0)) FROM DUAL;
    SELECT CAST(NVL("DUMMY",0)) FROM DUAL
                              *
    ERROR at line 1:
    ORA-00905: missing keyword


    SQL> SELECT CAST(NVL(1000,0)) FROM DUAL;
    SELECT CAST(NVL(1000,0)) FROM DUAL
                           *
    ERROR at line 1:
    ORA-00905: missing keyword


    SQL>


     
    Morover 5000T , 6263T are Column names adn not variables...
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please read the entire query text as those column names are assigned further down in the text.