Toad World® Forums

NVL Usage Question

Hello,

I’ve got a couple of questions regarding NVL usage in Oracle, but I’ll start with the most vexing first :smiley:

I have a situation where I’m pulling the business name of a customer from a table. However, if that customer isn’t currently active - instead they are pending - the business name resides on a different table until the order is complete.

Here’s the select that I’m trying to use:

NVL(N.BUSINESS_NAME,D.BUSINESS_NAME) AS “BUSINESS NAME”,

Which refers back to this join:

LEFT OUTER JOIN IDST_CUST_BUS_DIRECTORY N
ON W.ACCTCORP=N.ACCTCORP AND W.HOUSE=N.HOUSE AND W.CUST=N.CUST
LEFT OUTER JOIN IDST_WIP_BUS_DIRECTORY D
ON W.ACCTCORP=D.ACCTCORP AND W.HOUSE=D.HOUSE AND W.CUST=D.CUST AND W.WPCNT=D.WPCNT

However, the NVL doesn’t appear to be working - if the value is null (as it shows on the data grid in TDA), it is not pulling the value from the other table, which isn’t null.

Any thoughts?

Is N.BUSINESS_NAME really NULL, or does it contain something like a single space? A single space is not a null value.

I just remembered that in TDA, the grid will show “{null}” for null values, and not a space. So you can’t presumably know that N.BUSINESS_NAME is indeed a null value. Next thing would be to have this in the SELECT statement:

SELECT N.BUSINESS_NAME AS “N BUSINESS NAME”, D.BUSINESS_NAME AS “D BUSINESS NAME”, NVL (N.BUSINESS_NAME, D.BUSINESS_NAME) as “NVL BUSINESS NAME”

Display all three values in the query results, and send a screenshot if the results look confusing.

I’ve never had a problem with the NVL function like you are describing.

Well, it really REALLY helps if you look at the right table - and it was your suggestion Debbie that helped me figure out what I was doing wrong :slight_smile:

The two tables I was looking at only contain information if the customer has a particular product - for everyone else (including them), there’s a different table entirely, and once I started using that, I’m getting the results I expect :slight_smile:

Yay!