Re: convert access sql to postgresql

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,
^

Here are the table definitions for member (from) and
membermailingaddress (to)

current=# \d member
Table "public.member"
Column | Type | Modifiers
-----------------------+---------------+-----------
mb_acct_closed_dt | date |
mb_acct_num | numeric(6,2) | not null
mb_alt_num | text |
mb_backwithhold_perc | integer |
mb_birth_dt | date |
mb_cell_phone | text |
mb_census_tract | numeric(6,2) |
mb_city | text |
mb_cn_num | integer | not null
mb_credit_score | text |
mb_credit_score_date | date |
mb_ctr_cd | text |
mb_drivers_license | text |
mb_email_acct_num | numeric(6,2) |
mb_email_address | text |
mb_email_cn_num | integer |
mb_email_rec | integer |
mb_employ_phone | text |
mb_employer | text |
mb_first_name | text |
mb_flag4 | text |
mb_frst_99r_rec | integer |
mb_frst_at_rec | integer |
mb_frst_cl_rec | integer |
mb_frst_ct_rec | integer |
mb_frst_df_rec | integer |
mb_frst_ira_rec | integer |
mb_frst_ln_rec | integer |
mb_frst_sh_rec | integer |
mb_frst_tran_rec | integer |
mb_head_of_household | text |
mb_hire_dt | date |
mb_home_phone | text |
mb_homebank_status | text |
mb_income | numeric(10,2) |
mb_jand/or_cd | text |
mb_jfirst_name | text |
mb_jlast_name | text |
mb_jmid_init | text |
mb_join_dt | date |
mb_joint_birth_dt | date |
mb_joint_relationship | text |
mb_jssn_num | text |
mb_jsuff | text |
mb_last_name | text |
mb_life_ins_amt | numeric(10,2) |
mb_life_save_amt | numeric(10,2) |
mb_loc_cd | text |
mb_lst_act_dt | date |
mb_lst_tran_cd | text |
mb_lst_tran_rec | text |
mb_m/f_cd | text |
mb_mail_addr1 | text |
mb_mail_addr2 | text |
mb_mail_cd | integer |
mb_mail_city | text |
mb_mail_state | text |
mb_mail_zip_cd | text |
mb_master_rec | integer |
mb_mid_init | text |
mb_misc_cd | text |
mb_ofac_verified | text |
mb_payroll_num | integer |
mb_privacy_code | integer |
mb_prn_state | text |
mb_send_estatement | text |
mb_soc_emp_cd | integer |
mb_soc_sec_num | text |
mb_stat_cd | integer |
mb_state_cd | text |
mb_statement_pages | text |
mb_str_addr1 | text |
mb_str_addr2 | text |
mb_suff | text |
mb_tot_payroll | numeric(10,2) |
mb_vru_status | integer |
mb_w9_ver_cd | integer |
mb_withold | text |
mb_zip_cd | text |
Indexes:
"member_pkey" PRIMARY KEY, btree (mb_cn_num, mb_acct_num)

current=# \d membermailingaddress
Table "public.membermailingaddress"
Column | Type | Modifiers
-----------------+--------------+-----------
mb_cn_num | integer |
mb_acct_num | numeric(6,1) |
isactive | boolean |
ismember | boolean |
ismail | boolean |
ispromomail | boolean |
householdid | numeric |
householdsize | integer |
name1 | text |
name2 | text |
addr1 | text |
addr2 | text |
addr3 | text |
city | text |
state | text |
zip | text |
zip5 | text |
xxprimaryname | text |
xxjointname | text |
xxhouseholdhash | text |