Subscribe Bookmark RSS Feed

How to handle NULL values in JSL script

gerardmcl

Community Trekker

Joined:

Mar 15, 2013

Hi,

I am very new to JSL, my background is pl/sql.

I have an INSERT (see below) to an Oracle table that is failing on some rows that have null values in a few of the columns.

In Oracle I would just use NVL e.g. NVL(STD_ERROR,0)

How can I do this in JSL? In this case it's just column7 that is causing the problem but I would wrap all the columns in the function(?).

Any help is greatly appreciated.

for (i = 1, i <= nrows(dt), i++,

       sql_statement =

"INSERT INTO TBL_COMBINED_STG " ||

"(Y_AXIS, PROD, LOT, TERM, BIAS, ESTIMATE, STD_ERROR, TRATIO, PROB_T) " ||

"VALUES(" ||ULL

"'" || char(column(1)) || "', " ||

"'" || char(column(2)) || "', " ||

"'" || char(column(3)) || "', " ||

"'" || char(column(4)) || "', 0," ||

//char(column(5)) || ", " ||

char(column(6)) || ", " ||

char(column(7)) || ", " ||

char(column(8)) || ", " ||

char(column(9)) || ") ";

// JMP will add a commit to the insert statement.

      Execute SQL(dbc, sql_statement);

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

GerardMcL:

For numeric data, the test in JMP is "Is Missing ()".  Use the Is Missing function to build your SQL statement,  When the Is Missing is true, then use NULL instead.

So, here is one way to do it for small data sets:

You would start out with a basic string for the SQL statement and then loop through all values, testing each value.

sql statement = beginning stuff || " VALUES("

For (I_Counter=1, I_Counter<=NCols(), I_Counter++,

     If (IsMissing(thing I want to test),

          sql statement = sql statement || "NULL, "

     ,

          sql statement = sql statement || Char(Value) || ", "

     )

);

sql statement = Left(SQL Statement, length(sql statement)-2) // removes the final comma

sql statement = sql statement + stuff to close it

Alternately, you can take a numeric column and change the Data Type to Character.  In this case, missing values will change to strings of length 0.  However, I believe that Oracle recommends against the practice of using zero length strings to represent NULL.

Michael Haslam

3 REPLIES
Solution

GerardMcL:

For numeric data, the test in JMP is "Is Missing ()".  Use the Is Missing function to build your SQL statement,  When the Is Missing is true, then use NULL instead.

So, here is one way to do it for small data sets:

You would start out with a basic string for the SQL statement and then loop through all values, testing each value.

sql statement = beginning stuff || " VALUES("

For (I_Counter=1, I_Counter<=NCols(), I_Counter++,

     If (IsMissing(thing I want to test),

          sql statement = sql statement || "NULL, "

     ,

          sql statement = sql statement || Char(Value) || ", "

     )

);

sql statement = Left(SQL Statement, length(sql statement)-2) // removes the final comma

sql statement = sql statement + stuff to close it

Alternately, you can take a numeric column and change the Data Type to Character.  In this case, missing values will change to strings of length 0.  However, I believe that Oracle recommends against the practice of using zero length strings to represent NULL.

Michael Haslam

gerardmcl

Community Trekker

Joined:

Mar 15, 2013

Thank You very much for your help

pmroz

Super User

Joined:

Jun 23, 2011

Here's another approach.  Create a new function that acts similarly to Oracle's NVL function with a slight twist - it returns a character string.  Notice that I'm returning the empty string "" for your character variables, and returning "NULL" for numeric values.

/*

nvl_char: convert number to a character.  If missing return the second value

Arguments:

one_value        Numeric value to convert to a character

value_if_null    Character string to return if one_value is missing

*/

nvl_char = Function( {one_value, value_if_null},

      {Default Local},

    if (is missing(one_value),

    // then

        value_if_null,

    // else

        char(one_value);

    );

);

// Test out the function

nvl_char(123, "NULL");

nvl_char(., "NULL");

nvl_char(., "");

for (i = 1, i <= nrows(dt), i++,

       sql_statement =

"INSERT INTO TBL_COMBINED_STG " ||

"(Y_AXIS, PROD, LOT, TERM, BIAS, ESTIMATE, STD_ERROR, TRATIO, PROB_T) " ||

"VALUES(" ||

"'" || nvl_char(column(1)[i], "") || "', " ||

"'" || nvl_char(column(2)[i], "") || "', " ||

"'" || nvl_char(column(3)[i], "") || "', " ||

"'" || nvl_char(column(4)[i], "") || "', 0," ||

//char(column(5)) || ", " ||

nvl_char(column(6)[i], "NULL") || ", " ||

nvl_char(column(7)[i], "NULL") || ", " ||

nvl_char(column(8)[i], "NULL") || ", " ||

nvl_char(column(9)[i], "NULL") || ") ";

// JMP will add a commit to the insert statement.

      Execute SQL(dbc, sql_statement);

);