My program crashes when I try to read a NULL value using sqlite in C-CodePudding

I have tried to use the below statement to check if the value present in the given fieldname for a record is NULL or not, if it is NULL then put 0 else return the value present in the fieldname. It shows syntax error.

SELECT [structname.fieldname], isnull([structname.fieldname],0) FROM tablename WHERE condition;

Can you please let me know where am I going wrong?

I have also tried the below statement

SELECT [structname.fieldname], CASE WHEN [structname.fieldname] IS NULL THEN 0 ELSE [structname.fieldname] END FROM tablename WHERE condition;

For the above statement, the program crashes when it encounters a NULL value. Can you please tell me how else can I check if a value of a particular record is NULL or not and then read the value if it is not NULL else read it as 0

CodePudding user response：

You are not using correctly the square brackets.

The column names should be written like:

[structname].[fieldname]

assuming that [structname] is the name or the alias of the table where fieldname comes from, and not like:

[structname.fieldname]

which would be ok only if the column's name is actually structname.fieldname.

What you need can be done with the function COALESCE():

SELECT COALESCE([fieldname], 0) FROM tablename