Antwort: User defined SQL in Ora OCI stage

Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.

Moderators: chulett, rschirm

Locked
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Antwort: User defined SQL in Ora OCI stage

Post by admin »

I have the same problem with user-definied SQL in DataStage 4.0 (HP-UX 11).

I use "SELECT to_char(sysdate - 7, yyyy.dd.mm) FROM SYS.DUAL" for reading a date and "DELETE DAWA.LOGMNR_EINTRAEGE WHERE SID=:1 AND PROCESS_DATE < to_date(:2,yyyy.dd.mm)" for delete Entries in the table depends on the date.

Udo




Bob_2_Whiteside@sbphrd.com on 27.06.2001 22:27:50

Bitte antworten an informix-datastage@oliver.com

An: informix-datastage@oliver.com
Kopie: (Blindkopie: Udo Neumann/EDS)
Thema: User defined SQL in Ora OCI stage




Im trying to use an Oracle table, defined via OraOCI stage as a reference into a transform. The reference link will supply a "completed date" column to the main input link for writing to an Output OraOCI link. The completed date column is defined in Oracle as date datatype and is defined to Datastage as timestamp datatype. Using generated SQL for the reference link, I had no problem. I tried to change over to user-defined SQL and initially left the SQL as copied from the generated SQL to test; in otherwords the SQL was identical. Two things broke;

1. the data browse fails with an unbound variable Oracle error. Data browse worked with generated SQL, the link parameters were ignored and the entire table was presented in data browse window.

2. Any date column in the link was flagged as warning unable to convert string value and sent as NULL to the data browse and the transform stage.

Is user-defined SQL parsed and handled differently from generated SQL in DataStage?

If so, are there other implications to using it particularly in regard to Transform stages? I suspect that Ill need to add to_char functions in the reference stage SQL and then need to code the output link of the transform with user defined SQL and code to_date functions to process the date field received from the reference link. I tried this, by the way, and still couldnt get it to work. Any help appreciated. I use DataStage 4.0 (not 4.0.3) on SUN. Thanks, Bob
Locked