can't update a sql-table with a space

[matthias_prller]

Hello,

In a transaktion I'm getting some Values from a SAP-ERP System via JCO.

I update a sql-table with this values with a sql-query command.

But sometimes the values I get from SAP-ERP are empty (space) and I'm not able to update the sql-table because of a null-value exception. (The column doesn't allow null-values). It seems that MII thinks null and space are the same.

I tried to something like this when passing the value to the sql-query parameter but it didn't work:

stringif( Repeater_Result.Output{/item/SCHGT} == "X", "X", " ")

stringif( Repeater_Result.Output{/item/SCHGT} == "X", "X", " ")

this works but I don't want to have a "_"

stringif( Repeater_Result.Output{/item/SCHGT} == "X", "X", "_")

Any suggestions?

thank you.

Matthias

[Former Member]

Hello Matthias,

can you provide also your query?

Regards

Pedro

[matthias_prller]

Hello Pedro,

here is the update statement (it is a copy of the famous marc table from our SAP ERP)

update marc 
set 
LGort = '[Param.3]',
dispo = '[Param.4]',
schgt = '[Param.5]',
dismm = '[Param.6]',
sobsl = '[Param.7]',
fevor = '[Param.8]'
where matnr = '[Param.1]' and werks = '[Param.2]'

and if I test this query with a value for each parameter everything is ok, but not if I have a space as a parameter.

Regards

Matthias

[Former Member]

Try the sql space function, e.g. SPACE(1)

[matthias_prller]

The problem is Oracle doesn't know the space function. But it knows a similar function: NVL --> replaces a null value with something else. So this statement works fine for me:

update marc set 
LGort = '[Param.3]',
dispo = '[Param.4]',
schgt = NVL('[Param.5]', ' '),
dismm = '[Param.6]',
sobsl = NVL('[Param.7]',' '),
fevor = '[Param.8]'
where matnr = '[Param.1]' and werks = '[Param.2]'

If Param.5 or Param.7 is null Oracle replaces it with a space in every other case it is the parameter itself.

Christian, thank you for your hint with the space function. So I remembered the NVL-function.

Regards

Matthias