Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Predicate and blank spaces !

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rameshs

New User


Joined: 15 Jun 2005
Posts: 53
Location: India, Chennai

PostPosted: Thu Nov 02, 2006 11:34 am    Post subject: Predicate and blank spaces !
Reply with quote

Last_Name First_Name Company
--------------------------------------
AAAAAA BBBBBB COMP1
CCCCCC DDDDDD COMP2
GGGGGG HHHHHH COMP3
JJJJJJ KKKKKK ' ' (six blank spaces)
XXXXXX YYYYYY ' ' (six blank spaces)
LLLLLL MMMMM Null
NNNNNN OOOOOO Null

1. All the following query returns the same result. why ?
SELECT * FROM SQLTSR.TEST_TABLE WHERE COMPANY = ''; (no blank space)
SELECT * FROM SQLTSR.TEST_TABLE WHERE COMPANY = ' '; (single space
SELECT * FROM SQLTSR.TEST_TABLE WHERE COMPANY = ' '; (triple space)

Result:
JJJJJJ KKKKKK ' ' (blank)
XXXXXX YYYYYY ' '

Thanks,
Ramesh
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Nov 03, 2006 3:17 am    Post subject: Re: Predicate and blank spaces !
Reply with quote

Hello Ramesh,

Before we talk about you specific questions, let?s look at decimal compares.

If we have two variables:

VAR1 PIC S9(9) VALUE 75.
VAR2 PIC S9(3) VALUE 75.

I don?t think there is any question that the value these two variables are the same. Right? Likewise, if you coded

IF VAR2 = 0000075

You know they will compare equal.

If is assumed that there can be any number of leading zeros without affecting the value in the variable. So, you can assume that any smaller variable will have leading zeros added up to the length of the largest variable.

Now, lets talk about the value of nothing. If you were told to put a value on nothing in VAR1, I suspect you would move zero (0) to VAR1, It?s assumed by most people to be a value of nothing. But, it is a value. It?s the integer value between -1 and +1, as the value of 5 is the integer value between 4 and 6. But DB2 recognizes that maybe you really want to say, ?this variable has nothing in it, its value is unassigned, there is nothing in this variable?. This is where the concept of a null value comes in. It says that the variable has not been assigned a value. Therefore, null and non-null nothing are not the same thing.

Now to you question. As in decimal non-null numbers where it is assumed that any number of leading zeros will not affect the value in the variable, char strings assume that any number of training spaces will not affect the value of the variable (in common character strings). We can assume that shorter strings can have spaces (blanks) added to the end of the variable without affecting the value of the string.

So. ?? no space, ? ? one space, and ? ? three spaces have the same string value, and are equivalent of each other. But ?? no spaces, in this context, is not the same as null, or an unassigned value.

Now do you understand you results?

If not, please come back and we can go into this further.

Dave
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
No new posts Removing Extra Spaces in a comma sepa... Puspojit DFSORT/ICETOOL 3 Tue Sep 13, 2016 7:46 am
No new posts Remove intermediate spaces Pallavi Vishwanath SYNCSORT 1 Fri Jan 15, 2016 2:45 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us