Обсуждение: SQL and parse errors
Howdy:
Running Postgres 7.1.3 on RedHat Linux 7.2, kernel 2.4.7-rel 10.
Have an SQL question, which may very well be that
I need a second pair of eyes to trouble shoot the script.
[script]
explain
select
q.qid,
q.year,
m.contract,
m.mbr_num,
m.bu,
m.mbr_sex,
m.product,
m.prov_num,
m.riskgrp,
p.c_spec_1 as spec,
date_larger(m.effdt, q.effdt) as effdt,
date_smaller(m.enddt, q.enddt) as enddt,
-->>> (date_smaller(m.enddt, q.enddt) as enddt - date_larger(m.effdt, q.effdt) as effdt)::float8 / 30.4 as memmonth <<<--
from members m,
product p,
quarters q
where
m.prov_num=p.c_prov_num
--------- make sure *all* the spans overlap one another ----
and not (
m.enddt < q.effdt
or m.effdt > q.enddt
)
;
[/script]
I believe the problem comes from what's marked off by
the arrows. The error I get from this is:
ERROR: parser: parse error at or near "as"
If I comment that out, things work. It *used* to work
before, but for the life of me, I don't recall changing
anything ...
Where is the syntax error?
Thanks!
-X
On Tue, 5 Mar 2002, Johnson, Shaunn wrote: > explain > select > q.qid, > q.year, > m.contract, > m.mbr_num, > m.bu, > m.mbr_sex, > m.product, > m.prov_num, > m.riskgrp, > p.c_spec_1 as spec, > date_larger(m.effdt, q.effdt) as effdt, > date_smaller(m.enddt, q.enddt) as enddt, > -->>> (date_smaller(m.enddt, q.enddt) as enddt - > date_larger(m.effdt, q.effdt) as effdt)::float8 / 30.4 as memmonth <<<-- ^^ - I don't think you can use as clauses inside the expression... take out the extra as enddt and as effdt?
--That's what it was: I took out the 'as name_of_whatever'
in the expression.
--Thanks Stephan / Jim B.
-X
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Tuesday, March 05, 2002 11:15 AM
To: Johnson, Shaunn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL and parse errors
On Tue, 5 Mar 2002, Johnson, Shaunn wrote:
> explain
> select
> q.qid,
> q.year,
> m.contract,
> m.mbr_num,
> m.bu,
> m.mbr_sex,
> m.product,
> m.prov_num,
> m.riskgrp,
> p.c_spec_1 as spec,
> date_larger(m.effdt, q.effdt) as effdt,
> date_smaller(m.enddt, q.enddt) as enddt,
> -->>> (date_smaller(m.enddt, q.enddt) as enddt -
> date_larger(m.effdt, q.effdt) as effdt)::float8 / 30.4 as memmonth <<<--
^^ - I don't think you can use as clauses inside the expression... take
out the extra as enddt and as effdt?
remove the as in "date_larger(m.effdt, q.effdt) as effdt)::float8" on the line you marked Jim > Howdy: > > Running Postgres 7.1.3 on RedHat Linux 7.2, kernel 2.4.7-rel 10. > > Have an SQL question, which may very well be that > I need a second pair of eyes to trouble shoot the script. > > [script] > explain > select > q.qid, > q.year, > m.contract, > m.mbr_num, > m.bu, > m.mbr_sex, > m.product, > m.prov_num, > m.riskgrp, > p.c_spec_1 as spec, > date_larger(m.effdt, q.effdt) as effdt, > date_smaller(m.enddt, q.enddt) as enddt, > -->>> (date_smaller(m.enddt, q.enddt) as enddt - > date_larger(m.effdt, q.effdt) as effdt)::float8 / 30.4 as memmonth <<<-- > from members m, > product p, > quarters q > where > m.prov_num=p.c_prov_num > > --------- make sure *all* the spans overlap one another ---- > and not ( > m.enddt < q.effdt > or m.effdt > q.enddt > ) > ; > [/script] > > I believe the problem comes from what's marked off by > the arrows. The error I get from this is: > > ERROR: parser: parse error at or near "as" > > If I comment that out, things work. It *used* to work > before, but for the life of me, I don't recall changing > anything ... > > Where is the syntax error? > > Thanks! > > -X