topic Re: SQL Exec null variable in Application Development and Automation Discussions

SQL Exec null variable

luscruz — Mon, 13 Nov 2023 11:28:55 GMT

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

Re: SQL Exec null variable

ThorstenHoefer — Mon, 13 Nov 2023 11:35:10 GMT

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.

Re: SQL Exec null variable

Sandra_Rossi — Mon, 13 Nov 2023 13:14:51 GMT

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.

Re: SQL Exec null variable

Sandra_Rossi — Mon, 13 Nov 2023 13:15:45 GMT

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]?

Re: SQL Exec null variable

luscruz — Mon, 13 Nov 2023 14:37:12 GMT

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

Re: SQL Exec null variable

Sandra_Rossi — Mon, 13 Nov 2023 16:06:33 GMT

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.

Re: SQL Exec null variable

Sandra_Rossi — Mon, 13 Nov 2023 16:11:41 GMT

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.