SQL Exec null variable

[luscruz]

Hi,

I need to send a variable in a sql statement that can be null in some cases.

How can I send that?

Here is an example code:

lv_date = COND #( WHEN lv_test EQ 'ABC' THEN |2023-11-13 00:00:00 AM| ELSE |NULL| ).

EXEC SQL.
	UPDATE [Table]
		SET [Date] = :LV_DATE
		WHERE [Id_Linha] = 1
ENDEXEC.

When I try to send the null value I receive an error:

"Conversion failed when converting date and/or time from character string."

It seems it's convertion to string 'NULL'.

Can anyone help me?

Thanks,

Luis

[ThorstenHoefer]

Hi Luis,

try to use two conditions:

IF 1 = 2 then
EXEC SQL.
	UPDATE [Table]
		SET [Date] = :LV_DATE
		WHERE [Id_Linha] = 1
ENDEXEC.
else.

EXEC SQL.
	UPDATE [Table]
		SET [Date] = null
		WHERE [Id_Linha] = 1
ENDEXEC.

endif.

[Sandra_Rossi]

IF lv_test EQ 'ABC'.

LV_date = |2023-11-13 00:00:00 AM|.
EXEC SQL.
	UPDATE [Table]
		SET [Date] = :LV_DATE
		WHERE [Id_Linha] = 1
ENDEXEC.

else.

EXEC SQL.
	UPDATE [Table]
		SET [Date] = null
		WHERE [Id_Linha] = 1
ENDEXEC.

endif.

[Sandra_Rossi]

Even the value |2023-11-13 00:00:00 AM| could lead to the error:

"Conversion failed when converting date and/or time from character string."

What is the type of [Date] of [table]?

[luscruz]

Thank you.

That solution works, but I was looking for something without that IF.

Because I gave you a simple example, but in my update query I have several fields and I wanted a better way to do instead of several IF's and several updates.

Thanks

[Sandra_Rossi]

Please use the COMMENT button for comments, asking for complements, adding details, replying to a comment or a proposed solution or to the OP question, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.

[Sandra_Rossi]

You may use pure SQL solution like this:

EXEC SQL.
	UPDATE [Table]
		SET [Date] = CASE WHEN :lv_test = 'ABC' THEN :lv_date ELSE null END
		WHERE [Id_Linha] = 1
ENDEXEC.

Refer to your database reference guide for exact syntax.