How to use SQLExec with a criteria using IN or NOT IN

SQLExec is one of the most powerful peoplecode command to execute your SQL statement. Of course, there are several other ways to accomplish the same thing, such as CreateSQL, GetSQL, etc...If you ask me what I prefer, it really comes down to whether I need to loop through multiple rows vs single row from table. If multiple rows, CreateSQL and GETSQL will do a fantastic job, otherwise SQLExec is very efficient in fetching single row data.

Now and then, you will use IN or NOT IN SQL criteria to fetch a row from table in peoplecode. This example will use SQLExec technique.

Let say you have a string of values like this: 'A','B','C'. You want to use insert this string as a parameter in the SQLExec.

Bad Example:

Local string &in_values = "'A','B','C'";
Local string &out_val;

SQLExec("Select min(field1) from ps_table_1 Where field1 NOT IN (:1)", &in_values, &out_val);

Result: Bad


Good Example:

1 Local string &in_values = "'A','B','C'";
2 Local string &sql_cmd;
3 Local string &out_val;
4 
5 &sql_cmd = ("Select min(field1) from ps_table_1 Where field1 NOT IN (" | &in_values | ")"; 
6 
7 SQLExec(&sql_cmd, &out_val);

Result: Good