Re: SQL UDF not found MADNESS

[CRPence <CRPbottle@xxxxxxxxx>]

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