Using NVL function in SQL, please help!

Question

I'm needing to select first and last name in a table which is working fine in the following SQL, but the part that isn't working is the NVL function. The file should show all pilots at a company that fly helicopters and IF they don't have a licence the field HT_NAME should come up as 'N/A' and the field for end hours flown should be 0. I've put NVL function in as my text details but it still isn't working. Have i made a syntax error? Help would be appreciated.

Select E.EMP_NBR, E.EMP_FIRSTNAME || ' ' || E.EMP_LASTNAME, E.EMP_PILOT,
       ED.HT_NBR, NVL(HT.HT_NAME, 'N/A'), NVL(ED.END_HRS_FLOWN, 0),
       ED.END_LAST_ANNUAL_REVIEW_DATE
From  ENDORSEMENT ED, EMPLOYEE E, HELICOPTER_TYPE HT
WHERE HT.HT_NBR = ED.HT_NBR (+)
ORDER BY ED.END_HRS_FLOWN DESC, E.EMP_FIRSTNAME || ' ' || E.EMP_LASTNAME ASC;

should make employees who are not pilots appear with a N/A under heli type and 0 under hours flown. It isn't working - even though I have tried multiple things to repair it.

Answer 1

You have three tables and only one join condition; you need a join between Employee and Endorsement. Without that, you are getting a cross-product or Cartesian join between those tables.

With N tables (N > 0), you need J = N-1 join conditions at minimum. You have N = 3 tables, but J = 1 join conditions - which is too few. (There are various reasons why you might need J > N - 1, but the main one is because you need to join two columns between two tables. If you count each 'A.Col1 = B.Col2' condition as a separate join condition, then you need J > N-1; if you count the pair of conditions 'A.Col1 = B.Col2 AND A.Col3 = B.Col4' as a single join condition, then you still only need J = N-1 conditions. )

Ideally, you should also move away from the archaic, non-standard outer join notation using '(+)' and use the standard SQL joins.

---

The notation is a secondary issue. Every row in Employee is being joined with every row in Endorsement - and then that cross-product is being outer-joined with Helicopter_type. This is (almost certainly) not the query that is required.

You probably need to add some variation of this to the WHERE clause:

AND E.EMP_NBR = ED.EMP_NBR

(where the actual column in the Endorsement (ED) table is not shown in the query, so 'ED.EMP_NBR' is a guess).

Amongst other effects, if there are any helicopter pilots in the database, then every employee record is being joined with the endorsement for a pilot some of the time, and the sort order means that those records will be shown before the myriad records which show that the pilots don't have helicopter endorsements and that non-pilots don't have helicopter records, etc...

Answer 2

Check whether HT_NAME and END_HRS_FLOWN were having null values or not for the rows you expect N/A or 0

Edit 1

In Oracle, the NVL function lets you substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

If the value is not null, it will return same value

Answer 3

As @Jonathan said, you need to add something to the WHERE clause to tell the database how to match up EMPLOYEE with ENDORSEMENT. For purposes of discussion we'll use the EMP_NBR field on both, but you'll need to change the query around to use the field that's correct. We also need to know what field tells you if an employee has a valid license. I'm guessing it's something on the ENDORSEMENT table - let's call it ENDORSEMENT.LICENSE_TYPE for purposes of discussion. Once you know that you can use the NVL2 function to alter the values returned by the query appropriately, as follows:

SELECT E.EMP_NBR,
       E.EMP_FIRSTNAME || ' ' || E.EMP_LASTNAME AS FIRST_LAST_NAME,
       E.EMP_PILOT, 
       ED.HT_NBR,
       NVL2(ED.LICENSE_TYPE, HT.HT_NAME, 'N/A') HELO_TYPE,
       NVL2(ED.LICENSE_TYPE, ED.END_HRS_FLOWN, 0) FLOWN_HOURS, 
       ED.END_LAST_ANNUAL_REVIEW_DATE 
  FROM ENDORSEMENT ED,
       EMPLOYEE E,
       HELICOPTER_TYPE HT 
  WHERE HT.HT_NBR = ED.HT_NBR (+) AND
        ED.EMP_NBR = E.EMP_NBR (+)
  ORDER BY ED.END_HRS_FLOWN DESC,
           E.EMP_LASTNAME ASC,
           E.EMP_FIRSTNAME ASC;

I also changed the ORDER BY clause to get the employees ordered in a more normal manner.

Share and enjoy.