What is the use of the Derivation field in ODBC Stage(Outputs - Columns - Derivation)?
I know jobs work even with out Derivation field.
Are there any specific advantages for keeping the Derivation field?
Are there any disadvantages for not keeping the Derivation field?
We also observed that when a TableDefinition name/path is changed,that is getting updated/reflected in the job(ODBC-Outputs- Columns - Table definition reference field) automatically.
But when we modify the colunm name/description, that is not getting updated/reflected in the job(ODBC-Outputs- Columns - Column definition reference field) automatically.
Any thoughts?
Thanks in Advance.
Derivation in ODBC Stage
Moderators: chulett, rschirm, roy
The derivation is used when Auto-generated SQL is chosen. If a column's derivation is blank, then the column name is used.
The derivation is always the metadata name dot column name. Therefore, the metadata name must be either schema dot tablename or just tablename in order for the Auto-generated SQL to be functional.
Experiment a little with it, you'll see you can do TRIM(tablename.columnname) in the derivation field and the SQL adjusts accordingly.
The derivation is always the metadata name dot column name. Therefore, the metadata name must be either schema dot tablename or just tablename in order for the Auto-generated SQL to be functional.
Experiment a little with it, you'll see you can do TRIM(tablename.columnname) in the derivation field and the SQL adjusts accordingly.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can put anything in the Derivation field that you might put in a column specification in the SELECT clause, including SQL expressions, aggregate functions, and so on. Try it. Maybe something like col1 || ' -- ' || col2 or max(col4) or even DISTINCT col5. You can add GROUP BY, HAVING and ORDER BY clauses on the Selection tab. View the generated SQL to see what's produced.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks Ray.ray.wurlod wrote:You can put anything in the Derivation field that you might put in a column specification in the SELECT clause, including SQL expressions, aggregate functions, and so on. Try it. Maybe something like col1 || ' -- ' || col2 or max(col4) or even DISTINCT col5. You can add GROUP BY, HAVING and ORDER BY clauses on the Selection tab. View the generated SQL to see what's produced.
I will give a try.
Thanks Ken.kcbland wrote:The derivation is used when Auto-generated SQL is chosen. If a column's derivation is blank, then the column name is used.
The derivation is always the metadata name dot column name. Therefore, the metadata name must be either schema dot tablename or just tablename in order for the Auto-generated SQL to be functional.
Experiment a little with it, you'll see you can do TRIM(tablename.columnname) in the derivation field and the SQL adjusts accordingly.
I will give a try.
How about ColumnReference/Description not being updated,any thoughts?
Thanks in Advance.