SQL to JSON with null value fields

[pgd]

I am getting different results when i run a SQL statement from a simple test program (via Test RPG Call) and when using the same statement in a (modified) AutoCode grid inquiry program.

One of the fields used to join with another table has a null value.
The simple program returns all records but the grid inquiry program stops when it encounters the record with the null value field and displays only the first 2 records.

This makes me think of the SQL to JSON issue i had and we discussed in a confcall last month.

I have attached the source code of the simple program.

(we use Valence version 3.0.20110527.0).

[sean.lanktree]

In your "autocode" program, is there an "sql set options" statement? If so, what is it?

[pgd]

Yes, it is "set option srtseq=*langidshr,datfmt=*iso,closqlcsr=*endmod".

Thanks,
Peter

[sean.lanktree]

So if I have this clear, both of your programs are calling the same procedure (vvOut_execSqlToJson) and passing the same SQL statement yet getting different results? The only difference I can see right now would be in the sql options set in the auto code program. Maybe try removing that statement and see what you get after compiling?

[pgd]

I am calling the "simple program" via the Test RPG option but using the same sql statement and vvOut_execSqlToJson procedure.

I have removed the sql option and recompiled the program but the result is unchanged (no more records after the record with the null value).

[richard.milone]

Please email your telephone contact info to support@cnxcorp.com and someone from our support staff will give you a call to review the problem.

[JPinTO]

Use COALESCE in the SQL to ensure you don't get null values.

- JP