Обсуждение: SQL Join - MySQL/PostgreSQL difference?

Greetings.

I'm working with a product provided by a third part that interfaces to
data housed in a database of your choice.  Previously, my choice was
MySQL - apparently it handled certain queries too slowly, so I'm giving
PostgreSQL a shot.  Here's the query:

SELECT
  a.Number,
  a.Code,
  a.Text
FROM
  b,
  a
WHERE
  (b.Id = a.Id) AND
  (VersionId = 'key1') AND
  (Category = 'key2') AND
  (b.d_Id = 'key3')
ORDER BY
  a.Number;

(my apologies: I had to 'mangle' the table/column names because of NDA)

So my question is this: would this query operate differently in MySQL
than in PostgreSQL?  The reason I ask is that this query in MySQL
returns results, yet in PostgreSQL it does not.  I read a post about
PostgreSQL not supporting outer joins, but I don't have enough
experience with SQL to determine if this is such a query or not.  Please

advise.

Any help will be (as always) sincerely appreciated.

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Brice Ruth wrote:

> Greetings.
>
> I'm working with a product provided by a third part that interfaces to
> data housed in a database of your choice.  Previously, my choice was
> MySQL - apparently it handled certain queries too slowly, so I'm giving
> PostgreSQL a shot.  Here's the query:
>
> SELECT
>   a.Number,
>   a.Code,
>   a.Text
> FROM
>   b,
>   a
> WHERE
>   (b.Id = a.Id) AND
>   (VersionId = 'key1') AND
>   (Category = 'key2') AND
>   (b.d_Id = 'key3')
> ORDER BY
>   a.Number;
>
> (my apologies: I had to 'mangle' the table/column names because of NDA)
>
> So my question is this: would this query operate differently in MySQL
> than in PostgreSQL?  The reason I ask is that this query in MySQL
> returns results, yet in PostgreSQL it does not.  I read a post about
> PostgreSQL not supporting outer joins, but I don't have enough
> experience with SQL to determine if this is such a query or not.  Please
>
> advise.
>
> Any help will be (as always) sincerely appreciated.
>
> --
> Brice Ruth
> WebProjkt, Inc.
> VP, Director of Internet Technology
> http://www.webprojkt.com/

It should work the same in both.  The only thing I notice is that not all
the field names are qualified with table names or aliases.  That can lead
to ambiguity, but the query would blow up on both databases if that were a
problem.

Ian

Here's something to chew on ...

The following snippet of SQL produces a result:

--
CREATE TABLE TBLPEMDRUGLINK
(
    DRUGID                      VARCHAR(10) NOT NULL,
    MONOGRAPHID                 VARCHAR(10) NOT NULL,
    CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
);
CREATE TABLE TBLFDBMONO
(
    VERSIONID                   VARCHAR(10) NOT NULL,
    CATEGORY                    VARCHAR(10) NOT NULL,
    MONOGRAPHID                 VARCHAR(10) NOT NULL,
    SEQUENCENUMBER              INT2 NOT NULL,
    SECTIONCODE                 VARCHAR(1),
    LINETEXT                    VARCHAR(255),
    CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
MONOGRAPHID, SEQUENCENUMBER)
);
insert into tblpemdruglink values ('DG-5039','2008');
insert into tblfdbmono values ('FDB-PE','PEM','2008',8,'A','some info to display');
SELECT sequencenumber,sectioncode,linetext
  FROM tblfdbmono fdb, tblpemdruglink pem WHERE
   fdb.monographid=pem.monographid AND
   fdb.versionid='FDB-PE' AND
   fdb.category='PEM' AND
   pem.drugid='DG-5039';
--

This puts some 'fake' data into the tables, but the fake data resembles
the true data.  Now, when I load the tables with the true data using:

COPY tblpemdruglink FROM '/tmp/Data/db_files/tblPEMDrugLink.txt' USING
DELIMITERS '|';
COPY tblfdbmono FROM '/tmp/Data/db_files/tblFDBMono.txt' USING
DELIMITERS '|';

