Inner Joins and UDFs

[cbielins@gmail.com]

I'm trying to join a dimension table to a summary table using an inner
join. However, I would like to join using a udf that I've created.

Example:
select ...
from fact_table fact
inner join schema1.dim_tab le dim
on schema.udf(fact .var1) = dim.var2

I get the error:
SQL0338N An ON clause associated with a JOIN operator or in a MERGE
statement
is not valid. SQLSTATE=42972

Can this be done and I just don't have the right syntax?

I could use a WITH temp table to solve this, but is there a way to do
it altogether?

TIA,
Chris

[Serge Rielau]

Re: Inner Joins and UDFs

cbielins@gmail. com wrote:[color=blue]
> I'm trying to join a dimension table to a summary table using an inner
> join. However, I would like to join using a udf that I've created.
>
> Example:
> select ...
> from fact_table fact
> inner join schema1.dim_tab le dim
> on schema.udf(fact .var1) = dim.var2
>
> I get the error:
> SQL0338N An ON clause associated with a JOIN operator or in a MERGE
> statement
> is not valid. SQLSTATE=42972
>
> Can this be done and I just don't have the right syntax?
>
> I could use a WITH temp table to solve this, but is there a way to do
> it altogether?
>
> TIA,
> Chris
>[/color]
For inner join the only limitation I'm aware of is teh one listed:

SQL0338N An ON clause associated with a JOIN operator or in a
MERGE statement is not valid.
<snip>
o A SQL function or SQL method cannot be used.

You can use a a regular FROM WHERE to get teh same result as an INNER
JOIN ....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab