Topic
  • 7 replies
  • Latest Post - ‏2012-04-13T12:41:55Z by LuisRodriguez
Matteo_Ceriani
Matteo_Ceriani
8 Posts

Pinned topic cast in return into UDF

‏2012-04-11T12:05:23Z |
in this post: (http://www.ibm.com/developerworks/forums/thread.jspa?threadID=402518&tstart=30) i asked how to create a UDF that conver a number in a char, keeping the format.
I have used the UDF wrote by Bruce Vining (http://www.mcpressonline.com/programming/apis/the-api-corner-dynamically-editing-a-numeric-value.html)

Now in my code use:

Select  cast (EDITC(field, n, p, 
'X') as CHAR(n+2)) from MyTable

Where:
n = length of number
p = precision
N+2 = length of the char that i want (2 = 1 byte for the sign and 1 byte for the point/comma)

Now I'd like to return the exact result without using the cast function, there is a smart way for doing this?

thanks
Updated on 2012-04-13T12:41:55Z at 2012-04-13T12:41:55Z by LuisRodriguez
  • LuisRodriguez
    LuisRodriguez
    15 Posts

    Re: cast in return into UDF

    ‏2012-04-11T13:25:02Z  
    > Matteo_Ceriani wrote:
    > in this post: (http://www.ibm.com/developerworks/forums/thread.jspa?threadID=402518&tstart=30) i asked how to create a UDF that conver a number in a char, keeping the format.
    > I have used the UDF wrote by Bruce Vining (http://www.mcpressonline.com/programming/apis/the-api-corner-dynamically-editing-a-numeric-value.html)
    >
    > Now in my code use:
    >
    
    > Select  cast (EDITC(field, n, p, 
    'X') as CHAR(n+2)) from MyTable >
    

    > Where:
    > n = length of number
    > p = precision
    > N+2 = length of the char that i want (2 = 1 byte for the sign and 1 byte for the point/comma)
    >
    > Now I'd like to return the exact result without using the cast function, there is a smart way for doing this?
    >
    > thanks
    Matteo,

    Could you please post an example of what you are trying to accomplish?

    Regards,

    Luis
  • Matteo_Ceriani
    Matteo_Ceriani
    8 Posts

    Re: cast in return into UDF

    ‏2012-04-11T14:40:48Z  
    > Matteo_Ceriani wrote:
    > in this post: (http://www.ibm.com/developerworks/forums/thread.jspa?threadID=402518&tstart=30) i asked how to create a UDF that conver a number in a char, keeping the format.
    > I have used the UDF wrote by Bruce Vining (http://www.mcpressonline.com/programming/apis/the-api-corner-dynamically-editing-a-numeric-value.html)
    >
    > Now in my code use:
    > <pre class="jive-pre"> > Select cast (EDITC(field, n, p, 'X') as CHAR(n+2)) from MyTable > </pre>
    > Where:
    > n = length of number
    > p = precision
    > N+2 = length of the char that i want (2 = 1 byte for the sign and 1 byte for the point/comma)
    >
    > Now I'd like to return the exact result without using the cast function, there is a smart way for doing this?
    >
    > thanks
    Matteo,

    Could you please post an example of what you are trying to accomplish?

    Regards,

    Luis
    I try:

    Create a table:
    
    create table qtemp.test ( field_1 decimal(15, 3) )
    


    some value:
    
    INSERT INTO QTEMP.TEST VALUES(1154,256) INSERT INTO QTEMP.TEST VALUES( -154,238)
    


    Now execute the UDF:
    
    SELECT editc(field_1, 15, 3, 
    'P') FROM qtemp.test
    

    It's return a VARCHAR(256), but I'd like to return something as:

    
    SELECT cast(editc(field_1, 15, 3, 
    'P') as 
    
    char(17)) FROM qtemp.test
    


    In this case n= 15 p=3.
    But if i change the lenght of field_1 in decimal(18, 5) , i must change SQL as: cast(editc(field_1, 18, 3, 'P') as char(20))
    I'd like to create a new UDF that understand the lenght of the field e return the right lenght.
  • LuisRodriguez
    LuisRodriguez
    15 Posts

    Re: cast in return into UDF

    ‏2012-04-11T15:35:10Z  
    I try:

    Create a table:
    <pre class="jive-pre"> create table qtemp.test ( field_1 decimal(15, 3) ) </pre>

    some value:
    <pre class="jive-pre"> INSERT INTO QTEMP.TEST VALUES(1154,256) INSERT INTO QTEMP.TEST VALUES( -154,238) </pre>

    Now execute the UDF:
    <pre class="jive-pre"> SELECT editc(field_1, 15, 3, 'P') FROM qtemp.test </pre>
    It's return a VARCHAR(256), but I'd like to return something as:

    <pre class="jive-pre"> SELECT cast(editc(field_1, 15, 3, 'P') as char(17)) FROM qtemp.test </pre>

    In this case n= 15 p=3.
    But if i change the lenght of field_1 in decimal(18, 5) , i must change SQL as: cast(editc(field_1, 18, 3, 'P') as char(20))
    I'd like to create a new UDF that understand the lenght of the field e return the right lenght.
    > Matteo_Ceriani wrote:
    > I try:
    >
    > Create a table:
    >
    
    > create table qtemp.test > ( >  field_1 decimal(15, 3) > ) >
    

    >
    > some value:
    >
    
    > INSERT INTO QTEMP.TEST VALUES(1154,256) > INSERT INTO QTEMP.TEST VALUES( -154,238) >
    

    >
    > Now execute the UDF:
    >
    
    > SELECT editc(field_1, 15, 3, 
    'P') FROM qtemp.test >
    

    > It's return a VARCHAR(256), but I'd like to return something as:
    >
    >
    
    > SELECT cast(editc(field_1, 15, 3, 
    'P') as 
    
    char(17)) FROM qtemp.test >
    

    >
    > In this case n= 15 p=3.
    > But if i change the lenght of field_1 in decimal(18, 5) , i must change SQL as: cast(editc(field_1, 18, 3, 'P') as char(20))
    > I'd like to create a new UDF that understand the lenght of the field e return the right lenght.
    Matteo,

    Why not simply use a long enough length factor (say, 18 or 20)?
    Also, as a reply to your orignal 2011 post, B. Hauser wrote a simple solution for your problem, in the proviso you only wanted to right-adjust your results. This should be faster than Bruce's EDITC UDF.

    A third option would be using the LENGTH SQL function against your numeric field. This function returns the length of the field so for a decimal(15, 3) column the result would be 8 (decimal = packed field), and for a numeric(15, 3) (zoned) column the asnwer would be, of course, 15.

    Something like:
    editc(field_1, length(field_1), 3, 'P') -- for numeric columns
    editc(field_1, length(field_1)*2, 3, 'P') -- for decimal columns

    Hope this helps.

    Regards,
    Luis
  • Matteo_Ceriani
    Matteo_Ceriani
    8 Posts

    Re: cast in return into UDF

    ‏2012-04-12T07:08:39Z  
    Luis,

    I must have explained wrong, my curiosity is if you can create a function that returns a char I fixed depending on the length (n) that step.
    For instance: cast (editc (field_1, 15, 3, 'P') as char (17))
    EDITC returns a VARCHAR (256), after the cast becomes a CHAR (17), I would like to simulate the cast in the feature so that I have the variable returns the length I want.

    The EDITC is perfect and it works great.
    I do not know if now I have explained myself better.
  • LuisRodriguez
    LuisRodriguez
    15 Posts

    Re: cast in return into UDF

    ‏2012-04-12T15:56:26Z  
    Luis,

    I must have explained wrong, my curiosity is if you can create a function that returns a char I fixed depending on the length (n) that step.
    For instance: cast (editc (field_1, 15, 3, 'P') as char (17))
    EDITC returns a VARCHAR (256), after the cast becomes a CHAR (17), I would like to simulate the cast in the feature so that I have the variable returns the length I want.

    The EDITC is perfect and it works great.
    I do not know if now I have explained myself better.
    > Matteo_Ceriani wrote:
    > Luis,
    >
    > I must have explained wrong, my curiosity is if you can create a function that returns a char I fixed depending on the length (n) that step.
    > For instance: cast (editc (field_1, 15, 3, 'P') as char (17))
    > EDITC returns a VARCHAR (256), after the cast becomes a CHAR (17), I would like to simulate the cast in the feature so that I have the variable returns the length I want.
    >
    > The EDITC is perfect and it works great.
    > I do not know if now I have explained myself better.

    Matteo,

    If I understood your problem correctly (my native language is Spanish, and sometimes my translating circuits go haywire :-) ) I think that the answer to your problems is that you can't have a function return different CHAR lengths as their returning values. That is, you can't have EDITC return CHAR(15) on one result and CHAR(17) on another result.

    That's the main idea behind VARCHAR. The length of the value is the effective length of the data contained for that item. For example, if you write LENGTH(EDITC (FIELD_1, 17, 3, 'P')) the result would be 19 (17 pos for EDITC + 2 pos for Varchar).

    HTH,

    Luis Rodriguez
  • Matteo_Ceriani
    Matteo_Ceriani
    8 Posts

    Re: cast in return into UDF

    ‏2012-04-13T09:43:34Z  
    Luis,

    Surely your English is better than mine =)

    I thought that was not already possible, but I did not find documentation about it and having the curiosity to know whether or not it was possible, I turned to the forum, where I always find a good answer.

    I started from the fact that SQL is the CAST function that does that and I was wondering if you could fake it well within my code to create better UDF and use this as inspiration to learn something new.

    Thank you so much for the help.
    Matteo
  • LuisRodriguez
    LuisRodriguez
    15 Posts

    Re: cast in return into UDF

    ‏2012-04-13T12:41:55Z  
    Luis,

    Surely your English is better than mine =)

    I thought that was not already possible, but I did not find documentation about it and having the curiosity to know whether or not it was possible, I turned to the forum, where I always find a good answer.

    I started from the fact that SQL is the CAST function that does that and I was wondering if you could fake it well within my code to create better UDF and use this as inspiration to learn something new.

    Thank you so much for the help.
    Matteo
    > Matteo_Ceriani wrote:
    > Luis,
    >
    > Surely your English is better than mine =)
    >
    > I thought that was not already possible, but I did not find documentation about it and having the curiosity to know whether or not it was possible, I turned to the forum, where I always find a good answer.
    >
    > I started from the fact that SQL is the CAST function that does that and I was wondering if you could fake it well within my code to create better UDF and use this as inspiration to learn something new.
    >
    > Thank you so much for the help.
    > Matteo

    Matteo,

    Anytime. You can find a nice Redbook about UDFs here:

    http://www.redbooks.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246503.html?Open

    It is somewhat dated (2006), but I have found it a good reference text.

    Best Regards,

    Luis