By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

Inner Joins and UDFs

P: n/a

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_table 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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
cb******@gmail.com wrote:
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_table 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

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
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.