and then run the same SELECT as before:

SELECT sequencenumber,sectioncode,linetext
  FROM tblfdbmono fdb, tblpemdruglink pem WHERE
   fdb.monographid=pem.monographid AND
   fdb.versionid='FDB-PE' AND
   fdb.category='PEM' AND
   pem.drugid='DG-5039';

I get 0 results.  So the logical conclusion: the dataset doesn't contain
a record that satisfies the query.  However, the following queries
represent the 'manual join' that the join above automates:

SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';

> drugid  | monographid
>---------+-------------
> DG-5039 | 2008
>(1 row)

SELECT sequencenumber, sectioncode, linetext FROM tblfdbmono fdb WHERE
 fdb.monographid='2008' AND
 fdb.versionid='FDB-PE' AND
 fdb.category='PEM';

> sequencenumber | sectioncode | linetext
>----------------+-------------+---------------
>{************** results omitted *************)
>(64 rows)

So if the 'manual join' produces data yet the actual join does not, then
the logical conclusion above doesn't hold and something is wrong
(besides the data set).  That's where I need help.  I can stare at the
SQL all day & all night long and not figure anything out ... I'm not an
expert at SQL and this is the first time I'm using PostgreSQL.  One
thing I can say, however, is that mirroring this in MySQL (and allowing
for case-sensitivity) produces results (it takes a few seconds because
the tblFDBMono table is actually quite large).  What's going on??  Any
help at all is most sincerely appreciated.  Hell, I wouldn't mind
coughin' up the $$ for a support incident if I thought that would help
... but I'm not entirely sure where the problem is to warrant a support incident.

Please respond to this newsgroup, the mailing list (which should be
mirrored on the newsgroup), and/or to me personally.  I'm twiddlin' my
thumbs until I can figure this one out.

Regards,
Brice Ruth

Brice -
What's the result of

select monographid,length(monographid) from tblpemdruglink where
monographid ~ '^2008';

It occurs to me that your delimited text file may have padded values,
and "=" insists on exact matches for VARCHAR.

update tblpemdruglink set monographid=btrim(monographid);

might help, if the first query returns anything but 4.

Ross

On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote:
> Here's something to chew on ...
>
> The following snippet of SQL produces a result:
>

<SNIP>

> Please respond to this newsgroup, the mailing list (which should be
> mirrored on the newsgroup), and/or to me personally.  I'm twiddlin' my
> thumbs until I can figure this one out.
>
> Regards,
> Brice Ruth

Ross,

Thanx for the heads up on this.  The select did indeed return something
other than four: 5.  I updated as you suggested, but that alone didn't
fix the problem.  I'm updating tblFDBMono now with the same type of
'fix' to see if this is the root of the problem.  Is '=' handled
differently between PostgreSQL and MySQL in this case?

-Brice

"Ross J. Reedstrom" wrote:
>
> Brice -
> What's the result of
>
> select monographid,length(monographid) from tblpemdruglink where
> monographid ~ '^2008';
>
> It occurs to me that your delimited text file may have padded values,
> and "=" insists on exact matches for VARCHAR.
>
> update tblpemdruglink set monographid=btrim(monographid);
>
> might help, if the first query returns anything but 4.
>
> Ross
>
> On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote:
> > Here's something to chew on ...
> >
> > The following snippet of SQL produces a result:
> >
>
> <SNIP>
>
> > Please respond to this newsgroup, the mailing list (which should be
> > mirrored on the newsgroup), and/or to me personally.  I'm twiddlin' my
> > thumbs until I can figure this one out.
> >
> > Regards,
> > Brice Ruth

Unfortunately ... that didn't seem to help :(  I used btrim on all the
fields that were part of an equals (=) statement and reran the select
and got the same result (0 rows).  After I was in the process of
updating the tables, I thought that this may fail ... since again, the
'manual join' of these two tables returns the correct results ... and it
uses the same equality tests as the SQL join.

I'm more than happy to keep tryin' different things, though ... anything
to get to the bottom of this.

