[Babase] Re: query problem
Karl O. Pinc
kop at meme.com
Mon Sep 18 12:11:09 EDT 2006
On 09/18/2006 10:32:56 AM, Leah Gerber wrote:
> Hello Karl,
>
> I am having a small query problem and was wondering if you have any
> thoughts
> on it. This is the same file I was dealing with when I had to make
> names
> capital.
> There are 17 names (a few duplicates) and a date. i am just trying to
> add
> matgrp and sname to these rows. When i do I lose two rows. Two of the
> names
> are the same but with different dates. I don't understand why they
> are being
> tossed out. It doesn't seem like I would need to use an outer join
> for this.
> I have never had to before. Seems really weird to be losing the rows
> for no
> apparent (to me) reason.
>
> select leah_copy.*, biograph.matgrp, biograph.sname from leah_copy,
> biograph
> where biograph.name=leah_copy.name;
As I thought:
babase=# select '(' || name || ')' from lgerber.leah_copy;
?column?
----------
(ABBY)
(CABANA)
(DUNLIN)
(FACE)
(HONEY)
(KOLA)
(KOLA )
(LIZZY)
(LYENA)
(LYME)
(NIKE)
(NOBEL)
(NUTTY)
(VET)
(VET )
(WADE)
(WIPER)
(17 rows)
As you can see, two of the names end in a space character.
It's probably best to do a trim() function on all the text
columns as well as an upper() function when you load them
into the database. Another possiblity is to put some
validation into the table when you make it, so that
the database knows that particular columns must validate.
That will keep you from having other sorts of spooky
problems like this, especially wierd could be issues
with columns like group or other other support-table
type columns. If you make tables "by hand" then
you put text like this after the column name:
CONSTRAINT "Mstatus on MSTATUSES" REFERENCES mstatuses
(which makes sure that the given column is a value
on the MSTATUSES table.)
Karl <kop at meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
More information about the Babase
mailing list