no records returned

On Fri, Oct 24, 2003 at 04:27:16PM -0700, Lynn.Tilby@asu.edu wrote:
...

ZRAN |2003-10-03| 20031003| 731491| 20.25| 21.
(609398 rows)

pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN';

How about

pma=> select * from stck_dta_tbl_oprtnl where stck_sym like 'ZRAN%';

? (What is the definition of your table?)

Cheers,

Patrick

is it possible that there are spaces on the end? what type is
stck_sym? if it's varchar or text the padding won't be removed
automatically.

example:

basement=# create table test (t varchar(6));
CREATE TABLE
basement=# INSERT into test values( 'ZRAN ');
INSERT 92249850 1
basement=# select * from test where t = 'ZRAN';
t
---
(0 rows)

basement=#

On Oct 24, 2003, at 5:27 PM, Lynn.Tilby@asu.edu wrote:

Are the fields you are usiong varchar or char.

If they are char fields they will be getting padded if you do not have the
exact amount in the field. i.e.

char(5)

da will be padded da and 0s on the end up to the sizeof the char field.

If varchar then you will be ok.

If you do have a char then use the trim function to do the selects

HTH
Darren

On Fri, 24 Oct 2003 Lynn.Tilby@asu.edu wrote:

running - (PostgreSQL) 7.1.3

I have loaded a table with stock market data.

-
-
-
ZRAN |2003-09-29| 20030929| 731487| 20.81| 20.
ZRAN |2003-09-30| 20030930| 731488| 19.43| 20.1
ZRAN |2003-10-01| 20031001| 731489| 19.82| 19.9
ZRAN |2003-10-02| 20031002| 731490| 19.56| 20.3
ZRAN |2003-10-03| 20031003| 731491| 20.25| 21.
(609398 rows)

pma=> select count(*) from stck_dta_tbl_oprtnl;
count
------
609398

When I do a select * from stck_dta_tbl_oprtnl; I get the whole table
just fine; but when I try and retrieve just 1 record which is out
put when doing the select * ie:

pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN';
stck_sym|dta_date|dta_date_num|dta_date_dys|opn|hi|lw|cls|vol|unk
--------+--------+------------+------------+---+--+--+---+---+---
(0 rows)

I get 0 rows, and no errors from postmaster.

Is there a limit to the number of rows in a table?
I have also tried building a unique index composed of the stck_sym and
dta_date but I still get 0 rows.

I have also noticed that when setting up a cursor that the first fetch
does NOT get the first record in the table, it also returns nothing with
no errors from the postmaster. I am wondering if these bugs are related.

Thanks all for your help!!!!

Lynn

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Darren Ferguson

Darren,

Thanks for the thoughts...

stck_sym is a varchar the rest are int's and floats's.

Do you think that the size of the table could be part of the problem,
at 600000+ records?

Lynn

Hi All,

Solved this problem with a tip from
James Moe <jimoe@sohnen-moe.com> as follows:

Try this:

select * from stck_dta_tbl_oprtnl where stck_sym like 'ZRAN%';

It works PERFECTLY....

THANKS James!!!

Lynn

Quoting Brian Hirt <bhirt@mobygames.com>:

Patrick,

James Moe <jimoe@sohnen-moe.com>
also suggested this...

It WORKS!!!

Could you explain to a newbie WHY the like and % at
the end of the literal works when the normal select does
not?

Thanks,
Lynn

Quoting Patrick Welche <prlw1@newn.cam.ac.uk>:

On Sat, Oct 25, 2003 at 08:19:49PM -0700, Lynn.Tilby@asu.edu wrote:
...

Could you explain to a newbie WHY the like and % at
the end of the literal works when the normal select does
not?

ZRAN |2003-10-03| 20031003| 731491| 20.25| 21.

12345678

From that row, it looks as though your table definition says the first
column is a CHAR(8), rather than say varchar(8) or text. This means that
ZRAN is padded to 8 characters with spaces:

test=# create table zran (a char(8), b varchar(8), c text);
CREATE TABLE
test=# insert into zran values ('ZRAN','ZRAN','ZRAN');
INSERT 28334686 1
test=# select * from zran;
a | b | c
----------+------+------
ZRAN | ZRAN | ZRAN
(1 row)

test=# select * from zran where a='ZRAN ';
a | b | c
----------+------+------
ZRAN | ZRAN | ZRAN
(1 row)

Because we were guessing your table definition, we couldn't just add 4 spaces
after ZRAN, so we suggested "where a like 'ZRAN%'". LIKE understands % to
mean "0 or more anything", so it would match 'ZRANNN' too, which might not
be what you want.. Most sensible might be to change your table definition..

Cheers,

Patrick

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, 25 Oct 2003 20:19:49 -0700 (MST), Lynn.Tilby@asu.edu wrote:

Could you explain to a newbie WHY the like and % at
the end of the literal works when the normal select does
not?

The "%" is a wildcard character in SQL, the same as "*" in many file systems. It
matches 0 or more characters. Also "_" matches any single character, similar to
"?", or "." in regular expressions.
There is likely one or more spaces after the visible characters. Using a "%"
after ZRAN matches ZRAN plus anything else following that pattern.
The implementation of the LIKE operator is a somewhat uneven in my experience.
Depending on the database it can only be used to match strings, or to do a
case-insensitive compare, or is equivalent to "=" with "=" extended to string
operations. In most cases, though, the best practice is to use it for strings
especially when using regular expressions like "%"; and to use "=" for numeric and
exact matching.

- --
jimoe at sohnen-moe dot com
pgp/gpg public key: http://www.keyserver.net/en/
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 5.0 OS/2 for non-commercial use
Comment: PGP 5.0 for OS/2
Charset: cp850

wj8DBQE/nE7isxxMki0foKoRAkJjAJ9lpqeC8y+Go0tXclwIM8XzvaRd1QCgtn8i
26WzEwf8HNPY5iqY6ZckrmY=
=DY5x
-----END PGP SIGNATURE-----