• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

DW intircacies between Sybase and Oracle

I have a DW that pulls out some segments of data on the pull.

3 lines cause me an Invalid Datawindow Row/Column error when I run the application.

SELECT <Various Column names>,
     charindex( FAST.account_request.status,"xndaper"),  
     datepart(mm, FAST.account_request.requested_date),  
     datepart(dd, FAST.account_request.requested_date),  
     datepart(yy, FAST.account_request.requested_date)

These lines Oracle doesn't like in the Datawindow.

Any solution please?
0
valiant_at_mci
Asked:
valiant_at_mci
  • 2
1 Solution
 
BhattiCommented:
Please check the name of the column. May be false.
And/or also may the row does not exist.


0
 
Caseys95Commented:
Oracle does not recognize 'Charindex' or 'Datepart'
CharIndex(...) = InStr(...)
DatePart(...)  = To_Number( To_Char( ... ))

AND the parameter lists are different.

For Oracle why don't you try:

Option 1:
SELECT <Various Column names>,
    InStr("xndaper", FAST.account_request.status),  
    To_Number( To_Char(FAST.account_request.requested_date, 'mm') ),  
    To_Number( To_Char(FAST.account_request.requested_date, 'dd') ),  
    To_Number( To_Char(FAST.account_request.requested_date, 'yy') )

Option 2:
You could also create wrapper functions in Oracle to replicate the functions 'CharIndex' and 'DatePart'. This would be the prefered syntax if the datawindow needs to run in both environments.

Option 3:
You might also change the SQLSelect dynamically just before the retrieve.

ls_SQL = dw_1.Object.Datawindow.Table.Select
...
dw_1.Object.Datawindow.Table.Select = ls_SQL

ll_Return = dw1.Retrieve()
0
 
valiant_at_mciAuthor Commented:
Could you elaborate more on the use of to_number and Instr?

I inserted the text as it appears above, and of course it didn't take.  

Modifying the SQL on the fly is something that can be done, but won't it error out when it goes to retrieve anyhow?

Thanks much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now