topic Re: How to get data from Oracle using Native SQL in SAP.. Problem with date in Application Development and Automation Discussions

How to get data from Oracle using Native SQL in SAP.. Problem with date

Former Member — Wed, 01 Dec 2010 14:47:22 GMT

Hi Masters.

I'm trying to get data from an Oracle DB. I was able to connect to Oracle using tcode DBCO. The connetion works fine

I wrote this code and it works fine without the statement of where date > '01-09-2010'

But i need that statement on the select. I read a lot about this issue, but no answer.

My code is (this code is in SAP ECC 6.0)


DATA: BEGIN OF datos OCCURS 0,
      id_numeric(10),
      component_name(40),
      comuna(10),
      record_id(10),
      status,
      sampled_date(10),
      END OF datos.
DATA: c TYPE cursor.

EXEC SQL.
  connect to 'LIM' as 'MYDB'
ENDEXEC.

EXEC SQL.
  SET CONNECTION 'MYDB'
ENDEXEC.

EXEC SQL PERFORMING loop_output.
  SELECT ID_NUMERIC, COMPONENT_NAME, COMUNA, RECORD_ID, STATUS, SAMPLED_DATE
  into :datos from lims.SAMP_TEST_RESULT
  where	date > '01-09-2010'
ENDEXEC.

EXEC SQL.
  disconnect 'MYDB'
ENDEXEC.

How can i get the data from that date?? If i delete the where statemet, the program works well, it takes 30 mins and show all the data, I just need the data from that date.

Any help

Regards

Re: How to get data from Oracle using Native SQL in SAP.. Problem with date

kesavadas_thekkillath — Wed, 01 Dec 2010 14:56:50 GMT

Please refer the example in this link which deals with Oracle date format.

You can finnd a command DECODE which is used for date formats. If you have a look at whole theory then you will get an idea.

Link:[Bulk insert SQL command to transfer data from SAP to Oracle|http://sap.ittoolbox.com/groups/technical-functional/sap-dev/bulk-insert-sql-command-to-transfer-data-from-sap-to-oracle-cl_sql_connection-3780804]

Re: How to get data from Oracle using Native SQL in SAP.. Problem with date

Former Member — Thu, 02 Dec 2010 13:10:17 GMT

A better way to solve this problem was to ask a Basis guy to create a table in SAP pointing to a table in Oracle, so that's how we solve the issue.