User defined function (UDF) returning 'not null'



IBM's flagship relational database management system

User defined function (UDF) returning 'not null'

Postby bravedreamer » Thu Oct 09, 2014 11:48 pm

Hey guys,

I have somehow a special problem. Lately I had to write some user defined functions (sql based functions written in PLSQL, not external functions).
The function itself was quite easy. It contained just a lookup for an input parameter in a special table. If the lookup returned a record, the value '1' is returned. Otherwise the value '0'.
So, there will be returned every time a value.

My problem now is, that if I use the UDF in an UNLOAD-Statement, the PUNCH-file contains the additional byte for the indicator.

Is there a way to define a UDF in such a way, that DB2 recognizes it as a function 'that can never return a NULL value'?
bravedreamer
 
Posts: 17
Joined: Wed Nov 11, 2009 6:44 pm
Has thanked: 0 time
Been thanked: 0 time

Re: User defined function (UDF) returning 'not null'

Postby GuyC » Wed Oct 22, 2014 4:57 pm

using coalesce(myfunction(blabla),0) in your unload would remove the null-indicator.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: User defined function (UDF) returning 'not null'

Postby bravedreamer » Wed Oct 22, 2014 5:27 pm

Yes, I know that. But I think this is only a workaround.

There should be a way to define the function in the way that DB2 recognizes it al 'never returns NULL'...
bravedreamer
 
Posts: 17
Joined: Wed Nov 11, 2009 6:44 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post