Обсуждение: Whole record returned in stead of field

Поиск
Список
Период
Сортировка

Whole record returned in stead of field

От
Johann Spies
Дата:
The table definition:

CREATE TABLE isi.isi_l1_publication
(
  article_id character varying(15),
  journal_id character varying(7),
  volume character varying(10),
  issue character varying(10),
  publication_year character varying(4),
  publication_date character varying(20),
  id bigint NOT NULL DEFAULT nextval('isi.pubtmp_id_seq'::regclass),
  CONSTRAINT publ_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

A simple select works as expected:

select article_id, publication_year from isi.isi_l1_publication where article_id='000088352400014':

"000088352400014";"2000"

But this query (which works on a development computer):

WITH UTS AS (select art_id
            from isi.art_country_link
            where countrycode = 'ZA'
            INTERSECT
            select art_id
            from isi.art_country_link
            where
            countrycode = 'CH')
SELECT  A.article_id, A publication_year
            from isi.isi_l1_publication A,
                 UTS B,
                 isi.ritem C     
            where B.art_id = A.article_id
                  and A.publication_year <= '2012'
                  and A.publication_year >= '2000'
                  and C.ut = B.art_id
                  and C.dt  ='@ Article'
            order by A.publication_year;


delivers this result on the server:

"000166055700014";"(000166055700014,35528J0,40,1-2,2000,DEC,2316393)"
"000088352400014";"(000088352400014,12927J0,39,6,2000,JUN,30510144)"
"000166477400015";"(000166477400015,10233J0,11,4,2000,DEC,2530188)"
....

I have seen it once before and could not figured out what is causing it.

Version: Postgresql 9.1

I would like to know...

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: Whole record returned in stead of field

От
Johann Spies
Дата:
Sorry for waisting your time.  I just now saw the missing period between A and publication_year which is causing the problem and explains the 'strange' result.

Regards
Johann


On 3 October 2013 10:08, Johann Spies <johann.spies@gmail.com> wrote:
The table definition:

CREATE TABLE isi.isi_l1_publication
(
  article_id character varying(15),
  journal_id character varying(7),
  volume character varying(10),
  issue character varying(10),
  publication_year character varying(4),
  publication_date character varying(20),
  id bigint NOT NULL DEFAULT nextval('isi.pubtmp_id_seq'::regclass),
  CONSTRAINT publ_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

A simple select works as expected:

select article_id, publication_year from isi.isi_l1_publication where article_id='000088352400014':

"000088352400014";"2000"

But this query (which works on a development computer):

WITH UTS AS (select art_id
            from isi.art_country_link
            where countrycode = 'ZA'
            INTERSECT
            select art_id
            from isi.art_country_link
            where
            countrycode = 'CH')
SELECT  A.article_id, A publication_year
            from isi.isi_l1_publication A,
                 UTS B,
                 isi.ritem C     
            where B.art_id = A.article_id
                  and A.publication_year <= '2012'
                  and A.publication_year >= '2000'
                  and C.ut = B.art_id
                  and C.dt  ='@ Article'
            order by A.publication_year;


delivers this result on the server:

"000166055700014";"(000166055700014,35528J0,40,1-2,2000,DEC,2316393)"
"000088352400014";"(000088352400014,12927J0,39,6,2000,JUN,30510144)"
"000166477400015";"(000166477400015,10233J0,11,4,2000,DEC,2530188)"
....

I have seen it once before and could not figured out what is causing it.

Version: Postgresql 9.1

I would like to know...

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)



--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: Whole record returned in stead of field

От
Andreas Kretschmer
Дата:
Johann Spies <johann.spies@gmail.com> wrote:

> SELECT  A.article_id, A publication_year
>
> I have seen it once before and could not figured out what is causing it.

The point is the missing point between A and publication_year ;-)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


Re: Whole record returned in stead of field

От
Berend Tober
Дата:
Johann Spies wrote:
> SELECT  A.article_id, A publication_year

You are missing a "." between in what you think is the second
column (after the "A").