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

a strange sql

P: n/a
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

Aug 31 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Aug 31 '06 #2

P: n/a
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
Aug 31 '06 #3

P: n/a
How about this?
where((POCD01<>'D') and ( POCD01<>'F') and ( POCD01<>'O'))
OR POCD01 IS NULL;

Sep 1 '06 #4

P: n/a
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
Sep 1 '06 #5

P: n/a

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.

Sep 1 '06 #6

P: n/a

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.

Sep 4 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.