Skip to Main Content
  • Questions

  • Sql Loader (NULLIF and NVL(TO_NUMBER) - NULLIF not yielding the correct result. The columns is not being set as NULL but loaded with 0.0.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kevin.

Asked: November 08, 2016 - 2:43 pm UTC

Last updated: March 19, 2019 - 8:09 am UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

I have the following Sql Loader Control file :

POS_COST_PRC_FUND POSITION(173:202) DECIMAL EXTERNAL NULLIF V4_NULL='?'
"NVL(TO_NUMBER(LTRIM(:POS_COST_PRC_FUND,'0')),0)",
V4_NULL FILLER POSITION(203:203) CHAR,

The column in Oracle is defined as a BINARY_DOUBLE in Oracle 11g.

When the column V4_NULL contains a '?' we expect the Oracle Column POS_COST_PRC_FUND to be set to NULL. However , the sql expression looks like it is being executed , as the Oracle Column POS_COST_PRC_FUND is set to zero.

I thought that NULLIF would be checked first, and if found , set the column to NULL. What have I not accounted for please ?

Appreciate all help.
Kevin.

and Connor said...

I replicated the issue, eg

LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(x,
y nullif y = '?' "NVL(TO_NUMBER(LTRIM(:X,'0')),0)" 
)
BEGINDATA
1,10
2,20
3,?

SQL> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3


but I worked around it by putting all of the logic into the expression

LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(x,
y "case when :y = '?' then null else NVL(TO_NUMBER(LTRIM(:X,'0')),0) end" 
)
BEGINDATA
1,10
2,20
3,?

SQL> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3


Hope this helps

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Answer doesn't solve the actual problem

Johannes, March 19, 2019 - 7:42 am UTC

Hi,

I apologize for opening this up quite a while after it was created, but I'm currently facing the same problem and the answer doesn't solve the actual problem.

The problem in the question is, that there is a null indicator field 'V4_NULL', which is not to be reflected in the target table, which is why it needs to be specified as FILLER.
However, this field still is the null indicator for 'POS_COST_PRC_FUND', which is why it is checked in the NULLIF-clause of that field.
Expected behaviour would now be that 'POS_COST_PRC_FUND' is always null whenever 'V4_NULL' is a '?'.
Unfortunately, what actually happens is, that the nullif is ignored and the sql (nvl(...)) is executed instead.
The desired behaviour would be that the sql gets ignored when nullif condition is specified.

In your solution you don't use a FILLER field, that's why it works. Unfortunately as soon as the null indicator is a FILLER field it can't be referenced anymore within an sql clause.

I can't find a solution for this as well and would be happy if someone could check this again. Or, if the original author of the question found a way to solve it, that would also be well appreciated!

Thanks a lot,
Johannes

Found the real answer

Johannes, March 19, 2019 - 7:52 am UTC

To everyone still looking for the solution,

my previous review was too quick, I now found the actual solution by myself.

In order to solve the original problem, the FILLER field should be specified with BOUNDFILLER instead and then it can be referenced in data field 'POS_COST_PRC_FUND' as the original answer suggested. So the code should be as follows to work as intended:

POS_COST_PRC_FUND POSITION(173:202) DECIMAL EXTERNAL "case when :V4_NULL = '?' then null else NVL(TO_NUMBER(LTRIM(:POS_COST_PRC_FUND,'0')),0) end",
V4_NULL BOUNDFILLER POSITION(203:203) CHAR,

Since I haven't found this anywhere else and it took me quite some time to figure it out, maybe this will be of help to someone else looking for this.

Thanks,
Johannes
Connor McDonald
March 19, 2019 - 8:09 am UTC

Thanks for passing that info back.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.