Firstly I would like to say hello to you all as I am a new member.
I'm calling a User Defined Function 'FormatDate' from DB2 SQL on an AS/400. The UDF receives a CYYMMDD numeric date and returns a DD/MM/YYYY character date. The UDF doesn't allow null parameters so I'm using a Case statement as follows:-
[PHP]select case when DATEVAL is not null then
FormatDate(DATE VAL) else 'blank' end
from FILE1 left join FILE2 on F1KEY1=F2KEY1[/PHP]
The UDF still fails because of null parameters, how is this possible?
If run with an inner join so no nulls are returned then I get correct DD/MM/YYYY dates.
If I replace the UDF with a literal then I get the correct result e.g.
[PHP]select case when DATEVAL is not null then
'OK' else 'blank' end
from FILE1 left join FILE2 on F1KEY1=F2KEY1[/PHP]
I have solved the problem by specifying the 'RETURNS NULL ON NULL INPUT' clause to the UDF but this doesn't answer my question as to why the function fails when I assume that it will not be invoked if DATEVAL is null.
Thanks in advance for your thoughts, TK
I'm calling a User Defined Function 'FormatDate' from DB2 SQL on an AS/400. The UDF receives a CYYMMDD numeric date and returns a DD/MM/YYYY character date. The UDF doesn't allow null parameters so I'm using a Case statement as follows:-
[PHP]select case when DATEVAL is not null then
FormatDate(DATE VAL) else 'blank' end
from FILE1 left join FILE2 on F1KEY1=F2KEY1[/PHP]
The UDF still fails because of null parameters, how is this possible?
If run with an inner join so no nulls are returned then I get correct DD/MM/YYYY dates.
If I replace the UDF with a literal then I get the correct result e.g.
[PHP]select case when DATEVAL is not null then
'OK' else 'blank' end
from FILE1 left join FILE2 on F1KEY1=F2KEY1[/PHP]
I have solved the problem by specifying the 'RETURNS NULL ON NULL INPUT' clause to the UDF but this doesn't answer my question as to why the function fails when I assume that it will not be invoked if DATEVAL is null.
Thanks in advance for your thoughts, TK