473,372 Members | 1,058 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,372 software developers and data experts.

Left outer join with case

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

Similar topics

3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
1
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
6
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON (...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
14
by: mike | last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly. There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key of Item, and is also a field in LogEvent. Some...
5
by: Dev | last post by:
Hello all, I need to do a left out join where a.field1 ilike %b.field2% But I can not figure out the exact syntax to using the ilike in the join? ----------
3
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and...
1
by: nico3334 | last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT...
1
by: teneesh | last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this...
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...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.