Skip navigation
Up to Discussions in Enzee Universe
19073 Views 4 Replies Latest reply: Oct 30, 2009 10:18 AM by Shawn Fox RSS
Hemant Kumar New Enzee
Hemant Kumar
73 posts since
Jul 30, 2009
Currently Being Moderated

Oct 28, 2009 2:41 AM

Error to use procedure to check date validation?

Hi Folks,

I created below proc to check the date in a varchar field.

Create or replace procedure dbf_dt_valid (varchar(10)) returns DATE LANGUAGE NZPLSQL AS
begin_proc
declare
v_ret_dt date;
v_num int;
v_dt alias for $1;
BEGIN
v_ret_dt := TO_DATE(v_dt,'YYYYMMDD');

Return v_ret_dt;
Exception
when others then
Return NULL;
END;
END_PROC;

If I use select dbf_dt_valid ('20081212') it gives 2008-12-12 which is correct value. But If I use

select dbf_dt_valid(a.sk_maturity_date_id)
from fact_investment a

then getting below error:

 

Error: ERROR: 256 : Code generation failure

(State:HY000, Native Code: 6C)

Please help me, where I am wrong? Or If I need to use function then what will be the syntax to create the function.

Regards

Hemant

Tags: netezza
  • Shawn Fox Enzee Exraordinaire
    Shawn Fox
    1,333 posts since
    Aug 15, 2006
    Currently Being Moderated
    1. Oct 28, 2009 11:09 AM (in response to Hemant Kumar)
    Re: Error to use procedure to check date validation?

    I don't have 4.6 installed and haven't heavily researched stored procedures, but from my understanding stored procedures can't be used within a SQL statement.  You have to use the UDX method to create a function which can be used in SQL.  A stored procedure can execute a SQL statement, process the results, and output the results to a table, but cannot be used within a SQL statement itself.

     

    A UDX has to be written using C++ code, compiled using nzudxcompile, and then create a function in the database using the .o files produced by nzudxcompile.

  • Shawn Fox Enzee Exraordinaire
    Shawn Fox
    1,333 posts since
    Aug 15, 2006
    Currently Being Moderated
    2. Oct 28, 2009 11:08 AM (in response to Hemant Kumar)
    Re: Error to use procedure to check date validation?

    UDX documentation is also available on the NDN site:

     

    https://developer.netezza.com/wiki/img_auth.php/f/fe/Netezza_onstream_dev_guide.pdf

  • Shawn Fox Enzee Exraordinaire
    Shawn Fox
    1,333 posts since
    Aug 15, 2006
    Currently Being Moderated
    3. Oct 28, 2009 11:14 AM (in response to Hemant Kumar)
    Re: Error to use procedure to check date validation?

    One other piece of advice on UDX, you have to do all your development and testing in a non production environment.  The UDX code on the 10000 series hardware runs in the same memory area as everything else.  So if your code has any bugs in it and accesses memory that it shouldn't you can crash the entire database.  I managed to crash our dev system within about 15 minutes of experimentation with the UDX code.

  • Shawn Fox Enzee Exraordinaire
    Shawn Fox
    1,333 posts since
    Aug 15, 2006
    Currently Being Moderated
    4. Oct 30, 2009 10:18 AM (in response to Hemant Kumar)
    Re: Error to use procedure to check date validation?

    If you have the SQL Extenstions toolkit installed there is another way to do this. Using the regexp_like function you can validate that a string matches a specific regular expression before converting it to a date:

     

    select case when regexp_like(a.sk_maturity_date_id, '^[0-9]{4}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])$') then to_date(a.sk_maturity_date_id,'YYYYMMDD') else NULL end
    from fact_investment a;

     

    The above is not perfect because it would fail on a date such as 20090231 (feb 31 is not a valid date).  It just validates that the month is 01 through 12 and the day is 01 through 31.  It would be very hairy to get it to work for any date because I'd have to take leap years into account to determine how many days feb can have.  You could also just do a much simpler validation to test if the date_id is exactly 8 digits:

     

    select case when regexp_like(a.sk_maturity_date_id, '^[0-9]{8}$') then to_date(a.sk_maturity_date_id,'YYYYMMDD') else NULL end
    from fact_investment a;

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points