On Nov 16, 2007, at 5:43 PM, Tom Hart wrote:
> Tom Hart wrote:
>> Peter Eisentraut wrote:
>>> Tom Hart wrote:
>>>
>>>> Specifically I'm looking at these two lines
>>>>
>>>> isactive and (mb_mail_cd=0 or mb_mail_cd=1) as ismail,
>>>> ismail and (mb_stat_cd=0 or mb_stat_cd=2) as ispromomail,
>>>>
>>>> which appear to use other fields it's preparing to insert as
>>>> variables
>>>> in the determination of the values of other fields (I told you I
>>>> couldn't figure out how to explain it).
>>>>
>>>
>>> I can't tell more without the exact table definitions, but this
>>> should work just fine.
>>>
>> The error I'm receiving is
>>
>> ERROR: column "isactive" does not exist
>> LINE 26: isactive and (mb_mail_cd=0 or mb_mail_cd=1) as ismail,
>> ^
> I've been doing some googling on sql aliases (my sql knowledge is
> far from impressive) and it appears that column aliases can be
> great for displaying different column names in your output. However
> I was unable to find any information regarding using column aliases
> as variables, like in the code above. I'm not sure why this works
> in access, but does postgreSQL support using a column alias like a
> variable within the query that the alias was defined in?
Isn't the issue that you are not matching the case of the column? I'm
guessing you used some kind of Access dump and loaded it into
postgresql. If you must have capitalization in the column, you need
to quote all references to the column.
Here is the relevant FAQ:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.21
Cheers,
M
