How to handle NULL values in JSL script

[#1]

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

[#2 · Accepted 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]

Thank You very much for your help

[#4]

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