NVL() function gives out NULL-s?

dataservices job design
1

Hello,
tonight we got a very strange error. DataFlow tried to insert NULL-s into certain NOT NULL COLUMN.
All our query values are surrounded with NVL(Query.fieldName, -1) function.
This certain column gets its value from database function (eg. DS_LIVE.BO.FIND_ASSOCIATED_PERSON_ID(null, date1, date2)) Also the first parameter for our PL/SQL function is NULL with this certain row, but this shouldnt matter, because also this pl/sql function returns only nvl-ed values.

But still, it gives out NULL-s. What might be the cause of NVL to give out NULL-s?

I also mention that all our last query transforms before table targets are using NVL-s.

bobmeediake :estonia: (BOB member since 2008-12-01)

2

Try deleting the target table from the dataflow. Save the dataflow, close the dataflow. Then add it back again.

We’ve found sometimes this helps in these cases – it’s like the mappings between the query step and the target table get scrambled.

dnewton :us: (BOB member since 2004-01-30)

3

outer join?

Werner Daehn :de: (BOB member since 2004-12-17)

4

There is no outer join. The field from the previous query is surrounded with the NVL function and as stated in the techical manual: NVL replaces NULL values with specified values, but here it is not doing it, it still gives out NULL.

I replaced the original table with template table(template table is not pre-created, so I saw actual data types and values) and tried to run this flow again. The row with the NULL column appeared in the table.

I thought that the problem may be with different data types, but I changed them and no hope, so I added additional query transform between the last query transform and the target table and NVL-ed all fields once again.

Now it worked. Still strange!

bobmeediake :estonia: (BOB member since 2008-12-01)

5

The only other idea is empty strings and pushdowns.

The nvl(’’, NULL) might be pushed down and the database treats both different. So the result of this function is an empty string hence. In the target database there is no empty string semantic, these are converted to NULLs. Something along those lines…

Werner Daehn :de: (BOB member since 2004-12-17)

6

Just in case I write here down the “path” of this field:

Query1:
DS_TARGET.BO.FIND_PERSON_ID (it is added as a function call and passed on as PERSON_ID decimal(28,7))
->Query2:
NVL(PERSON_ID,-1), passed on as person_id int

Converting from decimal(28,7)->int should not affect anything in my case?

bobmeediake :estonia: (BOB member since 2008-12-01)

7

so much about empty strings, when the datatype is a number…

I am out of ideas. :crazy_face:

Werner Daehn :de: (BOB member since 2004-12-17)

8

what is the version of DI or Data Services ?

there was a bug in 12.1, in which the function was returning the first argument that is passed as return value, what is the first argument datatype that you are passing to this function

try calling this function from a script and see what is the value its returning ?

manoj_d (BOB member since 2009-01-02)

9

I imported and re-imported the target table and got it work.

Calling this function from script resulted in ACCESS_VIOLATION, I tested this script in different machines and the stack trace was different.

So I considered it as an application bug and I sent this error with the process dump to our reseller.

bobmeediake :estonia: (BOB member since 2008-12-01)