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?
I suppose the lines
mb_stat_cd<>1 as isactive,
mb_stat_cd=0 as ismember,
(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
Could be written
mb_stat_cd<>1 as isactive,
mb_stat_cd=0 as ismember,
((mb_stat_cd<>1) and (mb_mail_cd=0 or mb_mail_cd=1)) as ismail,
(((mb_stat_cd<>1) and (mb_mail_cd=0 or mb_mail_cd=1)) and (mb_stat_cd=0
or mb_stat_cd=2)) as ispromomail
But if postgreSQL does indeed support this, and I can get it to work,
I'd like to be able to use the syntax in the first set.
TIA
Thomas R. Hart II
tomhart@coopfed.org