Regards,
Brice Ruth

"Ross J. Reedstrom" wrote:
>
> Brice -
> What's the result of
>
> select monographid,length(monographid) from tblpemdruglink where
> monographid ~ '^2008';
>
> It occurs to me that your delimited text file may have padded values,
> and "=" insists on exact matches for VARCHAR.
>
> update tblpemdruglink set monographid=btrim(monographid);
>
> might help, if the first query returns anything but 4.
>
> Ross
>
> On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote:
> > Here's something to chew on ...
> >
> > The following snippet of SQL produces a result:
> >
>
> <SNIP>
>
> > Please respond to this newsgroup, the mailing list (which should be
> > mirrored on the newsgroup), and/or to me personally.  I'm twiddlin' my
> > thumbs until I can figure this one out.
> >
> > Regards,
> > Brice Ruth

FYI:  Here are the table definitions:

CREATE TABLE TBLPEMDRUGLINK
(
    DRUGID                      VARCHAR(10) NOT NULL,
    MONOGRAPHID                 VARCHAR(10) NOT NULL,
    CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
);

CREATE TABLE TBLFDBMONO
(
    VERSIONID                   VARCHAR(10) NOT NULL,
    CATEGORY                    VARCHAR(10) NOT NULL,
    MONOGRAPHID                 VARCHAR(10) NOT NULL,
    SEQUENCENUMBER              SMALLINT NOT NULL,
    SECTIONCODE                 VARCHAR(1),
    LINETEXT                    VARCHAR(255),
    CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
MONOGRAPHID, SEQUENCENUMBER)
);

Running the following query:

Query1: SELECT sequencenumber,sectioncode,linetext
Query1:   FROM tblfdbmono fdb, tblpemdruglink pem WHERE
Query1:    fdb.monographid=pem.monographid AND
Query1:    fdb.versionid='FDB-PE' AND
Query1:    fdb.category='PEM' AND
Query1:    pem.drugid='DG-5039';

returns 0 rows.

However, the following two queries produce results:

Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';

Query3: SELECT * FROM tblfdbmono WHERE
Query3:   monographid='2008' AND
Query3:   versionid='FDB-PE' AND
Query3:   category='PEM';

To my knowledge, Query1 is the join that should produce the same results
as the manual join represented by queries 2 & 3.

What's going on?

-Brice

Ian Harding wrote:
>
> Brice Ruth wrote:
>
> > Greetings.
> >
> > I'm working with a product provided by a third part that interfaces to
> > data housed in a database of your choice.  Previously, my choice was
> > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > PostgreSQL a shot.  Here's the query:
> >
> > SELECT
> >   a.Number,
> >   a.Code,
> >   a.Text
> > FROM
> >   b,
> >   a
> > WHERE
> >   (b.Id = a.Id) AND
> >   (VersionId = 'key1') AND
> >   (Category = 'key2') AND
> >   (b.d_Id = 'key3')
> > ORDER BY
> >   a.Number;
> >
> > (my apologies: I had to 'mangle' the table/column names because of NDA)
> >
> > So my question is this: would this query operate differently in MySQL
> > than in PostgreSQL?  The reason I ask is that this query in MySQL
> > returns results, yet in PostgreSQL it does not.  I read a post about
> > PostgreSQL not supporting outer joins, but I don't have enough
> > experience with SQL to determine if this is such a query or not.  Please
> >
> > advise.
> >
> > Any help will be (as always) sincerely appreciated.
> >
> > --
> > Brice Ruth
> > WebProjkt, Inc.
> > VP, Director of Internet Technology
> > http://www.webprojkt.com/
>
> It should work the same in both.  The only thing I notice is that not all
> the field names are qualified with table names or aliases.  That can lead
> to ambiguity, but the query would blow up on both databases if that were a
> problem.
>
> Ian

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Run the following query:

SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
3;

