Re: sql select query with column 'AS' assignment

DzZero wrote:

> Not sure if this is the right newsgroup to use. I did not see a general
> one or sql statement one for postgres. Please point me to the correct
> location if this is wrong and I apologize for the off topic if it is.
>
>
> I am attempting to do a select in which I force an existing
> column(field) to a specific value. I need to do so in order to group
> data properly.
>
> ie:
> agi_timesheets=# select distinct
> employee_id,first_name,last_name,date,sum(hours),"R" as job_code from
> timesheet group by employee_id,first_name,last_name,date having job_code
> <> 'H' and job_code <> 'V' and date >= '01-01-2002' and date <=
> '01-15-2002';
> ERROR: Attribute 'R' not found
>
> agi_timesheets=# select distinct
> employee_id,first_name,last_name,date,sum(hours),job_code AS "R" from
> timesheet group by employee_id,first_name,last_name,date having job_code
> <> 'H' and job_code <> 'V' and date >= '01-01-2002' and date <=
> '01-15-2002';
> ERROR: Attribute timesheet.job_code must be GROUPed or used in an
> aggregate function
>
> agi_timesheets=# select distinct
> employee_id,first_name,last_name,date,sum(hours),job_code AS 'R' from
> timesheet group by employee_id,first_name,last_name,date having job_code
> <> 'H' and job_code <> 'V' and date >= '01-01-2002' and date <=
> '01-15-2002';
> ERROR: parser: parse error at or near "'"
>
> agi_timesheets=# select distinct
> employee_id,first_name,last_name,date,sum(hours),job_code = 'R' from
> timesheet group by employee_id,first_name,last_name,date having job_code
> <> 'H' and job_code <> 'V' and date >= '01-01-2002' and date <=
> '01-15-2002';
> ERROR: Attribute timesheet.job_code must be GROUPed or used in an
> aggregate function
>
>
> etc. etc. etc. I have tried all possible combinations (or I think so)
> of "R", 'R', R using = or AS on either side of job_code. Nothing seems
> to work.
>
> Several of these combinations work in MySQL, Access, and Oracle. Or at
> least according to those online I have spoke to they do.
>
> Can any explain to me what I am doing wrong? If this is possible in
> PostgreSQL? Or the proper way of doing this? Or even a source of
> information that explains it. The closest source I found was obviously
> the psql documentation but I have yet to find a specific example of what
> I am doing.
>
> Thanks.
>

BTW.If I group by job_code on the last statement I posted it does so but
it groups them as if job_code has the orginal values in it. Also I end
up with something like:

employee_id | first_name | last_name | date | sum | ?column?
-------------+------------+------------+------------+-----+----------
7 | Larry | James | 2002-01-02 | 8 | f

I'm lost. heh