Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001
    Posts
    6

    Unanswered: Sybase TSQL to PostgreSQL

    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

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I belive *= is synonymous with LEFT OUTER JOIN. I am not 100% sure, though.
    Thanks,

    Matt

  3. #3
    Join Date
    Nov 2001
    Posts
    6

    I think you're right

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

    Thanks.
    Gabriel.

  4. #4
    Join Date
    Jan 2002
    Posts
    19

    Re: I think you're right

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •