Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: LEFT JOIN In ORACLE

    Hi,

    I am quite new to ORACLE and after some years of work with MS SQL I am trying to write some queries for an ORACLE server.

    My queries are for the version 8.1.7.

    I took the example of one of my old SQL selects:
    The SQL Server version

    SELECT A.tsUpdate, A.id, A.Code, AD.Name, AD.Description
    FROM Application A left outer join ApplicationDescription AD
    on A.id = AD.idApplication
    WHERE A.Code = @pCode AND
    ISNULL(AD.Name,'') = ISNULL(ISNULL(@pName, AD.Name),'') AND
    ISNULL(AD.Description,'') = ISNULL(ISNULL(@pDescription, AD.Description), '')

    This select returned all the lignes from the A table and the corresponding lines(if they existed form the AD table). Besides, as it was a code that was running in a search st proc if I put something in the @pName or @pDescription parameters the code was returning only the lines that had the text in AD.Code or AD.Description but if I put nothing in thses params the code made only thge left join.

    Now the ORACLE code:
    SELECT A.tsUpdate, A.id, A.Code, AD.Name, AD.Description
    FROM Application A, ApplicationDescription AD
    WHERE A.id = AD.idApplication(+)
    AND Code = NVL(pCode, Code)
    AND NVL(Name,'') = NVL(pName,'')
    AND Description = NVL(pDescription, Description)

    It seems that running this code returns the same result as the inner join as I get no lines where there is no corresponding line in the AD table.

    Could someone please tell me what am I doing wrong.

    Also is there a correspondent to the SQL Server Books Online. I looked in the program folders of ORACLE and I do not seem to have any documentation installed on the PC.
    Let's say I want the complete structure of a SELECT or UPDATE with all the possibilities that exists for these commands. Where could I find them?

    Thanks,
    Florian

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Your problem is these two lines

    AND NVL(Name,'') = NVL(pName,'')
    AND Description = NVL(pDescription, Description)

    1. '' = null in oracle AND you cant use equality to test if something is null you have to use 'is null'. Replace '' with a string you know wont appear.

    2. You invalidate the outer join by saying AD.description = something as null!=null in oracle (null = unknown in oracle).

    SELECT A.tsUpdate, A.id, A.Code, AD.Name, AD.Description
    FROM Application A,
    (select idApplication, name, description
    from ApplicationDescription where name=NVL(pName,name)
    and description = NVL(pDescription,description)
    ) AD
    WHERE A.id = AD.idApplication(+)
    AND Code = NVL(pCode, Code)

    I think this will work but I havent tested it : )

    Alan

Posting Permissions

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