I've got a couple of questions regarding NVL usage in Oracle, but I'll start with the most vexing first
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.
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
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
