Skip navigation
Up to Discussions in TwinFin Talk
9774 Views 3 Replies Latest reply: Jul 14, 2010 11:36 AM by AC Reddy RSS
AC Reddy New Enzee
AC Reddy
3 posts since
Jul 6, 2010
Currently Being Moderated

Jul 8, 2010 4:54 PM

A UDF to Handle Empty String like a Null

Here is the source code for a UDF that I wrote (based on Oleg's example: http://www.aginity.com/Blog/tabid/182/PostID/55/Creating-a-Netezza-User-Defined-Function-(UDF).aspx )

 

SELECT TEST_NVL(null, 'world') returns 'world'

 

SELECT TEST_NVL('', 'world') returns 'world'

 

SELECT TEST_NVL('hello', 'world') returns 'hello'

 

You can use this UDF in the where clause as well:

 

select a,b from t where upper(trim (TEST_NVL (a, b)))= 'XYZ'

 

 

<?xml version="1.0" standalone="yes"?>
<UDXDataSet xmlns="http://aginity.com/NetezzaWorkbench/UDXDataSet.xsd">
  <Definition>
    <ID>-1</ID>
    <Name>TEST_NVL</Name>
    <ClassName>CTEST_NVL</ClassName>
    <ReturnDataTypeID>32</ReturnDataTypeID>
    <ReturnPrecision>255</ReturnPrecision>
    <ReturnScale>0</ReturnScale>
    <LogMask>3</LogMask>
    <Comment />
    <SourceCode>

#include "udxinc.h"
#include "udxhelpers.h"
#include "string.h"

using namespace nz::udx::dthelpers;
using namespace nz::udx;

//****************************************************************************
// This is the main Function class implementation
//****************************************************************************
class CTEST_NVL: public Udf
{
public:
static Udf *instantiate();
virtual ReturnValue evaluate();
virtual uint64 calculateSize() const;
private:
// region Property Accessors
inline bool is_arg_null_string1() const
{
  return isArgNull(0);
};
inline StringArg* get_arg_string1() const
{
  return stringArg(0);
};
inline bool is_arg_null_string2() const
{
  return isArgNull(1);
};
inline StringArg* get_arg_string2() const
{
  return stringArg(1);
};
// end region
};

//****************************************************************************
// This static function creates a new instance of the class.
Udf *CTEST_NVL::instantiate()
{
return new CTEST_NVL();
}

//****************************************************************************
// Calculates and returns the function result.
ReturnValue CTEST_NVL::evaluate()
{
       // find out input string lengths
       int len[2];
        len[0] = isArgNull(0) ? 0 : stringArg(0)-&gt;length;
        len[1] = isArgNull(1) ? 0 : stringArg(1)-&gt;length;
     
       // create an array that holds input strings
       char* *parts = new char*[2];
       parts[0] = len[0] == 0 ? NULL : stringArg(0)-&gt;data;
       parts[1] = len[1] == 0 ? NULL : stringArg(1)-&gt;data;
  
       // prepare return value object
       StringReturn *pResult = stringReturnInfo();
       pResult-&gt;data[0] = 0;
       char*pCurrent = pResult-&gt;data;
        // Note: Lengths of the string arguments passed have been set to varchar(255)
   // if the first string has a length of 1 or more, copy the first string to the return value
   // Could have used strcpy. Included string.h above
   if (len[0]&gt;0) {
    int m = 0;
     int n = len[0] -1;
      while (m &lt;= n) {
        *pCurrent= parts[0][m];
      pCurrent++;
      m++;
      }
     }
   else
     // return the second string if the first string has zero characters or is NULL.
    if (len[0] == 0) {
               if (len[1] &gt; 0)
              {
                int j = 0;
                int k = len[1] - 1;
                  while (j &lt;= k)
                     {
                           *pCurrent =  parts[1][j];
                           pCurrent++;
                      j++;
                     }
              }
      
    }
    
     

       // clean up  
       delete []parts;
      
       // specify the output string length
       pResult-&gt;size = pCurrent - pResult-&gt;data;

       // done, return the generated NK
       NZ_UDX_RETURN_STRING(pResult);
}


//****************************************************************************
// Calculates the size of the return value for generic parameter style (ANY)
uint64 CTEST_NVL::calculateSize() const
{
// TODO: replace zero with an actual return value size
return 0;
}</SourceCode>
    <UDXType>0</UDXType>
    <TestSQL>
select TEST_NVL('s','world')</TestSQL>
    <Language>CPP</Language>
    <MemoryUsage />
    <Deterministic>true</Deterministic>
    <ReturnsNullOnNullInput>false</ReturnsNullOnNullInput>
    <AggregateType>0</AggregateType>
    <Args>
      <ParentID>-1</ParentID>
      <Sequence>1</Sequence>
      <Name>string1</Name>
      <DataTypeID>32</DataTypeID>
      <Precision>255</Precision>
      <Scale>0</Scale>
      <Comment />
    </Args>
    <Args>
      <ParentID>-1</ParentID>
      <Sequence>2</Sequence>
      <Name>string2</Name>
      <DataTypeID>32</DataTypeID>
      <Precision>255</Precision>
      <Scale>0</Scale>
      <Comment />
    </Args>
  </Definition>
  <DataTypes>
    <DataTypeID>1</DataTypeID>
    <Name>BIGINT</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>2</DataTypeID>
    <Name>BOOLEAN</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>3</DataTypeID>
    <Name>BPCHAR</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>4</DataTypeID>
    <Name>BYTEINT</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>5</DataTypeID>
    <Name>CHAR</Name>
    <HasPrecision>true</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>6</DataTypeID>
    <Name>DATE</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>7</DataTypeID>
    <Name>DECIMAL</Name>
    <HasPrecision>true</HasPrecision>
    <HasScale>true</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>8</DataTypeID>
    <Name>DOUBLE</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>9</DataTypeID>
    <Name>FLOAT</Name>
    <HasPrecision>true</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>10</DataTypeID>
    <Name>INTEGER</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>11</DataTypeID>
    <Name>INTERVAL DAY</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>12</DataTypeID>
    <Name>INTERVAL DAY TO HOUR</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>13</DataTypeID>
    <Name>INTERVAL DAY TO MINUTE</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>14</DataTypeID>
    <Name>INTERVAL DAY TO SECOND</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>15</DataTypeID>
    <Name>INTERVAL HOUR</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>16</DataTypeID>
    <Name>INTERVAL HOUR TO MINUTE</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>17</DataTypeID>
    <Name>INTERVAL HOUR TO SECOND</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>18</DataTypeID>
    <Name>INTERVAL MINUTE</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>19</DataTypeID>
    <Name>INTERVAL MINUTE TO SECOND</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>20</DataTypeID>
    <Name>INTERVAL MONTH</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>21</DataTypeID>
    <Name>INTERVAL SECOND</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>22</DataTypeID>
    <Name>INTERVAL YEAR</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>23</DataTypeID>
    <Name>INTERVAL YEAR TO MONTH</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>24</DataTypeID>
    <Name>NCHAR</Name>
    <HasPrecision>true</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>25</DataTypeID>
    <Name>NUMERIC</Name>
    <HasPrecision>true</HasPrecision>
    <HasScale>true</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>26</DataTypeID>
    <Name>NVARCHAR</Name>
    <HasPrecision>true</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>27</DataTypeID>
    <Name>REAL</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>28</DataTypeID>
    <Name>SMALLINT</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>29</DataTypeID>
    <Name>TIME</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>30</DataTypeID>
    <Name>TIMESTAMP</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>31</DataTypeID>
    <Name>TIMETZ</Name>
    <HasPrecision>false</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
  <DataTypes>
    <DataTypeID>32</DataTypeID>
    <Name>VARCHAR</Name>
    <HasPrecision>true</HasPrecision>
    <HasScale>false</HasScale>
  </DataTypes>
</UDXDataSet>             

Tags: user, oracle, empty, udf, function, udx, defined, string, nvl
  • Mike Burrow New Enzee
    Mike Burrow
    1 posts since
    Jun 12, 2007
    Currently Being Moderated
    1. Jul 13, 2010 12:17 PM (in response to AC Reddy)
    Re: A UDF to Handle Empty String like a Null

    I find the following handy for treating nulls and '' the same way, this will produce the same results as your hello world example just using simple SQL:

    create table mikeb_hw(s1 varchar(255), s2 varchar(255));

    insert into mikeb_hw values ('hello', 'world');

    insert into mikeb_hw values ('', 'world');

    insert into mikeb_hw values ('hello', '');

    insert into mikeb_hw(s1) values('hello');

    insert into mikeb_hw(s2) values('world');

    select nvl(s1, 'null'), nvl(s2, 'null') from mikeb_hw;

      nvl  |  nvl

    -------+-------

    hello | world

    hello |

    hello | null

           | world

    null  | world

    (5 rows)

    select nvl(nullif(s1, ''), s2) from mikeb_hw;

      nvl

    -------

    hello

    hello

    hello

    world

    world

    (5 rows)

    select * from  mikeb_hw where nullif(s1, '') is null;

    s1 |  s2

    ----+-------

        | world

        | world

    (2 rows)

  • David Shuttleworth Active Enzee
    David Shuttleworth
    270 posts since
    May 14, 2008
    Currently Being Moderated
    2. Jul 14, 2010 3:14 AM (in response to Mike Burrow)
    Re: A UDF to Handle Empty String like a Null

    Hi Mike and welcome to the forum.. you beat me to it, I was just about to post something similar!

     

    D.

  • AC Reddy New Enzee
    AC Reddy
    3 posts since
    Jul 6, 2010
    Currently Being Moderated
    3. Jul 14, 2010 11:36 AM (in response to Mike Burrow)
    Re: A UDF to Handle Empty String like a Null

    We have complex transformations written in SQL (Oracle) with NVL in nested statements, etc. on both sides of the SELECT statement (i.e. before  FROM  and
    after WHERE).

     

    In converting the SQL code from Oracle to Netezza, we would like to do a simple global replace of NVL with say MY_NVL
     rather than editing each SQL statement. Yes, you could use nullif or a case statement to get around the issue, but the idea is
    to reduce the amount of work and avoid the possibility of introducing errors (the later is difficult given the volume of code to be converted).

     

    You can see for yourself the pain of modifying the sample SQL below using  nullif/case:


       SELECT   date_col, NVL (UPPER (trim ( NVL (s.col1, s.col2))), 'NA') ,
       TO_NUMBER (NVL((CASE WHEN x_type IN ( 'ABC')  THEN m_val
                           WHEN x_type IN ( 'DEF','GHI')  THEN p_val
                           WHEN x_type IN ('FOO', 'BAR')  THEN q_val
                           ELSE '9999' END ),'9999'),'9999' ) r_val
       NVL (INITCAP (trim ( NVL (s.col4, s.col5))), 'NA') ,
       UPPER (trim (NVL (s.col2, s.col1)))
    FROM  stage_table s

    where

    UPPER (trim (NVL (s.col8, s.col9)))= 'MYVALUE1'
    and SM_NVL (UPPER (trim ( SM_NVL (s.col10, s.col11))), 'NA')= 'MYVALUE2'

More Like This

  • Retrieving data ...

Bookmarked By (0)