EXEC SQL insert null date

[Former Member]

Hello,

Could anyone please tell me how I can insert a null date into an oracle table dynamically? Here are my codes:

DATA: LV_KEY(10),

LV_DATE TYPE D.

TRY.

EXEC SQL.

INSERT INTO Z_ORACLE_TABLE

( KEY_FIELD, OPTION_DATE )

VALUES

( :LV_KEY, TO_DATE( :LV_DATE, 'YYYYMMDD' ) )

ENDEXEC.

ENDTRY.

Somehow, Oracle gives an error if LV_DATE is initial ('00000000'), saying the date is not between -49xx to ....

I could have done the following but it is kind of dumb:

TRY.

IF :LV_DATE IS INITIAL.

EXEC SQL.

INSERT INTO Z_ORACLE_TABLE

( KEY_FIELD, OPTION_DATE )

VALUES

( :LV_KEY, NULL )

ENDEXEC.

ELSE.

EXEC SQL.

INSERT INTO Z_ORACLE_TABLE

( KEY_FIELD, OPTION_DATE )

VALUES

( :LV_KEY, TO_DATE( :LV_DATE, 'YYYYMMDD' ) )

ENDEXEC.

ENDTRY.

Thanks in advance for any suggestions.

[SuhaSaha]

Hello,

I think in the Oracle DB table by default the date is defined as NULL & not as 0000000 as in ABAP which might be causing this error.

BR,

Suhas

[Former Member]

Suhas, I am aware the default value in Oracle is NULL. So my question is how to pass a dynamic NULL to the table without the IF ELSE statement.

[SuhaSaha]

Hello,

In SAP the default value for date-type variable is 0000000 & for character is (space). I dont think you have any data-type which has NULL as default value.

The way you have coded looks the only relevant solution. Other SDNers may have better solutions.

BR,

Suhas

[Former Member]

Thanks Suhas.

The solution is ok if there is just one date to be inserted into the table. But if there are 3 or more dates, there will be 8 or more combinations:

IF :LV_DATE1 IS INITIAL AND :LV_DATE2 IS INITIAL AND :LV_DATE3 IS INITIAL.

EXEC SQL.

INSERT INTO Z_TABLE

(... , DATE1, DATE2, DATE3)

VALUES

(... , NULL, NULL, NULL)

ENDEXEC.

ELSEIF :LV_DATE1 IS NOT INITIAL AND :LV_DATE2 IS INITIAL AND :LV_DATE3 IS INITIAL.

EXEC SQL.

INSERT INTO Z_TABLE

(... , DATE1, DATE2, DATE3)

VALUES

(... , TO_DATE( :LV_DATE1, 'YYYYMMDD', NULL, NULL)

ENDEXEC.

ELSEIF :LV_DATE1 IS NOT INITIAL AND :LV_DATE2 IS NOT INITIAL AND :LV_DATE3 IS INITIAL.

EXEC SQL.

INSERT INTO Z_TABLE

(... , DATE1, DATE2, DATE3)

VALUES

(... , TO_DATE( :LV_DATE1, 'YYYYMMDD', TO_DATE( :LV_DATE2, 'YYYYMMDD', NULL)

ENDEXEC.

ELSEIF....

... on and on...

ENDIF.

[former_member1159502]

Just had the same problem, and I have a table with 8 date fields. I decided to do an INSERT without the date fields (where the DB field is NULLABLE) and then make an UPDATE for each date field if the ABAP value is NOT INITIAL.

This solution is ok as long as DB performance is not important.

Edited by: Martin Lermen on Jul 14, 2011 4:10 PM

[Clemenss]

Hi Sau Tong Calvin,

don't you mess up INITIAL ans NULL values.

In ABAP, every data type has an initial value. As opposed to other programming languages, all variables are set to their initial value before use, numeric fields get 0, TYPE N which is numeric character will take 0 in all places, strings are empty and character fields are space. ABAP date fields are type N technically, so will be '00000000'.

You can store as this using open SQL.

NULL value means there is nothing stored for this field - this may save disk space if only few records have values stored for the field. But it will make problems in a WHERE clause of selection because SPACE matches initial values but not NULL values and opposite.

I don't know too much about native EXEC SQL because I never needed in the last decade. Hopefully you can solve the task using ABAP open SQL.

Regards,

Clemens

[Former Member]

Use decode function in sql statement where you have to_date function.

decode(abap_date, '00000000', null, to_date(abap_date, 'YYYYMMDD))

[former_member222709]

Hi Sau Tong,

This solution may not be the right way of doing it, but, you can achieve sending a 'NULL' value as follows:

1. Define the date field in the structure as character variable of length 8

2. Check the date field for value '00000000'.

3. If it is '00000000', clear the field

If you pass this table or work area to SQL, it should consider it as NULL as the cleared character variable will not have SPACE.

Regards,

Pranav.

[Former Member]

Tried that... didn't work.

Gets ORA-1847 error. Complaining that days of months must be valid...

When I tried your solution, found it's sending '          ' over, and causing problems.