is anything returned? If not, that is your problem (no rows exists with
matching monographid's).  If information is returned, however, pick an
arbitrary row, and plug the data into the following query (you will have
a valid where clause then):

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
pem.drugid = '<<DRUG ID>>'

Also, you may want to try qualifying your table names, i.e.:

SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Tue, 6 Feb 2001, Brice Ruth wrote:

> FYI:  Here are the table definitions:
>
> CREATE TABLE TBLPEMDRUGLINK
> (
>     DRUGID                      VARCHAR(10) NOT NULL,
>     MONOGRAPHID                 VARCHAR(10) NOT NULL,
>     CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
> );
>
> CREATE TABLE TBLFDBMONO
> (
>     VERSIONID                   VARCHAR(10) NOT NULL,
>     CATEGORY                    VARCHAR(10) NOT NULL,
>     MONOGRAPHID                 VARCHAR(10) NOT NULL,
>     SEQUENCENUMBER              SMALLINT NOT NULL,
>     SECTIONCODE                 VARCHAR(1),
>     LINETEXT                    VARCHAR(255),
>     CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
> MONOGRAPHID, SEQUENCENUMBER)
> );
>
> Running the following query:
>
> Query1: SELECT sequencenumber,sectioncode,linetext
> Query1:   FROM tblfdbmono fdb, tblpemdruglink pem WHERE
> Query1:    fdb.monographid=pem.monographid AND
> Query1:    fdb.versionid='FDB-PE' AND
> Query1:    fdb.category='PEM' AND
> Query1:    pem.drugid='DG-5039';
>
> returns 0 rows.
>
> However, the following two queries produce results:
>
> Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
>
> Query3: SELECT * FROM tblfdbmono WHERE
> Query3:   monographid='2008' AND
> Query3:   versionid='FDB-PE' AND
> Query3:   category='PEM';
>
> To my knowledge, Query1 is the join that should produce the same results
> as the manual join represented by queries 2 & 3.
>
> What's going on?
>
> -Brice
>
> Ian Harding wrote:
> >
> > Brice Ruth wrote:
> >
> > > Greetings.
> > >
> > > I'm working with a product provided by a third part that interfaces to
> > > data housed in a database of your choice.  Previously, my choice was
> > > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > > PostgreSQL a shot.  Here's the query:
> > >
> > > SELECT
> > >   a.Number,
> > >   a.Code,
> > >   a.Text
> > > FROM
> > >   b,
> > >   a
> > > WHERE
> > >   (b.Id = a.Id) AND
> > >   (VersionId = 'key1') AND
> > >   (Category = 'key2') AND
> > >   (b.d_Id = 'key3')
> > > ORDER BY
> > >   a.Number;
> > >
> > > (my apologies: I had to 'mangle' the table/column names because of NDA)
> > >
> > > So my question is this: would this query operate differently in MySQL
> > > than in PostgreSQL?  The reason I ask is that this query in MySQL
> > > returns results, yet in PostgreSQL it does not.  I read a post about
> > > PostgreSQL not supporting outer joins, but I don't have enough
> > > experience with SQL to determine if this is such a query or not.  Please
> > >
> > > advise.
> > >
> > > Any help will be (as always) sincerely appreciated.
> > >
> > > --
> > > Brice Ruth
> > > WebProjkt, Inc.
> > > VP, Director of Internet Technology
> > > http://www.webprojkt.com/
> >
> > It should work the same in both.  The only thing I notice is that not all
> > the field names are qualified with table names or aliases.  That can lead
> > to ambiguity, but the query would blow up on both databases if that were a
> > problem.
> >
> > Ian
>
> --
> Brice Ruth
> WebProjkt, Inc.
> VP, Director of Internet Technology
> http://www.webprojkt.com/
>

I believe we're getting closer to the problem here.  When I run the
first query below, I get 0 results, which should be wrong.  When I run
the query:

SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';

I get 5 returned to me, even though the string is only 4 ('2008').
However, after doing:

UPDATE tblpemdruglink SET monographid=trim(monographid);

and rerunning the query above, I still get 5, not 4.  Is something being
imported incorrectly by the COPY ... FROM?  Any way for me to tell if
there are other non-printing characters in there?

