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
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.
Return to “Archive of DataStage Users@Oliver.com”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration