Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cant group by non integer - ms access - odbc

Cant group by non integer - ms access - odbc

From
Glenn
Date:
Hi
Not sure if this should go in pgsql-odbc or here, but todays my first
real day on postgres, so I'll start here.

I'm trying an aggregate query with groupby's on text fields. As per
various FAQ's I have text fields NOT mapped to LongVARCHAR in the odbc
dsn - but I get an error when I try an view the query --(cut from
log)--> " message='ERROR:  Non-integer constant in GROUP BY' "

Any clues??
tia
--
Glenn <glenn@pip.com.au>

Re: Cant group by non integer - ms access - odbc

From
Ludwig Lim
Date:
Hi Glenn,

--- Glenn <glenn@pip.com.au> wrote:
> I'm trying an aggregate query with groupby's on text
> fields. As per
> various FAQ's I have text fields NOT mapped to
> LongVARCHAR in the odbc
> dsn - but I get an error when I try an view the
> query --(cut from
> log)--> " message='ERROR:  Non-integer constant in
> GROUP BY' "
>
> Any clues??

  What is your query (i.e. sql statement)? Can you
give me the sturcture of the table(s) you are
querying?

ludwig


__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

Re: Cant group by non integer - ms access - odbc

From
Glenn
Date:
Hi Ludwig
Seems I've actually asked the wrong q...
My query was something like

"SELECT f1,f2,count(f3) as countOff3, sum(f4) as countoff4,f5,"-" as f6
FROM mytable
GROUP BY f1,f2,f5,"-""

Once I took out everything to do with f6, then it worked - however I
need f6, as this forms a select to be inserted into another table. So
now my question should be what is the postgres equivalent to this?
Thanks
Glenn

On Wed, 2002-12-18 at 23:34, Ludwig Lim wrote:
> Hi Glenn,
>
> --- Glenn <glenn@pip.com.au> wrote:
> > I'm trying an aggregate query with groupby's on text
> > fields. As per
> > various FAQ's I have text fields NOT mapped to
> > LongVARCHAR in the odbc
> > dsn - but I get an error when I try an view the
> > query --(cut from
> > log)--> " message='ERROR:  Non-integer constant in
> > GROUP BY' "
> >
> > Any clues??
>
>   What is your query (i.e. sql statement)? Can you
> give me the sturcture of the table(s) you are
> querying?
>
> ludwig
>
>
> __________________________________________________
> Do you Yahoo!?
> New DSL Internet Access from SBC & Yahoo!
> http://sbc.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Glenn <glenn@pip.com.au>

Re: Cant group by non integer - ms access - odbc

From
Tom Lane
Date:
Glenn <glenn@pip.com.au> writes:
> Seems I've actually asked the wrong q...
> My query was something like

> "SELECT f1,f2,count(f3) as countOff3, sum(f4) as countoff4,f5,"-" as f6
> FROM mytable
> GROUP BY f1,f2,f5,"-""

You never need to group by a constant, so this would work fine (and be
compliant with the standard, which your given example is not):

SELECT f1,f2,count(f3) as countOff3, sum(f4) as countoff4,f5,"-" as f6
FROM mytable
GROUP BY f1,f2,f5

> Once I took out everything to do with f6, then it worked - however I
> need f6, as this forms a select to be inserted into another table.

Depending on what's happening upstream, it might also help to explicitly
cast the "-" to some specific datatype, eg

    select ..., "-"::varchar as f6 ...

            regards, tom lane

Re: Cant group by non integer - ms access - odbc

From
"Patrick Hatcher"
Date:
Shouldn't this be :
SELECT f1,f2,count(f3) as countOff3, sum(f4) as countoff4,f5,''-'' as f6
FROM mytable
GROUP BY f1,f2,f5,''-''"

2 single quotes.  Double means a literal


Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM





     
                    Glenn
     
                    <glenn@pip.com.au>            To:     Ludwig Lim <lud_nowhere_man@yahoo.com>
     
                    Sent by:                      cc:     PostgreSQL Mailing List <pgsql-novice@postgresql.org>
     
                    pgsql-novice-owner@post       Subject:     Re: [NOVICE] Cant group by non integer - ms access -
odbc     
                    gresql.org
     

     

     
                    12/18/2002 06:47 AM
     

     




Hi Ludwig
Seems I've actually asked the wrong q...
My query was something like

"SELECT f1,f2,count(f3) as countOff3, sum(f4) as countoff4,f5,"-" as f6
FROM mytable
GROUP BY f1,f2,f5,"-""

Once I took out everything to do with f6, then it worked - however I
need f6, as this forms a select to be inserted into another table. So
now my question should be what is the postgres equivalent to this?
Thanks
Glenn

On Wed, 2002-12-18 at 23:34, Ludwig Lim wrote:
> Hi Glenn,
>
> --- Glenn <glenn@pip.com.au> wrote:
> > I'm trying an aggregate query with groupby's on text
> > fields. As per
> > various FAQ's I have text fields NOT mapped to
> > LongVARCHAR in the odbc
> > dsn - but I get an error when I try an view the
> > query --(cut from
> > log)--> " message='ERROR:  Non-integer constant in
> > GROUP BY' "
> >
> > Any clues??
>
>   What is your query (i.e. sql statement)? Can you
> give me the sturcture of the table(s) you are
> querying?
>
> ludwig
>
>
> __________________________________________________
> Do you Yahoo!?
> New DSL Internet Access from SBC & Yahoo!
> http://sbc.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Glenn <glenn@pip.com.au>


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster