Re: convert access sql to postgresql

Shane Ambler wrote:
> Tom Hart wrote:
>> Hey guys. I have a long piece of sql that I'm trying to take out of
>> an existing Access db and modify to work with Postgresql. I've
>> started trying to convert it, but I've come across a problem that I
>> don't even know how to describe, let alone google. Here's the function
>>
>> INSERT INTO MemberMailingAddress (
>> mb_cn_num,
>> mb_acct_num,
>> isactive,
>> ismember,
>> ismail,
>> ispromomail,
>> ... <trimmed for your convenience> ...
>>
>> )
>> SELECT
>> mb_cn_num,
>> mb_acct_num,
>> 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,
>> ... <trimmed for your convenience> ...
>>
>> FROM member
>> ORDER BY mb_cn_num, mb_acct_num
>> ;
>>
>> 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,
>>
> Lets's clarify something
> - the insert puts data into the MemberMailingAddress table which
> includes the column isactive
> The select retrieves and calculates data from the member table.
> The select calculates a value for the third column that you alias to
> be named isactive.
That's correct.
> Are you expecting the 5th and 6th column (as ismail - as ispromomail)
> to calculate from the 3rd and 5th column of the select or from the
> columns of MemberMailingAddress?
It's not that I expect pgSQL to do this, as much as I've seen access
behave this way, in which column aliases were able to be used within the
same select query in a "variable" type fashion.
> If you expect the later you need to add a join to the
> MemberMailingAddress table to get those columns. (or use a sub-select
> to get the data)
> If you are only calculating from the member table then you will need
> to repeat the calculations instead of referring to the alias. And if
> this is the case how does the insert fit with the issue of the select?
I figured I would have to repeat the calculations (I've tried this with
a small chunk of the sql with success). What I was really looking for
was an answer on whether postgreSQL behaved this way, or if this was
standard SQL. The answer I've been hearing is no on both counts, just
wanted to be sure. Thank you for your reply.

Thomas R. Hart II
tomhart(at)coopfed(dot)org