Results 1 to 4 of 4

Thread: JOIN Question

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: JOIN Question

    Havig an issue with a prticualr query, and was wondering if someone could shed a little light on it. Here's the stored procedure I'm writing - nothing too elaborate:

    CREATE PROCEDURE AIM.UPDATEARCHIVERETRIEVAL (IN decArchiveRetrievalID DECIMAL(13,0),
    IN decStatusID DECIMAL(13,0)
    )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    IF (SELECT Archive FROM AIM.ArchiveType LEFT JOIN AIM.ArchiveRetrieval ON ARCHIVERETRIEVAL.ArchiveID = ArchiveType.ArchiveID and decArchiveRetrievalID = ArchiveRetrieval.ArchiveRetrievalID) = 'ViewPointe'
    THEN
    UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = NULL
    WHERE ArchiveRetrievalID = decArchiveRetrievalID;
    ELSE
    UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = CURRENT TIMESTAMP
    WHERE ArchiveRetrievalID = decArchiveRetrievalID;
    END IF;
    END P1

    When trying to build the procedure I get the error:
    AIM.UPDATEARCHIVERETRIEVAL: 10: [IBM][CLI Driver][DB2/NT] SQL0206N "ARCHIVERETRIEVAL.ARCHIVEID" is not valid in the context where it is used.

    Am I missing something really obvious here?

    Any help would be appreciated.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jan 2004
    Posts
    4

    Re: JOIN Question

    Can u check or list the columns from the tables. I think the problem is there.


    Santosh


    Originally posted by ansonee
    Havig an issue with a prticualr query, and was wondering if someone could shed a little light on it. Here's the stored procedure I'm writing - nothing too elaborate:

    CREATE PROCEDURE AIM.UPDATEARCHIVERETRIEVAL (IN decArchiveRetrievalID DECIMAL(13,0),
    IN decStatusID DECIMAL(13,0)
    )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    IF (SELECT Archive FROM AIM.ArchiveType LEFT JOIN AIM.ArchiveRetrieval ON ARCHIVERETRIEVAL.ArchiveID = ArchiveType.ArchiveID and decArchiveRetrievalID = ArchiveRetrieval.ArchiveRetrievalID) = 'ViewPointe'
    THEN
    UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = NULL
    WHERE ArchiveRetrievalID = decArchiveRetrievalID;
    ELSE
    UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = CURRENT TIMESTAMP
    WHERE ArchiveRetrievalID = decArchiveRetrievalID;
    END IF;
    END P1

    When trying to build the procedure I get the error:
    AIM.UPDATEARCHIVERETRIEVAL: 10: [IBM][CLI Driver][DB2/NT] SQL0206N "ARCHIVERETRIEVAL.ARCHIVEID" is not valid in the context where it is used.

    Am I missing something really obvious here?

    Any help would be appreciated.

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Alll the coplumns exist and the names are correct in the procedure.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: JOIN Question

    In this join criteria:
    LEFT JOIN AIM.ArchiveRetrieval ON ARCHIVERETRIEVAL.ArchiveID = ArchiveType.ArchiveID and decArchiveRetrievalID = ArchiveRetrieval.ArchiveRetrievalID

    you are specifying archiveretrieval as a table alias and it has not been defined as such. Try specifying a table alias as below:

    LEFT JOIN AIM.ArchiveRetrieval AR ON AR.ArchiveID = ArchiveType.ArchiveID and decArchiveRetrievalID = AR.ArchiveRetrievalID

    Note: you will have to define a table alias for ArchiveType. You can use the table name as the alias but it makes the SQL statement hard to read, it is good practice to use abbreviations as aliases.

    Originally posted by ansonee
    Havig an issue with a prticualr query, and was wondering if someone could shed a little light on it. Here's the stored procedure I'm writing - nothing too elaborate:

    CREATE PROCEDURE AIM.UPDATEARCHIVERETRIEVAL (IN decArchiveRetrievalID DECIMAL(13,0),
    IN decStatusID DECIMAL(13,0)
    )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    LANGUAGE SQL
    RESULT SETS 1
    P1: BEGIN
    IF (SELECT Archive FROM AIM.ArchiveType LEFT JOIN AIM.ArchiveRetrieval ON ARCHIVERETRIEVAL.ArchiveID = ArchiveType.ArchiveID and decArchiveRetrievalID = ArchiveRetrieval.ArchiveRetrievalID) = 'ViewPointe'
    THEN
    UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = NULL
    WHERE ArchiveRetrievalID = decArchiveRetrievalID;
    ELSE
    UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID, ArchiveRetrievalResponse = CURRENT TIMESTAMP
    WHERE ArchiveRetrievalID = decArchiveRetrievalID;
    END IF;
    END P1

    When trying to build the procedure I get the error:
    AIM.UPDATEARCHIVERETRIEVAL: 10: [IBM][CLI Driver][DB2/NT] SQL0206N "ARCHIVERETRIEVAL.ARCHIVEID" is not valid in the context where it is used.

    Am I missing something really obvious here?

    Any help would be appreciated.

Posting Permissions

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