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

Left outer join with case

P: n/a
Hi,

I receive the error code SQL0338N if I try to compile this statement
(part of the statement):

.... left outer join lateral
(SELECT * FROM LIZSYSABA.VWZL0359TBS WHERE tbs_name = CASE WHEN
MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
on CASE WHEN MC.fixed_date_dat IS NULL THEN cast('01.01.2007' as date)
+ MC.rel_shift_NR DAY ELSE MC.fixed_date_dat END
= TB1.TBS_START ...

Any hints?

Thanks in advance,
Sascha

Jun 27 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Sa***************@gmail.com wrote:
Hi,

I receive the error code SQL0338N if I try to compile this statement
(part of the statement):

.... left outer join lateral
(SELECT * FROM LIZSYSABA.VWZL0359TBS WHERE tbs_name = CASE WHEN
MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
on CASE WHEN MC.fixed_date_dat IS NULL THEN cast('01.01.2007' as date)
+ MC.rel_shift_NR DAY ELSE MC.fixed_date_dat END
= TB1.TBS_START ...

Any hints?

Thanks in advance,
Sascha
[lelle@53dbd181 lelle]$ db2 "? SQL0338N"
SQL0338N An ON clause associated with a JOIN operator or in a
MERGE statement is not valid.

Explanation:

An ON clause associated with a JOIN operator or in a MERGE
statement is not valid for one of the following reasons.

o The ON clause cannot include any subqueries.

o Column references in an ON clause must only reference columns
of tables that are in the scope of the ON clause.

o Scalar fullselects are not allowed in the expressions of an
ON clause.

o A function referenced in an ON clause of a full outer join
must be deterministic and have no external action.

o A dereference operation (->) cannot be used.

o A SQL function or SQL method cannot be used.

o The ON clause cannot include an XMLQUERY or XMLEXISTS
expression.

The statement cannot be processed.

User Response:

Correct the ON clause to reference appropriate columns or delete
any subqueries or scalar fullselects. Remove any dereference
operations, SQL functions, or SQL methods from the ON clause.

If using full outer join ensure that all functions in the ON
clause are deterministic and have no external action.

sqlcode : -338

sqlstate : 42972

Jun 27 '07 #2

P: n/a
Hi,

thank you. Well, but I don't know how to correct the problem.

Any ideas?

Thank you in advance,
Sascha
On 27 Jun., 20:12, Lennart <erik.lennart.jons...@gmail.comwrote:
Sascha.Moeller...@gmail.com wrote:
Hi,
I receive the error code SQL0338N if I try to compile this statement
(part of the statement):
.... left outer join lateral
(SELECT * FROM LIZSYSABA.VWZL0359TBS WHERE tbs_name = CASE WHEN
MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
on CASE WHEN MC.fixed_date_dat IS NULL THEN cast('01.01.2007' as date)
+ MC.rel_shift_NR DAY ELSE MC.fixed_date_dat END
= TB1.TBS_START ...
Any hints?
Thanks in advance,
Sascha

[lelle@53dbd181 lelle]$ db2 "? SQL0338N"

SQL0338N An ON clause associated with a JOIN operator or in a
MERGE statement is not valid.

Explanation:

An ON clause associated with a JOIN operator or in a MERGE
statement is not valid for one of the following reasons.

o The ON clause cannot include any subqueries.

o Column references in an ON clause must only reference columns
of tables that are in the scope of the ON clause.

o Scalar fullselects are not allowed in the expressions of an
ON clause.

o A function referenced in an ON clause of a full outer join
must be deterministic and have no external action.

o A dereference operation (->) cannot be used.

o A SQL function or SQL method cannot be used.

o The ON clause cannot include an XMLQUERY or XMLEXISTS
expression.

The statement cannot be processed.

User Response:

Correct the ON clause to reference appropriate columns or delete
any subqueries or scalar fullselects. Remove any dereference
operations, SQL functions, or SQL methods from the ON clause.

If using full outer join ensure that all functions in the ON
clause are deterministic and have no external action.

sqlcode : -338

sqlstate : 42972

Jun 27 '07 #3

P: n/a
Sa***************@gmail.com wrote:
Hi,

thank you. Well, but I don't know how to correct the problem.

Any ideas?
What is MC and how is it involved in the join?

/Lennart
Thank you in advance,
Sascha
On 27 Jun., 20:12, Lennart <erik.lennart.jons...@gmail.comwrote:
>Sascha.Moeller...@gmail.com wrote:
Hi,
I receive the error code SQL0338N if I try to compile this statement
(part of the statement):
.... left outer join lateral
(SELECT * FROM LIZSYSABA.VWZL0359TBS WHERE tbs_name = CASE WHEN
MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
on CASE WHEN MC.fixed_date_dat IS NULL THEN cast('01.01.2007' as date)
+ MC.rel_shift_NR DAY ELSE MC.fixed_date_dat END
= TB1.TBS_START ...
Any hints?
Thanks in advance,
Sascha

[lelle@53dbd181 lelle]$ db2 "? SQL0338N"

SQL0338N An ON clause associated with a JOIN operator or in a
MERGE statement is not valid.

Explanation:

An ON clause associated with a JOIN operator or in a MERGE
statement is not valid for one of the following reasons.

o The ON clause cannot include any subqueries.

o Column references in an ON clause must only reference columns
of tables that are in the scope of the ON clause.

o Scalar fullselects are not allowed in the expressions of an
ON clause.

o A function referenced in an ON clause of a full outer join
must be deterministic and have no external action.

o A dereference operation (->) cannot be used.

o A SQL function or SQL method cannot be used.

o The ON clause cannot include an XMLQUERY or XMLEXISTS
expression.

The statement cannot be processed.

User Response:

Correct the ON clause to reference appropriate columns or delete
any subqueries or scalar fullselects. Remove any dereference
operations, SQL functions, or SQL methods from the ON clause.

If using full outer join ensure that all functions in the ON
clause are deterministic and have no external action.

sqlcode : -338

sqlstate : 42972

Jun 27 '07 #4

P: n/a
Sa***************@gmail.com wrote:
Hi,

thank you. Well, but I don't know how to correct the problem.
Eliminate that SELECT * subquery. What is the use of it?
Why not LOJ on the base table?
BTW, which version platform of DB2 is this?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 27 '07 #5

P: n/a
On Jun 28, 2:33 am, "Sascha.Moeller...@gmail.com"
<Sascha.Moeller...@gmail.comwrote:
... left outer join lateral
(SELECT * FROM LIZSYSABA.VWZL0359TBS WHERE tbs_name = CASE WHEN
MC.type_txt = 'ZAB' THEN 'BII' ELSE 'STD' END) AS TB1
on CASE WHEN MC.fixed_date_dat IS NULL THEN cast('01.01.2007' as date)
+ MC.rel_shift_NR DAY ELSE MC.fixed_date_dat END
= TB1.TBS_START ...
I think ON clause can be make simple by using COALESCE.
ON COALESCE(MC.fixed_date_dat, cast('01.01.2007' as date) +
MC.rel_shift_NR DAY)
= TB1.TBS_START ...

Jun 28 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.