Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    38

    Post Unanswered: Using multiple Outer join for making an insert statement

    Hi All,

    I have following insert statement in Oracle :

    insert into temp_entity select umlattribute.name, umlattribute.ATTRIBUTEID from umlattribute, umlclass, umlstereotype
    where umlattribute.parententityid = umlclass.classid
    and umlattribute.modelid = umlclass.modelid
    and umlclass.stereotypeid = umlstereotype.stereotypeid(+)
    and umlclass.modelid = umlstereotype.MODELID(+)
    and (umlstereotype.name <> 'Interface' or
    umlstereotype.name is null)
    and umlattribute.modelid = Intmodelid;
    This works fine in oracle.
    Now I have to transform the same query in DB2. I tried it like this :

    insert into temp_entity select umlattribute.name, umlattribute.ATTRIBUTEID from umlattribute, umlclass LEFT OUTER JOIN umlstereotype ON umlclass.stereotypeid=umlstereotype.stereotypeid ,umlclass LEFT OUTER JOIN umlstereotype ON umlclass.modelid=umlstereotype.modelid
    where umlattribute.parententityid = umlclass.classid
    and umlattribute.modelid = umlclass.modelid
    and (umlstereotype.name <> 'Interface' or umlstereotype.name is null)
    and umlattribute.modelid = Intmodelid;

    It gave following error:

    ADMINISTRATOR.SP_ACTIVITYLOG: 94: [IBM][CLI Driver][DB2/NT] SQL0203N A reference to column "UMLCLASS.MODELID" is ambiguous. LINE NUMBER=94. SQLSTATE=42702


    Secondly I am not sure about insert statement would work fine. Please guide me.
    Thanks.
    Last edited by Raj Shekhar; 06-18-03 at 08:27.
    Raj Shekhar

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Just mention the outer join predicate only once, like:


    insert into temp_entity select umlattribute.name, umlattribute.ATTRIBUTEID from umlattribute, umlclass LEFT OUTER JOIN umlstereotype ON (umlclass.stereotypeid=umlstereotype.stereotypeid and umlclass.modelid=umlstereotype.modelid)
    where umlattribute.parententityid = umlclass.classid
    and umlattribute.modelid = umlclass.modelid
    and (umlstereotype.name <> 'Interface' or umlstereotype.name is null)
    and umlattribute.modelid = Intmodelid;

    This will avoid specifying ambiguous combinations of joins if you join over more than one field (incoherency)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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