Thank you all for the continued support on this.

Regards,
Brice Ruth

Michael Fork wrote:
>
> Run the following query:
>
> SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
> tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
> 3;
>
> is anything returned? If not, that is your problem (no rows exists with
> matching monographid's).  If information is returned, however, pick an
> arbitrary row, and plug the data into the following query (you will have
> a valid where clause then):
>
> SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
> fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
> pem.drugid = '<<DRUG ID>>'
>
> Also, you may want to try qualifying your table names, i.e.:
>
> SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
> fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Tue, 6 Feb 2001, Brice Ruth wrote:
>
> > FYI:  Here are the table definitions:
> >
> > CREATE TABLE TBLPEMDRUGLINK
> > (
> >     DRUGID                      VARCHAR(10) NOT NULL,
> >     MONOGRAPHID                 VARCHAR(10) NOT NULL,
> >     CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
> > );
> >
> > CREATE TABLE TBLFDBMONO
> > (
> >     VERSIONID                   VARCHAR(10) NOT NULL,
> >     CATEGORY                    VARCHAR(10) NOT NULL,
> >     MONOGRAPHID                 VARCHAR(10) NOT NULL,
> >     SEQUENCENUMBER              SMALLINT NOT NULL,
> >     SECTIONCODE                 VARCHAR(1),
> >     LINETEXT                    VARCHAR(255),
> >     CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
> > MONOGRAPHID, SEQUENCENUMBER)
> > );
> >
> > Running the following query:
> >
> > Query1: SELECT sequencenumber,sectioncode,linetext
> > Query1:   FROM tblfdbmono fdb, tblpemdruglink pem WHERE
> > Query1:    fdb.monographid=pem.monographid AND
> > Query1:    fdb.versionid='FDB-PE' AND
> > Query1:    fdb.category='PEM' AND
> > Query1:    pem.drugid='DG-5039';
> >
> > returns 0 rows.
> >
> > However, the following two queries produce results:
> >
> > Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
> >
> > Query3: SELECT * FROM tblfdbmono WHERE
> > Query3:   monographid='2008' AND
> > Query3:   versionid='FDB-PE' AND
> > Query3:   category='PEM';
> >
> > To my knowledge, Query1 is the join that should produce the same results
> > as the manual join represented by queries 2 & 3.
> >
> > What's going on?
> >
> > -Brice
> >
> > Ian Harding wrote:
> > >
> > > Brice Ruth wrote:
> > >
> > > > Greetings.
> > > >
> > > > I'm working with a product provided by a third part that interfaces to
> > > > data housed in a database of your choice.  Previously, my choice was
> > > > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > > > PostgreSQL a shot.  Here's the query:
> > > >
> > > > SELECT
> > > >   a.Number,
> > > >   a.Code,
> > > >   a.Text
> > > > FROM
> > > >   b,
> > > >   a
> > > > WHERE
> > > >   (b.Id = a.Id) AND
> > > >   (VersionId = 'key1') AND
> > > >   (Category = 'key2') AND
> > > >   (b.d_Id = 'key3')
> > > > ORDER BY
> > > >   a.Number;
> > > >
> > > > (my apologies: I had to 'mangle' the table/column names because of NDA)
> > > >
> > > > So my question is this: would this query operate differently in MySQL
> > > > than in PostgreSQL?  The reason I ask is that this query in MySQL
> > > > returns results, yet in PostgreSQL it does not.  I read a post about
> > > > PostgreSQL not supporting outer joins, but I don't have enough
> > > > experience with SQL to determine if this is such a query or not.  Please
> > > >
> > > > advise.
> > > >
> > > > Any help will be (as always) sincerely appreciated.
> > > >
> > > > --
> > > > Brice Ruth
> > > > WebProjkt, Inc.
> > > > VP, Director of Internet Technology
> > > > http://www.webprojkt.com/
> > >
> > > It should work the same in both.  The only thing I notice is that not all
> > > the field names are qualified with table names or aliases.  That can lead
> > > to ambiguity, but the query would blow up on both databases if that were a
> > > problem.
> > >
> > > Ian
> >
> > --
> > Brice Ruth
> > WebProjkt, Inc.
> > VP, Director of Internet Technology
> > http://www.webprojkt.com/
> >

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Brice Ruth <brice@webprojkt.com> writes:
> SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
> I get 5 returned to me, even though the string is only 4 ('2008').

