Sybase TSQL to PostgreSQL [Archive]

hp9000

12-18-01, 13:31

Hello,

I'm trying to move some code from Sybase to PostgreSQL and one of the things that is giving me problems is the outer joins. So I'll ask you to please take a look at the following code and tell me if they should do the same:

--Sybase code
select
displayID,
seqNum,
hiddenChildFlg,
parentNum,
timeDispCd,
timeDispCd.codeDesc timeDispCdDesc,
dataDispCd,
dataDispCd.codeDesc dataDispCdDesc,
advDispCd,
advDispCd.codeDesc advDispCdDesc,
clarcID,
trnsfrmrList,
descriptor,
units,
highScale,
highScaleCd,
highScaleCd.codeDesc highScaleCdDesc,
lowScale,
lowScaleCd,
lowScaleCd.codeDesc lowScaleCdDesc,
formatStr,
delFltrList
from ChartRow, timeDispCd, dataDispCd, advDispCd,
highScaleCd, lowScaleCd
where
ChartRow.timeDispCd *= timeDispCd.code and
ChartRow.dataDispCd *= dataDispCd.code and
ChartRow.advDispCd *= advDispCd.code and
ChartRow.highScaleCd *= highScaleCd.code and
ChartRow.lowScaleCd *= lowScaleCd.code

--PostgreSQL code
select
displayID,
seqNum,
hiddenChildFlg,
parentNum,
timeDispCd,
timeDispCd.codeDesc as timeDispCdDesc,
dataDispCd,
dataDispCd.codeDesc as dataDispCdDesc,
advDispCd,
advDispCd.codeDesc as advDispCdDesc,
clarcID,
trnsfrmrList,
descriptor,
units,
highScale,
highScaleCd,
highScaleCd.codeDesc as highScaleCdDesc,
lowScale,
lowScaleCd,
lowScaleCd.codeDesc as lowScaleCdDesc,
formatStr,
delFltrList
from
ChartRow
right join timeDispCd on (ChartRow.timeDispCd = timeDispCd.code)
right join dataDispCd on (ChartRow.dataDispCd = dataDispCd.code)
right join advDispCd on (ChartRow.advDispCd = advDispCd.code)
right join highScaleCd on (ChartRow.highScaleCd = highScaleCd.code)
right join lowScaleCd on (ChartRow.lowScaleCd = lowScaleCd.code)
;

Thanks in advance!
Gabriel

MattR

12-18-01, 14:21

I belive *= is synonymous with LEFT OUTER JOIN. I am not 100% sure, though.

hp9000

12-19-01, 10:06

I looked around and yes, *= becomes left join. Do you think the rest of the statement looks right?

Thanks.
Gabriel.

kalman

02-15-02, 10:46

Originally posted by hp9000
I looked around and yes, *= becomes left join. Do you think the rest of the statement looks right?

Thanks.
Gabriel.

Well
you should write LEFT OUTER JOIN
instead of RIGHT JOIN

Consider also that write

SELECT *
FROM tableA a LEFT OUTER JOIN tableB b,
tableA aa LEFT OUTER JOIN tableC c
WHERE a.oid = aa.oid;

sometimes is more performant that write:

SELECT *
FROM tableA a LEFT OUTER JOIN tableB b
LEFT OUTER JOIN tableC c;

Ciao
Gaetano