Message Mapping JDBC Doubt

[Former Member]

Hello everybody,

I hve a doubt abot this SQL_Query can be possible

- <CCONTABLE action="SQL_QUERY">

<TABLE>CCONTABLE</TABLE>

<access>SELECT C.CUENTA_CONTABLE,C.INDICADOR_IVA,C.CVE_POSFINAN,C.APLICA_IVA FROM CCONTABLE C WHERE '0000107536' BETWEEN C.CLIENTE_INI AND C.CLIENTE_FIN AND c.CLAVE_CARGOCRED = '$CLAVE_CARGOCRED$' AND nvl('32011',4043) BETWEEN C.PRODUCTO_INI AND C.PRODUCTO_FIN AND C.IVA = '$IVA$AND '05-NOV-2008' BETWEEN C.FINIV AND C.FFINV</access>

- <key1>

<CLAVE_CARGOCRED>990</CLAVE_CARGOCRED>

<IVA>15.00</IVA>

</key1>

- <key1>

<CLAVE_CARGOCRED>701</CLAVE_CARGOCRED>

<IVA>15.00</IVA>

</key1>

- <key1>

<CLAVE_CARGOCRED>600</CLAVE_CARGOCRED>

<IVA>15.00</IVA>

</key1>

- <key1>

<CLAVE_CARGOCRED>704</CLAVE_CARGOCRED>

<IVA>15.00</IVA>

</key1>

- <key1>

<CLAVE_CARGOCRED>712</CLAVE_CARGOCRED>

<IVA>15.00</IVA>

</key1>

- <key1>

<CLAVE_CARGOCRED>890</CLAVE_CARGOCRED>

<IVA>15.00</IVA>

</key1>

</CCONTABLE>

as you can see I need many keys, now is this possible?, thanks in advance for your suggestions.

Regards,

Julio Cesar

[Former Member]

Thanks everyone, I solved this problem by using JDBC LookUp to the database in the message mapping

[Former Member]

Thanks Anand,

yes the wuery in the db is returning more than one row, now the payload in SXMB_MONI in the request is as I posted it before, for the payload of the response is returning only one row.

[justin_santhanam]

Julio,

to see the actual SQL query generated by XI, please go to ur receiver communication channel in the advanced tab add the following parameters.

logSQLStatement true

raj.

[Former Member]

Thanks for your answers, yes the space was an error on writing the thread but it has it, I'm not getting any errors, but I'm not getting what I expect cause I'm getting only one row as result, and I want to obtain a row for each key1, thanks again for your answers.

[Former Member]

check the query if it is returning more results by executing on the db without xi.

If yes, then check ur mapping...

check the sxmb_moni to see the payload...

[Former Member]

> <access>SELECT C.CUENTA_CONTABLE,C.INDICADOR_IVA,C.CVE_POSFINAN,C.APLICA_IVA FROM CCONTABLE C WHERE '0000107536' BETWEEN C.CLIENTE_INI AND C.CLIENTE_FIN AND c.CLAVE_CARGOCRED = '$CLAVE_CARGOCRED$' AND nvl('32011',4043) BETWEEN C.PRODUCTO_INI AND C.PRODUCTO_FIN AND C.IVA = '$IVA$ AND '05-NOV-2008' BETWEEN C.FINIV AND C.FFINV</access>

You keys are incorrect.. It should be

<key>

<CLAVE_CARGOCRED>Value</CLAVE_CARGOCRED>

<IVA>05-AUG-2008</IVA>

</key>

Give the space between '$IVA' and the AND in the query

[Former Member]

Hi,

if the values given in your query are fixed, then write a view on database and call that view in adapter, it will make life easy.

Regards,

Sukarna.

[justin_santhanam]

Julio,

Are u getting any errrors? Or you are not getting what you expected?

raj,