Re: convert access sql to postgresql

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