Okay, so you've got some invisible character in there, but not a space
(else the trim() woulda got rid of it).  Tab maybe?  Might want to go
back and examine your original data file more carefully, with an editor
that will show you control characters and such.

            regards, tom lane

You could try this to see if it makes a difference

UPDATE tblpemdruglink SET monographid = substr(monographid, 1,
length(monographid) - 1)

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Wed, 7 Feb 2001, Brice Ruth wrote:

> I believe we're getting closer to the problem here.  When I run the
> first query below, I get 0 results, which should be wrong.  When I run
> the query:
>
> SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
>
> I get 5 returned to me, even though the string is only 4 ('2008').
> However, after doing:
>
> UPDATE tblpemdruglink SET monographid=trim(monographid);
>
> and rerunning the query above, I still get 5, not 4.  Is something being
> imported incorrectly by the COPY ... FROM?  Any way for me to tell if
> there are other non-printing characters in there?
>
> Thank you all for the continued support on this.
>
> Regards,
> Brice Ruth
>
> Michael Fork wrote:
> >
> > Run the following query:
> >
> > SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
> > tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
> > 3;
> >
> > is anything returned? If not, that is your problem (no rows exists with
> > matching monographid's).  If information is returned, however, pick an
> > arbitrary row, and plug the data into the following query (you will have
> > a valid where clause then):
> >
> > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> > fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
> > fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
> > pem.drugid = '<<DRUG ID>>'
> >
> > Also, you may want to try qualifying your table names, i.e.:
> >
> > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> > fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
> > fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';
> >
> > Michael Fork - CCNA - MCP - A+
> > Network Support - Toledo Internet Access - Toledo Ohio
> >
> > On Tue, 6 Feb 2001, Brice Ruth wrote:
> >
> > > FYI:  Here are the table definitions:
> > >
> > > CREATE TABLE TBLPEMDRUGLINK
> > > (
> > >     DRUGID                      VARCHAR(10) NOT NULL,
> > >     MONOGRAPHID                 VARCHAR(10) NOT NULL,
> > >     CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
> > > );
> > >
> > > CREATE TABLE TBLFDBMONO
> > > (
> > >     VERSIONID                   VARCHAR(10) NOT NULL,
> > >     CATEGORY                    VARCHAR(10) NOT NULL,
> > >     MONOGRAPHID                 VARCHAR(10) NOT NULL,
> > >     SEQUENCENUMBER              SMALLINT NOT NULL,
> > >     SECTIONCODE                 VARCHAR(1),
> > >     LINETEXT                    VARCHAR(255),
> > >     CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
> > > MONOGRAPHID, SEQUENCENUMBER)
> > > );
> > >
> > > Running the following query:
> > >
> > > Query1: SELECT sequencenumber,sectioncode,linetext
> > > Query1:   FROM tblfdbmono fdb, tblpemdruglink pem WHERE
> > > Query1:    fdb.monographid=pem.monographid AND
> > > Query1:    fdb.versionid='FDB-PE' AND
> > > Query1:    fdb.category='PEM' AND
> > > Query1:    pem.drugid='DG-5039';
> > >
> > > returns 0 rows.
> > >
> > > However, the following two queries produce results:
> > >
> > > Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
> > >
> > > Query3: SELECT * FROM tblfdbmono WHERE
> > > Query3:   monographid='2008' AND
> > > Query3:   versionid='FDB-PE' AND
> > > Query3:   category='PEM';
> > >
> > > To my knowledge, Query1 is the join that should produce the same results
> > > as the manual join represented by queries 2 & 3.
> > >
> > > What's going on?
> > >
> > > -Brice
> > >
> > > Ian Harding wrote:
> > > >
> > > > Brice Ruth wrote:
> > > >
> > > > > Greetings.
> > > > >
> > > > > I'm working with a product provided by a third part that interfaces to
> > > > > data housed in a database of your choice.  Previously, my choice was
> > > > > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > > > > PostgreSQL a shot.  Here's the query:
> > > > >
> > > > > SELECT
> > > > >   a.Number,
> > > > >   a.Code,
> > > > >   a.Text
> > > > > FROM
> > > > >   b,
> > > > >   a
> > > > > WHERE
> > > > >   (b.Id = a.Id) AND
> > > > >   (VersionId = 'key1') AND
> > > > >   (Category = 'key2') AND
> > > > >   (b.d_Id = 'key3')
> > > > > ORDER BY
> > > > >   a.Number;
> > > > >
> > > > > (my apologies: I had to 'mangle' the table/column names because of NDA)
> > > > >
> > > > > So my question is this: would this query operate differently in MySQL
> > > > > than in PostgreSQL?  The reason I ask is that this query in MySQL
> > > > > returns results, yet in PostgreSQL it does not.  I read a post about
> > > > > PostgreSQL not supporting outer joins, but I don't have enough
> > > > > experience with SQL to determine if this is such a query or not.  Please
> > > > >
> > > > > advise.
> > > > >
> > > > > Any help will be (as always) sincerely appreciated.
> > > > >
> > > > > --
> > > > > Brice Ruth
> > > > > WebProjkt, Inc.
> > > > > VP, Director of Internet Technology
> > > > > http://www.webprojkt.com/
> > > >
> > > > It should work the same in both.  The only thing I notice is that not all
> > > > the field names are qualified with table names or aliases.  That can lead
> > > > to ambiguity, but the query would blow up on both databases if that were a
> > > > problem.
> > > >
> > > > Ian
> > >
> > > --
> > > Brice Ruth
> > > WebProjkt, Inc.
> > > VP, Director of Internet Technology
> > > http://www.webprojkt.com/
> > >
>
> --
> Brice Ruth
> WebProjkt, Inc.
> VP, Director of Internet Technology
> http://www.webprojkt.com/
>

