RE: FW: Air pocket char fields.

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-l