How to handle NULL values in JSL script

gerardmcl

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

 

michaelhaslam_p

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