Horton

Thread

  1. no records returned

    lynn.tilby@asu.edu — 2003-10-24T23:27:16Z

    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

  2. Re: no records returned

    Patrick Welche <prlw1@newn.cam.ac.uk> — 2003-10-25T17:42:11Z

    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

  3. Re: no records returned

    Brian Hirt <bhirt@mobygames.com> — 2003-10-25T17:58:54Z

    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:

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

  4. Re: no records returned

    Darren Ferguson <darren@crystalballinc.com> — 2003-10-25T19:15:56Z

    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

  5. Re: no records returned

    lynn.tilby@asu.edu — 2003-10-26T02:48:52Z

    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
> 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
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

  6. Re: no records returned

    lynn.tilby@asu.edu — 2003-10-26T03:09:38Z

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

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

  7. Re: no records returned

    lynn.tilby@asu.edu — 2003-10-26T03:19:49Z

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

  8. Re: no records returned

    Patrick Welche <prlw1@newn.cam.ac.uk> — 2003-10-26T13:15:26Z

    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

  9. Re: no records returned

    James Moe <jimoe@sohnen-moe.com> — 2003-10-26T22:46:58Z

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