[SQL] please help me with text cast to int ....
I give up.. what don't I understand about casting and
ints and text..
i have a table jobinfo with:
acode text,
jobnumber text default
nextval('public.jobinfo_seq'::text),
jobtitle text
I have about 3000 rows starting with jobnumber = 1000.
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber >= 999 ORDER BY jobnumber ASC;
The above SQL produces no rows. however...
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber >= 200 ORDER BY jobnumber ASC;
produces rows with jobnumber >= 2000
if I change the query with jobnumber >= 201, I get
rows >= 2010.
it is as if there was a silent zero being appended to
the end of my int in the query. What am I missing,
please.
select version();
version
-
PostgreSQL 7.4.2 on powerpc-apple-darwin7.3.0,
compiled by GCC gcc (GCC) 3.3 20030304 (Apple
Computer, Inc. build 1495)
(1 row)
Ted
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] please help me with text cast to int ....
Theodore,
Because jobnumber is declared as text, you are getting "dictionary
order" (lexicographic) ordering on the values. In a dictionary, "abc"
comes after "a", obviously. So indeed "999" will come after
"1000". To get the effect that you want you need to treat jobnumber as
a number. The easiest thing to do would be to change the declaration of
the table. If for some reason you can't do that, you need to do a cast
in the query; that would make your WHERE expression work, but I don't
know about ORDER BY (look it up).
For example:
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber::integer >= 200 ORDER BY jobnumber ASC;
Do you ever have non-numeric values in the jobnumber field? (Is that
why it's declared as text?) If you do you will get problems because
they cannot be converted to integers in order to perform the comparison.
Regards,
--Phil.
Theodore Petrosky wrote:
I give up.. what don't I understand about casting and
ints and text..
i have a table jobinfo with:
acode text,
jobnumber text default
nextval('public.jobinfo_seq'::text),
jobtitle text
I have about 3000 rows starting with jobnumber = 1000.
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber >= 999 ORDER BY jobnumber ASC;
The above SQL produces no rows. however...
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber >= 200 ORDER BY jobnumber ASC;
produces rows with jobnumber >= 2000
if I change the query with jobnumber >= 201, I get
rows >= 2010.
it is as if there was a silent zero being appended to
the end of my int in the query. What am I missing,
please.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] please help me with text cast to int ....
On Sun, 11 Jul 2004, Theodore Petrosky wrote:
> I give up.. what don't I understand about casting and
> ints and text..
>
> i have a table jobinfo with:
>
> acode text,
> jobnumber text default
> nextval('public.jobinfo_seq'::text),
> jobtitle text
>
> I have about 3000 rows starting with jobnumber = 1000.
>
> SELECT jobnumber, jobtitle FROM jobinfo WHERE
> jobnumber >= 999 ORDER BY jobnumber ASC;
This is going to do a textual comparison, not an integer one.
So it's looking for jobnumbers greater than the string '999'.
If you want to treat jobnumber as an integer, CAST(jobnumber as integer)
>= 999 may be what you want. Note, however that it will fail if there are
any non-numeric jobnumber rows. In general, storing numbers in strings
that you want to act upon as numbers is a bad idea.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] please help me with text cast to int ....
Thank you this pointed me to the problem. I have
non castable entries. I had picked up legacy data (for
the jobnumbers [that's why they were text in the first
place]) and some of the jobnumbers where 1162_01
1162_02 so this would fail.
The error message was giving me the correct message
however I failed to see it. Silly me, I kept thinking
that it was an error code that I didn't recognize...
Thanks again.
Ted
--- Stephan Szabo <[EMAIL PROTECTED]>
wrote:
>
> On Sun, 11 Jul 2004, Theodore Petrosky wrote:
>
> > I give up.. what don't I understand about casting
> and
> > ints and text..
> >
> > i have a table jobinfo with:
> >
> > acode text,
> > jobnumber text default
> > nextval('public.jobinfo_seq'::text),
> > jobtitle text
> >
> > I have about 3000 rows starting with jobnumber =
> 1000.
> >
> > SELECT jobnumber, jobtitle FROM jobinfo WHERE
> > jobnumber >= 999 ORDER BY jobnumber ASC;
>
> This is going to do a textual comparison, not an
> integer one.
> So it's looking for jobnumbers greater than the
> string '999'.
>
> If you want to treat jobnumber as an integer,
> CAST(jobnumber as integer)
> >= 999 may be what you want. Note, however that it
> will fail if there are
> any non-numeric jobnumber rows. In general, storing
> numbers in strings
> that you want to act upon as numbers is a bad idea.
>
>
> ---(end of
> broadcast)---
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to
> [EMAIL PROTECTED] so that your
> message can get through to the mailing list
> cleanly
>
__
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