Here's my latest tactic: I'm guessing that the data 'corruption' has
something to do with the way the data was exported from the original
database by the third party ... probably something with the cr/lf
linebreaks or something to that effect (the data field in question
happens to be the last field in a line in the data file).  So, I loaded
up all the files in my text editor (BBedit) and saved 'em back out w/
UNIX linebreaks, then transferred the files as a bzipped tar over to the
server.  I deleted all the content from the db and am reimporting via
'COPY ... FROM' as I write this.  Its about 100MB of data so it'll take
a few minutes to import.

I'll report back any successes ... if things fail again, I'll follow all
the advice I've gotten to this point then report back with any
discoveries.  Thank you SO much for all the help with this.  I really
appreciate it tremendously.

-Brice

Tom Lane wrote:
>
> Brice Ruth <brice@webprojkt.com> writes:
> > SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
> > I get 5 returned to me, even though the string is only 4 ('2008').
>
> Okay, so you've got some invisible character in there, but not a space
> (else the trim() woulda got rid of it).  Tab maybe?  Might want to go
> back and examine your original data file more carefully, with an editor
> that will show you control characters and such.
>
>                         regards, tom lane

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Brice Ruth <brice@webprojkt.com> writes:
> Here's my latest tactic: I'm guessing that the data 'corruption' has
> something to do with the way the data was exported from the original
> database by the third party ... probably something with the cr/lf
> linebreaks or something to that effect (the data field in question
> happens to be the last field in a line in the data file).

Ooooh ... the queries you were showing us made it look like the column
was not the last one, so I hadn't thought of that.  Almost certainly,
your extra character is a CR.  Postgres expects plain LF as newline in
COPY data files; if the newlines are actually CR/LF then the CRs will
be taken as part of the last data field.

            regards, tom lane

