join view question

[xixi]

when i create a join view like this

create view JV104FZ.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
JV104FZ.APPTINM.APAM32, JV104FZ.APPTINM.APNO20,
JV104FZ.APPTINM.APQY05, JV104FZ.APPTINM.PONO01,
JV104FZ.APPTINM.PONO05, JV104FZ.APPTINM.PONO19,
COALESCE(JV104FZ.POPTOL.POCD01, ' '), COALESCE(JV104FZ.POPTOL.POCD13,
' '), JV104FZ.POPTOL.systimestamp, JV104FZ.POPTOL.loginname,
COALESCE(JV104FZ.APPTINM.id, JV104FZ.POPTOL.id ) from JV104FZ.APPTINM
left join JV104FZ.POPTOL on JV104FZ.APPTINM.PONO01 =
JV104FZ.POPTOL.PONO01 and JV104FZ.APPTINM.PONO05 =
JV104FZ.POPTOL.PONO05

i also want to have the where clause so the view will exclude the data
i don't want, so i have the view created like this

create view GN104DX.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
GN104DX.APPTINM.APAM32, GN104DX.APPTINM.APNO20,
GN104DX.APPTINM.APQY05, GN104DX.APPTINM.PONO01,
GN104DX.APPTINM.PONO05, GN104DX.APPTINM.PONO19, GN104DX.POPTOL.POCD01,
GN104DX.POPTOL.POCD13, GN104DX.POPTOL.systimestamp,
GN104DX.POPTOL.loginname, COALESCE(GN104DX.APPTINM.id,
GN104DX.POPTOL.id ) from GN104DX.APPTINM left join GN104DX.POPTOL on
GN104DX.APPTINM.PONO01 = GN104DX.POPTOL.PONO01 and
GN104DX.APPTINM.PONO05 = GN104DX.POPTOL.PONO05 where ((POCD01<>'D')
and ( POCD01<>'F') and ( POCD01<>'O')) , but looks like it exclude the
data from poptol first then do the join, so how to let db2 do the join
first then where clause later?

[Tokunaga T.]

da****@yahoo.com (xixi) wrote in message

create view GN104DX.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
GN104DX.APPTINM.APAM32, GN104DX.APPTINM.APNO20,
GN104DX.APPTINM.APQY05, GN104DX.APPTINM.PONO01,
GN104DX.APPTINM.PONO05, GN104DX.APPTINM.PONO19, GN104DX.POPTOL.POCD01,
GN104DX.POPTOL.POCD13, GN104DX.POPTOL.systimestamp,
GN104DX.POPTOL.loginname, COALESCE(GN104DX.APPTINM.id,
GN104DX.POPTOL.id ) from GN104DX.APPTINM left join GN104DX.POPTOL on
GN104DX.APPTINM.PONO01 = GN104DX.POPTOL.PONO01 and
GN104DX.APPTINM.PONO05 = GN104DX.POPTOL.PONO05 where ((POCD01<>'D')
and ( POCD01<>'F') and ( POCD01<>'O')) , but looks like it exclude the
data from poptol first then do the join, so how to let db2 do the join
first then where clause later?

Why do you think so?
DB2 do join first then apply where clause. So, all rows in APPTINM
that have no matched row of POPTOL will be excluded from final result.
The reason is:
POCD01 of those rows are NULL, then conditions in where are unknown,
not true. In consequence, those rows will be excluded from final
result.

If you want to include those rows in final result, you should put the
conditions in ON clause.

[--CELKO--]

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;

[xixi]

looks like the post here is incorrect for coalesce, the actual one is

create view NJIPD.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
NJIPD.APPTINM.APAM32, NJIPD.APPTINM.APNO20, NJIPD.APPTINM.APQY05,
NJIPD.APPTINM.PONO01, NJIPD.APPTINM.PONO05, NJIPD.APPTINM.PONO19,
COALESCE(NJIPD.POPTOL.POCD01, ' '), COALESCE(NJIPD.POPTOL.POCD13, '
'), NJIPD.POPTOL.systimestamp, NJIPD.POPTOL.loginname,
COALESCE(NJIPD.APPTINM.id, NJIPD.POPTOL.id ) from NJIPD.APPTINM left
join NJIPD.POPTOL on NJIPD.APPTINM.PONO01 = NJIPD.POPTOL.PONO01 and
NJIPD.APPTINM.PONO05 = NJIPD.POPTOL.PONO05 where ((POCD01<>'D') and (
POCD01<>'F') and ( POCD01<>'O'))

i specify that if the final result pocd01 and pocd13 is null, then use
space replace.

i can't get the right data if i have the where clause when i create
the view, i have to create a temp view first, then create a another
view on the temp view with where clause, then i get it right, can you
tell me whether you have any direct way? thanks

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. com>...

da****@yahoo.com (xixi) wrote in message

create view GN104DX.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
GN104DX.APPTINM.APAM32, GN104DX.APPTINM.APNO20,
GN104DX.APPTINM.APQY05, GN104DX.APPTINM.PONO01,
GN104DX.APPTINM.PONO05, GN104DX.APPTINM.PONO19, GN104DX.POPTOL.POCD01,
GN104DX.POPTOL.POCD13, GN104DX.POPTOL.systimestamp,
GN104DX.POPTOL.loginname, COALESCE(GN104DX.APPTINM.id,
GN104DX.POPTOL.id ) from GN104DX.APPTINM left join GN104DX.POPTOL on
GN104DX.APPTINM.PONO01 = GN104DX.POPTOL.PONO01 and
GN104DX.APPTINM.PONO05 = GN104DX.POPTOL.PONO05 where ((POCD01<>'D')
and ( POCD01<>'F') and ( POCD01<>'O')) , but looks like it exclude the
data from poptol first then do the join, so how to let db2 do the join
first then where clause later?

Why do you think so?
DB2 do join first then apply where clause. So, all rows in APPTINM
that have no matched row of POPTOL will be excluded from final result.
The reason is:
POCD01 of those rows are NULL, then conditions in where are unknown,
not true. In consequence, those rows will be excluded from final
result.

If you want to include those rows in final result, you should put the
conditions in ON clause.

[Tokunaga T.]

da****@yahoo.com (xixi) wrote in message news:<c0**************************@posting.google. com>...

looks like the post here is incorrect for coalesce, the actual one is

create view NJIPD.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
NJIPD.APPTINM.APAM32, NJIPD.APPTINM.APNO20, NJIPD.APPTINM.APQY05,
NJIPD.APPTINM.PONO01, NJIPD.APPTINM.PONO05, NJIPD.APPTINM.PONO19,
COALESCE(NJIPD.POPTOL.POCD01, ' '), COALESCE(NJIPD.POPTOL.POCD13, '
'), NJIPD.POPTOL.systimestamp, NJIPD.POPTOL.loginname,
COALESCE(NJIPD.APPTINM.id, NJIPD.POPTOL.id ) from NJIPD.APPTINM left
join NJIPD.POPTOL on NJIPD.APPTINM.PONO01 = NJIPD.POPTOL.PONO01 and
NJIPD.APPTINM.PONO05 = NJIPD.POPTOL.PONO05 where ((POCD01<>'D') and (
POCD01<>'F') and ( POCD01<>'O'))

i specify that if the final result pocd01 and pocd13 is null, then use
space replace.

i can't get the right data if i have the where clause when i create
the view, I feel that you didn't understand my explanation.

If you specify "where ((POCD01<>'D') and (POCD01<>'F') and
(POCD01<>'O'))",
all rows that pocd01 is null will be excluded from results before you
apply COALESCE function.
If you want get rows that pocd01 is null, you shoud specify the
conditions in ON caluse and remove WHERE clause.
Like this:
on NJIPD.APPTINM.PONO01 = NJIPD.POPTOL.PONO01 and NJIPD.APPTINM.PONO05
= NJIPD.POPTOL.PONO05 AND POCD01<>'D' and POCD01<>'F' and POCD01<>'O'
i have to create a temp view first, then create a another
view on the temp view with where clause, then i get it right, I guess that you replaced null POCD01 with space in a temp view.
Then "((POCD01<>'D') and (POCD01<>'F') and (POCD01<>'O'))" get 'ture',
NOT 'unknown'.
can you tell me whether you have any direct way? thanks I have mentioned at top of this post and at bottom of my previous
post.
ON ..... AND POCD01<>'D' and POCD01<>'F' and POCD01<>'O'

I'm sorry, if I miss the point.

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. com>...

da****@yahoo.com (xixi) wrote in message

create view GN104DX.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
GN104DX.APPTINM.APAM32, GN104DX.APPTINM.APNO20,
GN104DX.APPTINM.APQY05, GN104DX.APPTINM.PONO01,
GN104DX.APPTINM.PONO05, GN104DX.APPTINM.PONO19, GN104DX.POPTOL.POCD01,
GN104DX.POPTOL.POCD13, GN104DX.POPTOL.systimestamp,
GN104DX.POPTOL.loginname, COALESCE(GN104DX.APPTINM.id,
GN104DX.POPTOL.id ) from GN104DX.APPTINM left join GN104DX.POPTOL on
GN104DX.APPTINM.PONO01 = GN104DX.POPTOL.PONO01 and
GN104DX.APPTINM.PONO05 = GN104DX.POPTOL.PONO05 where ((POCD01<>'D')
and ( POCD01<>'F') and ( POCD01<>'O')) , but looks like it exclude the
data from poptol first then do the join, so how to let db2 do the join
first then where clause later?

Why do you think so?
DB2 do join first then apply where clause. So, all rows in APPTINM
that have no matched row of POPTOL will be excluded from final result.
The reason is:
POCD01 of those rows are NULL, then conditions in where are unknown,
not true. In consequence, those rows will be excluded from final
result.

If you want to include those rows in final result, you should put the
conditions in ON clause.

[xixi]

hi, i already try your way and still doesn't work, it returns all the
row from apptinm.

still can't get it right

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. com>...

da****@yahoo.com (xixi) wrote in message news:<c0**************************@posting.google. com>...

looks like the post here is incorrect for coalesce, the actual one is

create view NJIPD.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
NJIPD.APPTINM.APAM32, NJIPD.APPTINM.APNO20, NJIPD.APPTINM.APQY05,
NJIPD.APPTINM.PONO01, NJIPD.APPTINM.PONO05, NJIPD.APPTINM.PONO19,
COALESCE(NJIPD.POPTOL.POCD01, ' '), COALESCE(NJIPD.POPTOL.POCD13, '
'), NJIPD.POPTOL.systimestamp, NJIPD.POPTOL.loginname,
COALESCE(NJIPD.APPTINM.id, NJIPD.POPTOL.id ) from NJIPD.APPTINM left
join NJIPD.POPTOL on NJIPD.APPTINM.PONO01 = NJIPD.POPTOL.PONO01 and
NJIPD.APPTINM.PONO05 = NJIPD.POPTOL.PONO05 where ((POCD01<>'D') and (
POCD01<>'F') and ( POCD01<>'O'))

i specify that if the final result pocd01 and pocd13 is null, then use
space replace.

i can't get the right data if i have the where clause when i create
the view,

I feel that you didn't understand my explanation.

If you specify "where ((POCD01<>'D') and (POCD01<>'F') and
(POCD01<>'O'))",
all rows that pocd01 is null will be excluded from results before you
apply COALESCE function.
If you want get rows that pocd01 is null, you shoud specify the
conditions in ON caluse and remove WHERE clause.
Like this:
on NJIPD.APPTINM.PONO01 = NJIPD.POPTOL.PONO01 and NJIPD.APPTINM.PONO05
= NJIPD.POPTOL.PONO05 AND POCD01<>'D' and POCD01<>'F' and POCD01<>'O'

i have to create a temp view first, then create a another
view on the temp view with where clause, then i get it right,

I guess that you replaced null POCD01 with space in a temp view.
Then "((POCD01<>'D') and (POCD01<>'F') and (POCD01<>'O'))" get 'ture',
NOT 'unknown'.

can you tell me whether you have any direct way? thanks

I have mentioned at top of this post and at bottom of my previous
post.
ON ..... AND POCD01<>'D' and POCD01<>'F' and POCD01<>'O'

I'm sorry, if I miss the point.

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. com>...

da****@yahoo.com (xixi) wrote in message
> create view GN104DX.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
> PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
> GN104DX.APPTINM.APAM32, GN104DX.APPTINM.APNO20,
> GN104DX.APPTINM.APQY05, GN104DX.APPTINM.PONO01,
> GN104DX.APPTINM.PONO05, GN104DX.APPTINM.PONO19, GN104DX.POPTOL.POCD01,
> GN104DX.POPTOL.POCD13, GN104DX.POPTOL.systimestamp,
> GN104DX.POPTOL.loginname, COALESCE(GN104DX.APPTINM.id,
> GN104DX.POPTOL.id ) from GN104DX.APPTINM left join GN104DX.POPTOL on
> GN104DX.APPTINM.PONO01 = GN104DX.POPTOL.PONO01 and
> GN104DX.APPTINM.PONO05 = GN104DX.POPTOL.PONO05 where ((POCD01<>'D')
> and ( POCD01<>'F') and ( POCD01<>'O')) , but looks like it exclude the
> data from poptol first then do the join, so how to let db2 do the join
> first then where clause later?
Why do you think so?
DB2 do join first then apply where clause. So, all rows in APPTINM
that have no matched row of POPTOL will be excluded from final result.
The reason is:
POCD01 of those rows are NULL, then conditions in where are unknown,
not true. In consequence, those rows will be excluded from final
result.

If you want to include those rows in final result, you should put the
conditions in ON clause.

[Tokunaga T.]

da****@yahoo.com (xixi) wrote in message news:<c0**************************@posting.google. com>...

hi, i already try your way and still doesn't work, it returns all the
row from apptinm.

still can't get it right

What result do you want to get?
You specified "NJIPD.APPTINM left join NJIPD.POPTOL" and no condition
for APPTINM(I thought POCD01 is a column of POPTOL).
So, all rows of APPTINM will be included in result.

[Tokunaga T.]

da****@yahoo.com (xixi) wrote in message news:<c0**************************@posting.google. com>...

hi, i already try your way and still doesn't work, it returns all the
row from apptinm.

still can't get it right

How about this?
where (POCD01<>'D') and POCD01<>'F' and POCD01<>'O' OR POCD01 is null

This returns all the rows of APPTINM which have no corresponding row
of POPTOL and, if there is a corresponding row of POPTOL, POCD01 is
not in ('D', 'F', 'O') or POCD01 is null.

[xixi]

as i said, when i create the temp join view without where clause
specify, when i create a final view with where clause specify , i get
the right data, which is less than when i do the way as you specify.

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81*************************@posting.google.c om>...

da****@yahoo.com (xixi) wrote in message news:<c0**************************@posting.google. com>...

hi, i already try your way and still doesn't work, it returns all the
row from apptinm.

still can't get it right

What result do you want to get?
You specified "NJIPD.APPTINM left join NJIPD.POPTOL" and no condition
for APPTINM(I thought POCD01 is a column of POPTOL).
So, all rows of APPTINM will be included in result.