cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
gerardmcl
Level II

How to handle NULL values in JSL script

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
michaelhaslam_p
Level III

Re: How to handle NULL values in JSL script

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

View solution in original post

3 REPLIES 3
michaelhaslam_p
Level III

Re: How to handle NULL values in JSL script

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
Level II

Re: How to handle NULL values in JSL script

Thank You very much for your help

pmroz
Super User

Re: How to handle NULL values in JSL script

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);
);