Search Postgresql Archives

Re: convert access sql to postgresql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx

Get Sheeky @ http://Sheeky.Biz

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

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux