a strange sql

[db2group88]

hi , i am trying to create such sql on db2 v8.2 on windows.
create view AP (APAM32, APNO20, APQY05, pocd01) as select APP1.APAM32,
APP1.APNO20, APP1.APQY05, COALESCE(POPT.POCD01, ' ') from APP1 left
join POPT on APP1.PONO01 = POPT.PONO01 and APP1.PONO05 = POPT.PONO05
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O')), but could
not come up with right output, so i have to do seperate steps, first
create a temporay view without where clause, then create the final view
append with the where clause, can somebody understand the mystery
behind it? thanks

[Knut Stolze]

db2group88 wrote:

hi , i am trying to create such sql on db2 v8.2 on windows.
create view AP (APAM32, APNO20, APQY05, pocd01) as select APP1.APAM32,
APP1.APNO20, APP1.APQY05, COALESCE(POPT.POCD01, ' ') from APP1 left
join POPT on APP1.PONO01 = POPT.PONO01 and APP1.PONO05 = POPT.PONO05
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O')), but could
not come up with right output, so i have to do seperate steps, first
create a temporay view without where clause, then create the final view
append with the where clause, can somebody understand the mystery
behind it? thanks

If you would provide some information on the table structure, the data in
the table, the output you got, and the output you expect, then we may be
able to assist in figuring out what's going on. For all we can tell, it
may be just a mistake in the view definition...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[db2group88]

Hi, we are trying to create a join view based on two tables ( only have
varchar and decimal type column), If i simply create the view together
with where clause, i got less row than what it should be, that's why i
come up the other way. thank you

Knut Stolze wrote:

db2group88 wrote:

hi , i am trying to create such sql on db2 v8.2 on windows.
create view AP (APAM32, APNO20, APQY05, pocd01) as select APP1.APAM32,
APP1.APNO20, APP1.APQY05, COALESCE(POPT.POCD01, ' ') from APP1 left
join POPT on APP1.PONO01 = POPT.PONO01 and APP1.PONO05 = POPT.PONO05
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O')), but could
not come up with right output, so i have to do seperate steps, first
create a temporay view without where clause, then create the final view
append with the where clause, can somebody understand the mystery
behind it? thanks

If you would provide some information on the table structure, the data in
the table, the output you got, and the output you expect, then we may be
able to assist in figuring out what's going on. For all we can tell, it
may be just a mistake in the view definition...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Tonkuma]

How about this?
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O'))
OR POCD01 IS NULL;

[Knut Stolze]

db2group88 wrote:

Hi, we are trying to create a join view based on two tables ( only have
varchar and decimal type column), If i simply create the view together
with where clause, i got less row than what it should be, that's why i
come up the other way. thank you

There may be a problem in DB2 or there may be a misconception on your side.
My point is that we can't help you at all if you don't give us a complete
scenario.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

db2group88 wrote:

hi , i am trying to create such sql on db2 v8.2 on windows.
create view AP (APAM32, APNO20, APQY05, pocd01) as select APP1.APAM32,
APP1.APNO20, APP1.APQY05, COALESCE(POPT.POCD01, ' ') from APP1 left
join POPT on APP1.PONO01 = POPT.PONO01 and APP1.PONO05 = POPT.PONO05
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O')), but could
not come up with right output, so i have to do seperate steps, first
create a temporay view without where clause, then create the final view
append with the where clause, can somebody understand the mystery
behind it? thanks

Not an answer, just a note.

where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O'))

should probably be

WHERE NOT POCD01 IN('D', 'F', 'O')

B.

[Tonkuma]

Tonkuma wrote:

How about this?
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O'))
OR POCD01 IS NULL;

The reason of my guess is as following.

>hi , i am trying to create such sql on db2 v8.2 on windows.

create view AP (APAM32, APNO20, APQY05, pocd01) as select APP1.APAM32,
APP1.APNO20, APP1.APQY05, COALESCE(POPT.POCD01, ' ') from APP1 left
join POPT on APP1.PONO01 = POPT.PONO01 and APP1.PONO05 = OPT.PONO05
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O')), but could
not come up with right output,<<
If POCD01 is NULL, where condition get UnKnown.
Because (POCD01<>'D') =: (NULL <>'D') =: Unknown.
So, the row will not be included the result.

> so i have to do seperate steps, first

create a temporay view without where clause, then create the final view

append with the where clause, can somebody understand the mystery
behind it? thanks <<
Your temporay view would make view column pocd01 to ' '. if original
POPT.POCD01 is NULL.
Because in your original view AP, you used function
COALESCE(POPT.POCD01, ' ') for view column pocd01.
Then if you would select this temporay view with same where condition.
where condition will get True. So, the row will be included the
rresult.