Re: SQL UDF not found MADNESS

On 04-Aug-2011 12:44 , Charles Wilt wrote:

<<SNIP>> try to CAST the field to match

select TRUEDATE( cast(fldCC as dec(2,0))
, cast(fldYY as dec(2,0))
, cast(fldMM as dec(2,0))
, cast(fldDD as dec(2,0))
)

<<SNIP>>

Although explained using CAST() is probably much clearer to a reader than what is shown below [esp. for its syntax "as Data-Type"], a couple of FWiW thoughts may be of value to someone:

Note that the SQL scalar function DECIMAL [or DEC] is one of the various _casting_ scalar functions such that the CAST() function can be replaced. However replacing CAST() with DECIMAL() has potential for reduced portability of the SQL. I often would write instead:

select TRUEDATE( dec(fldCC, 2, 0)
, dec(fldYY, 2, 0)
, dec(fldMM, 2, 0)
, dec(fldDD, 2, 0)
)

Note also the space included after the comma so the syntax remains valid where decimal separator is comma. Including the blank space after the comma increases the portability of the SQL; e.g. someone in Spain could copy\paste the above statement snippet without then also having to add the missing spaces to the quoted snippet, in order to make the SQL functional. However for the special case of no scale, per zero specified on each scale provided, then even better to just drop both the comma and the scale specification entirely, irrespective of the means utilized to effect the casting to decimal; e.g. showing both forms of casting with zero-scale defaulted:

select TRUEDATE( dec(fldCC, 2) /* scale defaults to zero */
, cast(fldYY as dec(2)) /* scale defaults to zero */
, ...

Regards, Chuck