SEO

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,

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).

Does anybody have any idea what I'm talking about? I sure don't.

Thomas R. Hart II
tomhart@coopfed.org

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 |

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Fri, Nov 16, 2007 at 05:43:44PM -0500, Tom Hart wrote:
> 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?

IIRC this is not mentioned in the SQL standard. Not sure why, maybe the
theory is that all output columns are calculated simultenously which
can't be true if they refer to eachother.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHPiP5IB7bNG8LQkwRApsJAJ9MVYDqmWt4crhkeha8ze FhcmIongCgj3Os
rjAhbjhVq+48z+1XS6Mny+A=
=zyL2
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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.

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?

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?



--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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@coopfed.org

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend