Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: conversion from oracle to DB2 syntax

    Hi,

    I have the following SQL written in Oracle :

    SELECT DISTINCT A.* FROM MyTABLEA A , MyTABLEB B
    WHERE A.ID = '77787'
    AND A.ID =B.ID(+)
    AND A.MAIN = B.MAIN(+)
    AND A.NO = B.NO(+)
    AND A.EQP = B.EQP(+)
    AND A.JOB_ID = B.JOB_ID(+)

    my purpose is to get all record fro ID =7787 from MYTABLEA even if no matching record in MYTABLEB

    i tried

    SELECT DISTINCT A.* FROM MyTABLEA A LEFT OUTER JOIN MyTABLEB B
    ON A.ID = '77787'
    AND A.ID =B.ID
    AND A.MAIN = B.MAIN
    AND A.NO = B.NO
    AND A.EQP = B.EQP
    AND A.JOB_ID = B.JOB_ID

    but it seems to return ALL records from MyTABLEA and not just limited to the specified records from MYTABLEA

    please advise

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to find a SQL manual on how to do an outer join. You must separate the join predicates (ON) from the predicates you want to filter the rows (WHERE) in the first table.

    So it looks to me like A.ID = '77787' should be in a WHERE clause of the outer join, and not used in the ON clause.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    As Marcus says above you are not limiting what you want from the A table. You are only limiting when you perform the outer join to the B table, so that the only time you will see data that exists in the B table is when the A.ID is '77787'.

    Dave

Posting Permissions

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