JDBC receiver with select is null

[Former Member]

Hi,

I need to do a mapping between SAP and an Oracl database, but there is a issue that I can not solve.

I need that the mapping do a call like:

SELECT field1 field2 FROM table1 WHERE field1 = 'value1' and field2 = 'value2' and field3 is null.

I can do all steps except include the "is null" in the where clausule, I have tried using the key attributes but seems that it doesn't work like I need.

Could someboy help me? How can get the SQL sentence that the JDBC adapter generate.

Best regards, Raú

[former_member208856 · Accepted solution]

The value should be IS NULL

write IS NULL in capital letter.

[Former Member · Accepted solution]

I had tried it but i have this error

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. TABLE1' (structure 'StatementSQL'): java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected

this is the xml generate by adapter:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns1:MT_TABLE1 xmlns:ns1="urn:xxxxxxxxxxx">

- <StatementSQL>

- <TABLE1 action="SELECT">

<table>TABLE1</table>

- <access>

<FIELD1 />

<FIELD2 />

</access>

- <key>

<FIELD3>074</FIELD3>

<FIELD4>03083</FIELD4>

<DATE1>IS NULL</DATE1>

</key>

</TABLE1>

</StatementSQL>

</ns1:MT_TABLE1>

Thanks

[former_member208856 · Accepted solution]

you are passing IS NULL in a field.

Pass ' ' in query for DATE1

SELECT field1 field2 FROM table1 WHERE field1 = 'value1' and field2 = 'value2' and field3 = ' '.

[Former Member · Accepted solution]

I agree with Sandeep,

You need to pass '' (empty value) to field on where condition.

[Former Member · Accepted solution]

The adapter generates this XML:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns1:MT_TABLE1 xmlns:ns1="urn:xxxxxxxxxxx">

- <StatementSQL>

- <TABLE1 action="SELECT">

<table>TABLE1</table>

- <access>

<FIELD1 />

<FIELD2 />

</access>

- <key>

<FIELD3>074</FIELD3>

<FIELD4>03083</FIELD4>

<DATE1>

</key>

</TABLE1>

</StatementSQL>

</ns1:MT_TABLE1>

but there are no records return for database and thera are records in database for this selection.

Thanks

[markangelo_dihiansan · Accepted solution]

Hello,

You can try using SQL_QUERY instead

http://help.sap.com/saphelp_nwpi71/helpdata/EN/44/7b7855fde93673e10000000a114a6b/content.htm

In access, write your statement as was suggested above:

SELECT field1 field2 FROM table1 WHERE field1 = 'value1' and field2 = 'value2' and field3 IS NULL.

Hope this helps,

Mark

[Former Member · Accepted solution]

The SQL_QUERY works fine, but I am trying solve it with the SELECT action (project requirements).

Do you now if there is a solution?

Thanks

[former_member208856 · Accepted solution]

Is it Inbound scenario OR Outbound ?

As per first question, it is Outbound Scenario, where you are using Select for picking data from sql system.

If it is in ACTION, it means it is Inbound Scenario.

Pass the CONSTANT value ' ' in Mappiing for Date field for Inbound scenario.

[baskar_gopalakrishnan2 · Accepted solution]

SQL_Query will work. For your requirement, try to specify Date field as follows

<DATE1></DATE1>

[Former Member · Accepted solution]

I agree with Baskar, as i said in the post above.

[Former Member · Accepted solution]

I have tried to do it but when I put the blank value like constant in the mapping the xml is

<DATE1>

and not

<DATE1></DATE1>

If a try mapped other values I have a mapping error because the value is not a date value.

How can solve it?

[former_member463616]

Hi,

>>If a try mapped other values I have a mapping error because the value is not a date value.

>>How can solve it?

Pass the CONSTANT value as 0 in Mapping for Date field.

I hope that the scenario is work fine.

[markangelo_dihiansan]

Hello,

In integration directory, under your jdbc receiver, make sure you set Interpretation of Empty String Values to NULL value.

Hope this helps,

Mark

[Former Member]

Thanks Mark, but i have this parametrization done in the adapter.

Best regards

[Former Member]

If it doesn't work, You can solve with a stored procedure (write your select into stored procedure, and call it from jdbc receiver structure with EXECUTE command).

[Former Member]

is null has to be "IS NULL"?

Edited by: Bai Li on Jun 3, 2011 8:59 AM