Thread: SQL and parse errors

SQL and parse errors

From
"Johnson, Shaunn"
Date:

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

Re: SQL and parse errors

From
Stephan Szabo
Date:
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?


Re: SQL and parse errors

From
"Johnson, Shaunn"
Date:

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

Re: SQL and parse errors

From
"Jim Buttafuoco"
Date:
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