cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Statament Oracle using TRUNC Command

Former Member
0 Kudos

Hi all,

I am using RFC to JDBC Scenario with syncrounous SELECT, with Oracle DB. But it doens't work, because i need to select the field DATA_TRANSACAO as below, using only the DATE, because i don't have the TIME in SAP system, to select in Receiver Database. So, i need to truncated the Date field (DATA_TRANSACAO). If i use SQL statement below in DataBase client, it will work fine, but in JDBC Adapter, doens't work.

I have tried a lot of changes, but i didn't get success!

Please, could you help me?

Thanks!

Fábio Ferri

SAP Consultant PI/ABAP/Netweaver

SELECT * FROM INCONSISTENCIAS_CARGA

WHERE TRUNC( DATA_TRANSACAO )= TO_DATE('2008-10-06','YYYY-MM-DD')

<DATA_TRANSACAO hasQuot="NO">TRUNC( DATA_TRANSACAO )=TO_DATE('2008-10-03','YYYY-MM-DD')</DATA_TRANSACAO>

</KEY>

</TABLENAME>

</STATEMENT>

</ns1:MT_INCONSISTENCIAS_CARGA>

Accepted Solutions (1)

Accepted Solutions (1)

prateek
Active Contributor
0 Kudos

Check the action = SQL_QUERY here

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

For your case, it would be

<MT_INCONSISTENCIAS_CARGA >
  <STATEMENT>
     <INCONSISTENCIAS_CARGA action="SQL_QUERY">
      <access> SELECT * FROM INCONSISTENCIAS_CARGA 
WHERE TRUNC( DATA_TRANSACAO )= TO_DATE('2008-10-06','YYYY-MM-DD')
      </access>
    </INCONSISTENCIAS_CARGA>
  </STATEMENT>
</MT_INCONSISTENCIAS_CARGA >

Regards,

Prateek

Answers (1)

Answers (1)

prateek
Active Contributor
0 Kudos

First make sure that when u run the SQL statement of the database system directly, it works.

Now instead of using such complex structure, just create the structure till access node. Don't create any field inside it. During mapping, pass the entire SQL statment (tested) as the value of <access> field.

For this ur action = SQL_QUERY. Rest all settings should be same.

Regards,

Prateek

Former Member
0 Kudos

Hi Prateek.

Thanks for your help!

The SQL Statement works fine using in the directly DB.

You informed that a i can't use any field in access statement.

Could you send me a one example? Please

Thansk!

Regards

Fábio Ferri