Editing an Oracle WHERE clause

[PhilHibbs]

I'm trying to edit a complex expression that I've built up using the Oracle 10g Query Builder, is there any way that I can do this without causing an "Unhandled exception" error? Once the constructed WHERE clause has been added, it looks like the only way that I can change it is to manually delete it from the Selection edit and then build it up again from scratch using the very slow and annoying Expression Editor. Any hints?

*Edit*: Looks like it's not just the editing that is causing the problem, the built expression also causes the exception. Here is what it looks like...

Code: Select all

TABLE.SDATE >= TO_TIMESTAMP('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TABLE.SDATE >= TO_TIMESTAMP( CONCAT( ( TO_CHAR( SYSDATE  , 'YYYY'  ) - 2 )  , '-01-01 00:00:00'  )  , 'YYYY-MM-DD HH24:MI:SS'  )

[/size]
Is it objecting to "TO_CHAR( SYSDATE , 'YYYY' ) - 2", subtracting a number from a string? I'm sure I've done that before iin Oracle.

*Edit2*: Also, it won't allow TO_NUMBER with a single parameter!

*Edit3*: I've given up and done the calculation in a Sequence Job.

[priyadarshikunal]

why not use a user-defined query?

I will try to have a look on query builder when I get time.

[PhilHibbs]

why not use a user-defined query?

I will try to have a look on query builder when I get time.

I've always had a preference for using the tools to generate queries, as they tend to keep schemas in line automatically.

[chulett]

No idea as I've always used either the 'column generated' option (but that would be in Server, pretty sure there is an equivalent in PX) or just gone user-defined. I think that Query Builder is there only to say they have it but found it combersome to use, from what I recall an update to the query required a complete rewrite from scratch.

Sorry.

[HariK]

Code: Select all

 TABLE.SDATE >= TO_TIMESTAMP( CONCAT( ( TO_CHAR( SYSDATE  , 'YYYY'  ) - 2 )  , '-01-01 00:00:00'  )  , 'YYYY-MM-DD HH24:MI:SS'  )

Could you try the following, to check if whether Edit 1 has the issue.

Code: Select all

 TABLE.SDATE >= ADD_MONTHS(TRUNC( SYSDATE, 'YEAR') , - 24)

[kwwilliams]

The query builders within DataStage are very poor, and I know few people who use them. You get no additional functionality included by using it and you inherit some weakness in that its sql may not be optimum.

If you were to use user-defined sql, you could performance tune the query more effeciently. You could use the more advanced features of your sql editing tool. In short your time would be used wisely to create effecient sql. This means that good ETL developers still have to be strong in understanding of each of the database engines utilized by their company (or for consultants multiple companies).

Eventually, I think the Infosphere tooling will get to the point that the query builders are worth the time to work with them. I don't think this will happen until RSA/RDA is integrated with DataStage to the point it understands the relationships with tables AND the query building tool can utilize information from tools like Performance Expert or Optim top create more effecient sql. The BI tools are closer to this than the ETL tooling currently.