RE: FW: Air pocket char fields.
Date: Thu, 8 Dec 2011 10:43:36 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8BF18B61_at_JAXMSG01.crowley.com>
Thanks Norman,
I saw the "and <field> <> ' '" code in a query. I was questioning this logic in the where clause, and trying to investigate it and this behavior.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Norman Dunbar
Sent: Thursday, December 08, 2011 9:21 AM
To: oracle-l_at_freelists.org
Subject: Re: FW: Air pocket char fields.
Hi Joel,
On 07/12/11 20:51, Joel.Patterson_at_crowley.com wrote:
> Shouldn't a CHAR field size 15 return all the records where the field is 15 spaces when requested?
When you compare a CHAR(15) field with a string literal, the literal is
padded on the right with spaces so that you are comparing exactly 15
characters with 15 characters.
That should explain your "= FIFTEEN" through "= ONE" results. You always get all the rows with 15 spaces in.
Your ZERO queries are effectively comparing your field with NULL || '<-- 15 spaces -->' and that results in your field being compared with NULL, so you get nothing back because all you can do with NULL is "IS NULL" or "IS NOT NULL" - you can't add it, concatenate it etc etc.
I *THINK* your "= SIXTEEN" query is truncating the 16 spaces to 15 to make the comparison, which is why you get the same results as above.
(But you know all this!)
Going on to all your "<> ONE" through "<> SIXTEEN" queries, you are saying, each time, get me all the rows where the field is not = 15 spaces.
> Should it not return all rows if<> anything but 15 spaces?
CHAR comparisons are fraught with this sort of thing. I tend not to use
them, unless they are NOT NULL and only one or two characters long. I
did a similar thing in COBOL but that was so long ago, I can't even
remember the PIC for a space filled field!
And the reason that your MIN() = MAX() = 15, is because, when stored, every bit of data is exactly 15 characters long - if you give it shorted data, it is padded to exactly 15.
HTH.
HIUTQC.
Cheers,
Norm.
PS. HIUTQC = Hope I Understood The Question Correctly!
-- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 08 2011 - 09:43:36 CST