Is there a simple (unix) command I can run on text files to convert
cr/lf to lf?  The way I did it seemed pretty ass-backward to me (not to
mention time consuming).

-Brice

Tom Lane wrote:
>
> Brice Ruth <brice@webprojkt.com> writes:
> > Here's my latest tactic: I'm guessing that the data 'corruption' has
> > something to do with the way the data was exported from the original
> > database by the third party ... probably something with the cr/lf
> > linebreaks or something to that effect (the data field in question
> > happens to be the last field in a line in the data file).
>
> Ooooh ... the queries you were showing us made it look like the column
> was not the last one, so I hadn't thought of that.  Almost certainly,
> your extra character is a CR.  Postgres expects plain LF as newline in
> COPY data files; if the newlines are actually CR/LF then the CRs will
> be taken as part of the last data field.
>
>                         regards, tom lane

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

Brice Ruth <brice@webprojkt.com> writes:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?

You could strip out CRs entirely with

    tr -d '\015'

            regards, tom lane

heya

there are a couple of good example on how to do this in the perl
cookbook

the trim function in chapter one might help

the source code from the book is avail at ora.com

On Wed, 7 Feb 2001, Brice Ruth wrote:

> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?  The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).

perl -pi -e 's/\cM\\g' <filename>

will do the trick, assuming you have Perl instaleld on your system.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Money will say more in one moment than the most eloquent lover can in years.

I believe the cr/lf was the problem.  I reran the original query:

SELECT
  tblFDBMono.SequenceNumber,
  tblFDBMono.SectionCode,
  tblFDBMono.LineText
FROM
  tblPEMDrugLink,
  tblFDBMono
WHERE
  (tblPEMDrugLink.MonographId = tblFDBMono.MonographId) AND
  (VersionId = 'FDB-PE') AND
  (Category = 'PEM') AND
  (tblPEMDrugLink.DrugId = 'DG-5039')
ORDER BY
  tblFDBMono.SequenceNumber

And was returned the results I expected.  Sweetness.  Damned stupid that
it took me this long to figure this out ... I have the PostgreSQL book,
but I must have missed the section where it talked about COPY ... FROM
only taking the LF and leaving the CR.  I knew the files were exported
in 'DOS' format (according to vi) ... and in the back of my mind I
thought that could screw things up.  Hrmpf.  At least I got it figured out.

Thanx a ton, guys.

"Brett W. McCoy" wrote:
>
> On Wed, 7 Feb 2001, Brice Ruth wrote:
>
> > Is there a simple (unix) command I can run on text files to convert
> > cr/lf to lf?  The way I did it seemed pretty ass-backward to me (not to
> > mention time consuming).
>
> perl -pi -e 's/\cM\\g' <filename>
>
> will do the trick, assuming you have Perl instaleld on your system.
>
> -- Brett
>                                      http://www.chapelperilous.net/~bmccoy/
> ---------------------------------------------------------------------------
> Money will say more in one moment than the most eloquent lover can in years.

--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/

On Thu, 08 Feb 2001 05:38, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?  The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).

This little heiroglyph will convert all text files rescued from that man's
system in a single directory to the format required by the one true way.

for f in `echo *`;  #  Change this to select files by regex if needed
do
 echo $f;
 cat $f | tr -d "\015\032" > $f.unix;
 mv $f.unix $f;
done

Don't forget to remove the control Z file terminator as well as the
superfluous CRs. It could cause very obtuse problems. Assumes you have
write permission to the directory.

On Wed, Feb 07, 2001 at 10:38:53AM -0600, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?  The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
> 
> -Brice

On many systems (linux at least) there is a command dos2unix, which is
actually an alias for `recode ibmpc:`. But that will take care of more
than just CR, e.g. umlauts, diacritics, etc..

Albert.


-- 

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------

On Wed, 7 Feb 2001, Brice Ruth wrote:

> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?  The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).

perl -pi~ -e 's/\r//g' file1 file2 ... fileN


--
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/