Derivation in ODBC Stage

poorna_76 · Post by **poorna_76** » Fri Mar 17, 2006 8:35 am

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.

kcbland · Post by **kcbland** » Fri Mar 17, 2006 9:13 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 17, 2006 9:58 am

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.

poorna_76 · Post by **poorna_76** » Fri Mar 17, 2006 12:37 pm

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.

Thanks Ray.

I will give a try.

poorna_76 · Post by **poorna_76** » Fri Mar 17, 2006 2:51 pm

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.

Thanks Ken.
I will give a try.

How about ColumnReference/Description not being updated,any thoughts?

Thanks in Advance.