Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
Rameshs

New User

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

 Posted: Thu Nov 02, 2006 11:34 am    Post subject: Predicate and blank spaces ! 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

DavidatK

Active Member

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

 Posted: Fri Nov 03, 2006 3:17 am    Post subject: Re: Predicate and blank spaces ! 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
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Inefficient BUILD - VB records paddin... Daniel Prosser SYNCSORT 9 Thu Dec 14, 2017 3:52 pm Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us