473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Inner Joins and UDFs


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
1 3991
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
4
by: michaelnewport | last post by:
Greetings, I like to write my inner joins as below, but someone at work tells me its not as 'performant' as using the 'inner join' statement. Is this true ? Is there a better way to write it...
14
by: Martin Lacoste | last post by:
Access 2000 Trying even the most basic queries - select queries joining (inner join) two tables - and the results are not updatable, and I can't tell why they aren't. All permissions are on,...
2
by: gkellymail | last post by:
the following query works fine: select link.idx, link.x_table, link.x_id_a, link.x_id_z, a.strandid, b.strandid from link_detail, link, strand A, strand B where link_detail.x_table =...
0
by: stanlew | last post by:
Happy New Year everyone! I'm new to both T-SQL and this forum. I'm currently doing an internship and my first task was to create a small program which will send an email detailing the sales of the...
5
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
6
by: loffing4 | last post by:
I'm trying to write a query that selects the top five members of a forum based on how many total points they have. A member's total points are calculated as follows: (3 * # answers posted) + (10 * #...
4
by: AXESMI59 | last post by:
I created a query in the Access Query Builder that I modified to use with VBA Code so I could reuse it for any record I choose. The one that I created with the Access Query builder works beautifully...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.