DSXchange

Hi,

I am trying to use the hint /*+ append */ on a user-defined sql (insert) but datastage complains about the syntaxe. Any ideas on how to work around this?

Cheers,

Post the code and the syntax error. Not enough information here. It should work based on what you have stated.

You don't even need to use User Defined sql if you are doing that just to add the hint. With Column Generated I add it in the Derivation of the first field selected... works like a champ. For select statements anyway.

Post the complete sql and the complaint.

chulett wrote:You don't even need to use User Defined sql if you are doing that just to add the hint. With Column Generated I add it in the Derivation of the first field selected... works like a champ. For select statements anyway.

Post the complete sql and the complaint.

Thanks guys, I have sorted it out. It's working. I might have typed something that ds couldn't compile.

the sql is like this:

insert into table /*+ append */ ( a, b ) values (:1,:2)

Cheers,

Hints work. I have used no_merge hint using user-defined sql

Regards
Sreenivasulu

Has anyone tried Craig's technique of including the Hint in the expression for the first column using Designer 7.5.1.A. They've upgraded the OCI SQL-builder tool and it's a bit of a syntax-Nazi.

I actually wanted to use the same technique to place a DISTINCT before the first column name, but I cannot get it to work for hints either.

The only things the expression-builder allows you to do are functions that you select from a list (eg. ABS(col)) and arithmetic calculations (eg. col + 10).

I shun the expression builder, so won't have seen this. I've certainly used the /*+ APPEND */ hint to good effect here and there (but still prefer sqlldr).

I don't use the 'Fully Generated SQL' option now and I wouldn't touch the 'SQL Builder tool' with a thirty-nine and a half foot pole.

However, putting hints and other goodies in the first column's derivation works fine with the 'Column Generated' option in earlier versions and with the 'Generate SELECT clause from column list; enter other clauses' option in 7.5.1A too from what little I've been able to play with it.

Ahhhh. The veil has been withdrawn!!! Column Generated is my new best friend. It even lets you slip a DISTINCT into the derivation of the first column.

The SQL Builder should come with compulsory public health warnings.

Thanks for your help guys.

If I have a lot of columns from multiple tables I start with the SQL builder to build a column list and then switch across to generated or user-defined SQL straight afterwards. The SQL builder may be poor at table joins and where clauses but it is very fast for finding multiple tables and choosing the columns from those tables. When you are done it loads those columns into the column list. Faster then running the load table definition several times over.

That's an interesting thought, Vincent. I'll have to play around with it.

At this point, I still prefer to 'seed' things by either dragging the column metadata in from the Repository browser onto a link, or directly via the Load button inside the stage. The method depends on if I want blank derivations or not. And since I've typically built and tested the query from TOAD first, I can then just paste in the 'other' clauses the stage needs.