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!

NVL use in the ON portion of a JOIN - Oracle 11g

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, Oct 22, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    The results I'm trying to acquire include NULL values. There is only one column that is not used in a calculation and there are 8 columns that are calculated. Each calculation has unique requirements. Consequently, I chose to use JOINs to get accurate values, but for those rows in the database containing NULL in the Dept column, I can't get the calculated values unless I run a separate query for each of the calculations. If I add the NVL in the Select clauses that are within the JOINS, I get invalid identifier ORA-00904. How can I overcome this error and get calculated values for the report row that has a blank Dept?

    Code (SQL):
    SELECT NVL(t1.DEPT, ' '),
               ex1.CALC1,
               ex2.CALC2,
               ex3.CALC3,
               ...
    FROM TABLE1 t1
    JOIN TABLE2 t2
      ON t1.DEPT = t2.DEPT
    JOIN TABLE3 t3....

    JOIN
         (SELECT COUNT(*) AS CALC1,
                     st1.DEPT
          FROM SUBTBL1 st1
          JOIN SUBTBL2 st2
             ON st1.DEPT = st2.DEPT
          WHERE st2.IND = 'Q'
          GROUP BY st1.DEPT
         )  ex1
      ON ex1.DEPT = t1.DEPT
    JOIN
         (SELECT SUM(st2.AMT) AS CALC2,
                     st1.DEPT
          FROM SUBTBL1 st1
          JOIN SUBTBL2 st2
             ON st1.DEPT = st2.DEPT
          WHERE st2.IND = 'F'
              AND st1.PD_DT >= TO_DATE('10/01/2014', 'MM/DD/YYYY')
          GROUP BY st1.DEPT
         )  ex1
      ON ex1.DEPT = t1.DEPT
    ...
    ORDER BY t1.DEPT
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Haven't tested it - you haven't posted the table creation scripts and sample data to check ...
    But try the following:

    Code (SQL):
    SELECT NVL(t1.DEPT, ' '),
               ex1.CALC1,
               ex2.CALC2,
               ex3.CALC3,
               ...
    FROM TABLE1 t1
    JOIN TABLE2 t2
      ON NVL(t1.DEPT, ' ') = NVL(t2.DEPT, ' ')
    JOIN TABLE3 t3....
     
    JOIN
         (SELECT COUNT(*) AS CALC1,
                     NVL(st1.DEPT, ' ') DEPT
          FROM SUBTBL1 st1
          JOIN SUBTBL2 st2
             ON st1.DEPT = st2.DEPT
          WHERE st2.IND = 'Q'
          GROUP BY st1.DEPT
         )  ex1
      ON ex1.DEPT = NVL(t1.DEPT, ' ')
    JOIN
         (SELECT SUM(st2.AMT) AS CALC2,
                     NVL(st1.DEPT, ' ') DEPT
          FROM SUBTBL1 st1
          JOIN SUBTBL2 st2
             ON st1.DEPT = st2.DEPT
          WHERE st2.IND = 'F'
              AND st1.PD_DT >= TO_DATE('10/01/2014', 'MM/DD/YYYY')
          GROUP BY st1.DEPT
         )  ex1
      ON ex1.DEPT = NVL(t1.DEPT, ' ')
    ...
    ORDER BY t1.DEPT
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for your reply Rajen. What you did is exactly what I tried. I get the ORA-00904 on the NVL clause in the ON portion: ON ex1.DEPT = NVL(t1.DEPT, ' ')

    I didn't include data because the actual query is far more complex than what I've provided, as is the data. I know that doesn't give you much to go on. I can tell you that if I only execute the select portion within the JOIN and I include the NVL, it works (See below). Obviously and unfortunately, I need all the calculations to appear in the report.

    Code (SQL):
    SELECT COUNT(*) AS CALC1,
                     NVL(st1.DEPT, ' ') DEPT
          FROM SUBTBL1 st1
          JOIN SUBTBL2 st2
             ON st1.DEPT = st2.DEPT
          WHERE st2.IND = 'Q'
          GROUP BY NVL(st1.DEPT, ' ');
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    In 11.2.0.4:


    Code (SQL):

    SQL> SELECT e.empno, e.ename, d.deptno, e.sal, e.job
      2  FROM emp e JOIN dept d ON (d.deptno = nvl(e.deptno, 9999))
      3  /


         EMPNO ENAME          DEPTNO        SAL JOB
    ---------- ---------- ---------- ---------- ---------
          7369 SMITH              20        800 CLERK
          7499 ALLEN              30       1600 SALESMAN
          7521 WARD               30       1250 SALESMAN
          7566 JONES              20       2975 MANAGER
          7654 MARTIN             30       1250 SALESMAN
          7698 BLAKE              30       2850 MANAGER
          7782 CLARK              10       2450 MANAGER
          7788 SCOTT              20       3000 ANALYST
          7839 KING               10       5000 PRESIDENT
          7844 TURNER             30       1500 SALESMAN
          7876 ADAMS              20       1100 CLERK
          7900 JAMES              30        950 CLERK
          7902 FORD               20       3000 ANALYST
          7934 MILLER             10       1300 CLERK
          7939 DUKE               10       5000 CEO
          7949 PRINCE             10       5000 CFO
          7959 QUEEN              10       5000 CIO
          7869 JACK               10       5000 PRESIDENT


    18 ROWS selected.


    SQL>
     

    In 12.1.0.2:


    Code (SQL):

    SQL> SELECT e.empno, e.ename, d.deptno, e.sal, e.job
      2  FROM emp e JOIN dept d ON (d.deptno = nvl(e.deptno, 9999))
      3  /


         EMPNO ENAME          DEPTNO        SAL JOB
    ---------- ---------- ---------- ---------- ---------
          7369 SMITH              20        800 CLERK
          7499 ALLEN              30       1600 SALESMAN
          7521 WARD               30       1250 SALESMAN
          7566 JONES              20       2975 MANAGER
          7654 MARTIN             30       1250 SALESMAN
          7698 BLAKE              30       2850 MANAGER
          7782 CLARK              10       2450 MANAGER
          7788 SCOTT              20       3000 ANALYST
          7839 KING               10       5000 PRESIDENT
          7844 TURNER             30       1500 SALESMAN
          7876 ADAMS              20       1100 CLERK
          7900 JAMES              30        950 CLERK
          7902 FORD               20       3000 ANALYST
          7934 MILLER             10       1300 CLERK
          7939 DUKE               10       5000 CEO
          7949 PRINCE             10       5000 CFO
          7959 QUEEN              10       5000 CIO
          7869 JACK               10       5000 PRESIDENT


    18 ROWS selected.


    SQL>
     

    So the ON ... = nvl(some expression here) isn't causing the problem if you're using 11.2 or later. As you reported if you nest the queries it doesn't work, so I expect it's a scope issue. Try to re-write the query as a WITH query (subquery factoring) and try the join in the 'driving' select:


    with blahblah as (
    ...
    ),
    yaddayadda as (
    ...
    ),
    bleezo as (
    ...
    )
    select ... from ... join ... on () join ... on () ...


    and see if that fixes the problem.
     
  5. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Thank you, David! I'll give that a try.
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Can you please check if you have put the proper alias in the inline query - I mean on the line:

    NVL(st1.DEPT, ' ') DEPT

    Code (SQL):

    ...
    JOIN
         (SELECT COUNT(*) AS CALC1,
                     NVL(st1.DEPT, ' ') DEPT
          FROM SUBTBL1 st1
          JOIN SUBTBL2 st2
             ON st1.DEPT = st2.DEPT
          WHERE st2.IND = 'Q'
          GROUP BY st1.DEPT
         )  ex1
      ON ex1.DEPT = NVL(t1.DEPT, ' ')
    ...
     
    If you don't put "DEPT" as alias for NVL(st1.DEPT, ' '), you'll get ORA-00904.