473,594 Members | 2,747 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

a strange sql

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.P OCD01, ' ') 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
6 2440
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.P OCD01, ' ') 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
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.P OCD01, ' ') 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
How about this?
where((POCD01<> 'D') and ( POCD01<>'F') and ( POCD01<>'O'))
OR POCD01 IS NULL;

Sep 1 '06 #4
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

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.P OCD01, ' ') 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

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.P OCD01, ' ') 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.P OCD01, ' ') 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1805
by: Arthur | last post by:
I've come across some strange xml, that I need to deal with, it looks like this:- <root> <foo attr="1">Some random strange text. <bar attr="2">blar</bar> <bar attr="3">blar blar</bar> <bar attr="4">blar blar blar</bar> </foo> </root>
2
1955
by: Paul Drummond | last post by:
Hi all, I am developing software for Linux Redhat9 and I have noticed some very strange behaviour when throwing exceptions within a shared library. All our exceptions are derived from std::exception. We have a base class which all processes derive from which is always instantiated in main surrounded by a try/catch(std::exception) which catches all exceptions that have not be handled at a higher level. The catch block cleans up and...
2
8915
by: Olaf | last post by:
I have a frameset page witch contains the myFuc() function. The function is accessed from a page in one of the frames in the frameset. An example is shown below. <input onclick="javaScript:alert('document.forms(0)='+document.forms(0)); parent.myFunc(document.forms(0));" type="button" value="Open" name="Button" ID="Button"> The strange part is that the debug alert says that the document.forms(0) is an object så all seem to be well. But...
7
1656
by: M O J O | last post by:
Hi, I'm developing a asp.net application and ran into a strange css problem. I want all my links to have a dashed underline and when they are hovered, it must change to a solid line. Sounds simple, but it's not working. I've cooked down my output code to show you what I mean. If you run the code below, there's no line under the link, but if you either remove the
25
3709
by: Neil Ginsberg | last post by:
I have a strange situation with my Access 2000 database. I have code in the database which has worked fine for years, and now all of a sudden doesn't work fine on one or two of my client's machines. The code opens MS Word through Automation and then opens a particular Word doc. It's still working fine on most machines; but on one or two of them, the user is getting an Automation Error. The code used is as follows: Dim objWord As...
0
1102
by: unknown | last post by:
Hi, I am developing an online book store with shopping cart. My shopping cart is represented as a Xml server control and I am using an XSLT to render it at the client side. I am using an XmlDocument object as session variable to represent my shopping cart. Initially when the session starts, I am using the XmlDocument with root and no elements to show that no items have been added to the
0
328
by: Kris Vanherck | last post by:
yesterday i started getting this strange error when i try to run my asp.net project: Compiler Error Message: CS0006: Metadata file 'c:\winnt\microsoft.net\framework\v1.1.4322\temporary asp.net files\spsweb\0e3514bf\cb1844e7\assembly\dl2\3b163f 16\00452d31_84e5c301\infragistics.webui.ultrawebgrid.v3.dll' could not be found
11
2578
by: Martin Joergensen | last post by:
Hi, I've encountered a really, *really*, REALLY strange error :-) I have a for-loop and after 8 runs I get strange results...... I mean: A really strange result.... I'm calculating temperatures. T = 20 degrees at all times.... The 2D T-array looks like this:
20
1658
by: SpreadTooThin | last post by:
I have a list and I need to do a custom sort on it... for example: a = #Although not necessarily in order def cmp(i,j): #to be defined in this thread. a.sort(cmp) print a
14
3307
by: blumen | last post by:
Hi all, I'm a newbie in VB.Net Programming.. Hope that some of you can help me to solve this.. I'm working out to read,parse and save textfile into SQL Server. The textfile contains thousands of rows with about 50 coloums every row.. Everythings goes well until I found one textfile with some strange character...seems to be Japanese character(because it's a Japanese company who owns this textfile)
0
7941
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7874
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8368
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8231
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6652
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5404
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3895
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2383
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1476
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.