0

I'm a Business Intelligence intern and am trying to write a simple ETL batch job to bring one table into our warehouse using SAP Data Services Designer. The source table has a timestamp column, which halts the job's execution, saying:

You cannot select directly from timestamp column . Using a timestamp column in SQL transform may cause this error. See documentation or notify Customer Support.

From the technical manual, this limitation is confirmed in the timestamp section, which reads:

You cannot use timestamp columns in the SQL transform or in an Oracle stored procedure. To use a timestamp column in the SQL transform, convert the timestamp column in the select list of the SQL transform to a character format using the to_char function and convert it back to timestamp using the to_date function."

I've tried remedying the problem by changing the output schema's column to a datetime type, and converting the timestamp in the SQL transform with

TO_DATE(TO_CHAR(SQL.DATETIME_STAMP, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS')

I'm missing a key concept as it still fails with error 54003 no matter what I try. Thoughts, anyone?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Canucklesandwich
  • 693
  • 6
  • 15
  • The second attempt looks correct, but the error says: "ORA-54003: specified data type is not supported for a virtual column". Does "select SQL.DATETIME_STAMP from " work fine? – Multisync Oct 17 '14 at 20:34
  • I'm unable to query the table directly due to access restrictions. – Canucklesandwich Oct 17 '14 at 20:36
  • 1
    It isn't ORA-54003, I'm sorry. Have you read this? http://www.forumtopics.com/busobj/viewtopic.php?p=996856 – Multisync Oct 17 '14 at 20:48
  • Thanks for the article. He seems to be having the exact same issue I'm having. 54003 isn't an ORA error, you're right. I wish I could query the table directory to see a sample of the data in it, but. – Canucklesandwich Oct 17 '14 at 20:55
  • Fixed it. I just did the conversion in the SQL transform instead of in the intermediate Query transform (that was between the SQL transform and the temp table). Thanks for your help, Multisync. – Canucklesandwich Oct 17 '14 at 21:26

0 Answers0