Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: SQL0206N "TBL_ROW" is not valid in the context where it is used. What is wrong?

    I am trying to concatenate all the columns of this SELECT statements into one column. Columns are coming from multiple tables. When I try to execute it, I am getting the following error:
    [IBM][CLI Driver][DB2/LINUX] SQL0206N "TBL_ROW" is not valid in the context where it is used. SQLSTATE=42703. Can someone tell what is wrong here? I am DB2 UDBv8.2 windows XP. The complete SQL is as follow:

    SELECT (CHAR(DB2ADMIN.ASSIGNMENT.CD_ASGN_ROLE) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.NM_LST) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.NM_FRST) ||
    CHAR(DB2ADMIN.ASSIGNMENT.DT_STRT) ||
    CHAR(DB2ADMIN.ASSIGNMENT.DT_END) ||
    CHAR(DB2ADMIN.ASSIGNMENT.ID_GRP_LVL2) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.ID_UNIT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.NM_UNIT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.ID_ENTITY) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.NM_ENTITY) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_ENTITY_TYPE) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_ENTITY_TYPE) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_DISTRICT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_DISTRICT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_COUNTY) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_COUNTY) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER_ROLE) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER)) AS NEWROW,
    COUNT(*) AS NumOccurrences

    FROM DB2ADMIN.CPS_CASE,
    DB2ADMIN.ASSIGNMENT,
    DB2ADMIN.CPS_ORG_WORKER_ROLE,
    DB2ADMIN.CPS_ENTITY_LOCATION
    WHERE DB2ADMIN.CPS_CASE.ID_CASE=DB2ADMIN.ASSIGNMENT.ID_G RP_LVL1
    AND DB2ADMIN.ASSIGNMENT.CD_ASGN_CTGRY=1
    AND DB2ADMIN.ASSIGNMENT.ID_PRSN=DB2ADMIN.CPS_ORG_WORKE R_ROLE.ID_WORKER_ROLE
    AND DB2ADMIN.ASSIGNMENT.ID_UNIT=DB2ADMIN.CPS_ENTITY_LO CATION.ID_UNIT
    AND DB2ADMIN.ASSIGNMENT.CD_COUNTY=DB2ADMIN.CPS_ENTITY_ LOCATION.CD_COUNTY
    GROUP BY NEWROW
    HAVING ( COUNT(*) > 1 )
    ;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by pagwu
    Can someone tell what is wrong here?
    I think what's wrong here is that the statement you've posted is not the one that gives you the error.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Something else that is wrong is that you refer to NEWROW in a GROUP BY expression. You have to use exactly the same expression as in the SELECT list and not the alias name. An alternative is to put things in a subselect and do the grouping/having in the outer-most select.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Mar 2007
    Posts
    72
    Hi n_i,

    But that is precisely what SQL produced the error, again, as follows:
    SELECT (CHAR(DB2ADMIN.ASSIGNMENT.CD_ASGN_ROLE) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.NM_LST) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.NM_FRST) ||
    CHAR(DB2ADMIN.ASSIGNMENT.DT_STRT) ||
    CHAR(DB2ADMIN.ASSIGNMENT.DT_END) ||
    CHAR(DB2ADMIN.ASSIGNMENT.ID_GRP_LVL2) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.ID_UNIT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.NM_UNIT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.ID_ENTITY) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.NM_ENTITY) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_ENTITY_TYPE) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_ENTITY_TYPE) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_DISTRICT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_DISTRICT) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_COUNTY) ||
    CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_COUNTY) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER_ROLE) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER)) AS TBL_ROW,
    COUNT(*) AS NumOccurrences

    FROM DB2ADMIN.CPS_CASE,
    DB2ADMIN.ASSIGNMENT,
    DB2ADMIN.CPS_ORG_WORKER_ROLE,
    DB2ADMIN.CPS_ENTITY_LOCATION
    WHERE DB2ADMIN.CPS_CASE.ID_CASE=DB2ADMIN.ASSIGNMENT.ID_G RP_LVL1
    AND DB2ADMIN.ASSIGNMENT.CD_ASGN_CTGRY=1
    AND DB2ADMIN.ASSIGNMENT.ID_PRSN=DB2ADMIN.CPS_ORG_WORKE R_ROLE.ID_WORKER_ROLE
    AND DB2ADMIN.ASSIGNMENT.ID_UNIT=DB2ADMIN.CPS_ENTITY_LO CATION.ID_UNIT
    AND DB2ADMIN.ASSIGNMENT.CD_COUNTY=DB2ADMIN.CPS_ENTITY_ LOCATION.CD_COUNTY
    GROUP BY TBL_ROW
    HAVING ( COUNT(*) > 1 )
    ;
    ------------------------------------------------------------------------------
    SELECT (CHAR(DB2ADMIN.ASSIGNMENT.CD_ASGN_ROLE) || CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.NM_LST) || CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.NM_FRST) || CHAR(DB2ADMIN.ASSIGNMENT.DT_STRT) || CHAR(DB2ADMIN.ASSIGNMENT.DT_END) || CHAR(DB2ADMIN.ASSIGNMENT.ID_GRP_LVL2) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.ID_UNIT) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.NM_UNIT) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.ID_ENTITY) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.NM_ENTITY) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_ENTITY_TYPE) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_ENTITY_TYPE) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_DISTRICT) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_DISTRICT) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.CD_COUNTY) || CHAR(DB2ADMIN.CPS_ENTITY_LOCATION.TX_COUNTY) || CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER_ROLE) || CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER)) AS TBL_ROW, COUNT(*) AS NumOccurrences FROM DB2ADMIN.CPS_CASE, DB2ADMIN.ASSIGNMENT, DB2ADMIN.CPS_ORG_WORKER_ROLE, DB2ADMIN.CPS_ENTITY_LOCATION WHERE DB2ADMIN.CPS_CASE.ID_CASE=DB2ADMIN.ASSIGNMENT.ID_G RP_LVL1 AND DB2ADMIN.ASSIGNMENT.CD_ASGN_CTGRY=1 AND DB2ADMIN.ASSIGNMENT.ID_PRSN=DB2ADMIN.CPS_ORG_WORKE R_ROLE.ID_WORKER_ROLE AND DB2ADMIN.ASSIGNMENT.ID_UNIT=DB2ADMIN.CPS_ENTITY_LO CATION.ID_UNIT AND DB2ADMIN.ASSIGNMENT.CD_COUNTY=DB2ADMIN.CPS_ENTITY_ LOCATION.CD_COUNTY GROUP BY TBL_ROW HAVING ( COUNT(*) > 1 )
    SQL0206N "TBL_ROW" is not valid in the context where it is used.
    SQLSTATE=42703

    SQL0206N "TBL_ROW " is not valid in the context where it is used.

    Explanation:

    This error can occur in the following cases:

    o For an INSERT or UPDATE statement, the specified column is
    not a column of the table, or view that was specified as the
    object of the insert or update.

    o For a SELECT or DELETE statement, the specified column is not
    a column of any of the tables or views identified in a FROM
    clause in the statement.

    o For an ORDER BY clause, the specified column is a correlated
    column reference in a subselect, which is not allowed.

    o For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION
    statement:

    - The reference "<name>" does not resolve to the name of a
    column, local variable or transition variable.

    - The condition name "<name>" specified in the SIGNAL statement
    has not been declared.

    o For a CREATE TRIGGER statement:

    - A reference is made to a column of the subject table without
    using an OLD or NEW correlation name.

    - The left hand side of an assignment in the SET
    transition-variable statement in the triggered action
    specifies an old transition variable where only a new
    transition variable is supported.

    o For a CREATE FUNCTION statement with a PREDICATES clause:

    - The RETURN statement of the SQL function references a
    variable that is not a parameter or other variable that
    is in the scope of the RETURN statement.

    - The FILTER USING clause references a variable that is not a
    parameter name or an expression name in the WHEN
    clause.

    - The search target in an index exploitation rule does not
    match some parameter name of the function that is being
    created.

    - A search argument in an index exploitation rule does not
    match either an expression name in the EXPRESSION AS
    clause or a parameter name of the function being
    created.

    o For a CREATE INDEX EXTENSION statement, the RANGE THROUGH
    clause or the FILTER USING clause references a variable that
    is not a parameter name that can be used in the clause.



    The statement cannot be processed.

    User Response:

    Verify that the names are specified correctly in the SQL
    statement. For a SELECT statement, ensure that all the required
    tables are named in the FROM clause. For a subselect in an ORDER
    BY clause, ensure that there are no correlated column references.
    If a correlation name is used for a table, verify that subsequent
    references use the correlation name and not the table name.

    For a CREATE TRIGGER statement, ensure that only new transition
    variables are specified on the left hand side of assignments in
    the SET transition-variable statement and that any reference to
    columns of the subject table have a correlation name specified.

    sqlcode : -206

    sqlstate : 42703

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by pagwu
    Hi n_i,

    But that is precisely what SQL produced the error, again, as follows:
    In your original statement, you were using NEWROW instead of TBL_ROW, so n_i was right - your statement did not match the error message.

    How about reading the error message and its explanation?
    o For a SELECT or DELETE statement, the specified column is not
    a column of any of the tables or views identified in a FROM
    clause in the statement.
    You don't have TBL_ROW as a column in the tables listed in the FROM clause, do you?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Mar 2007
    Posts
    72
    No, there is no TBL_ROW or NEW_ROW column in any of the tables. I have derived a new column from such a string concatenation expression before. So, what is really wrong with the way I have it? I truly will like to understand the nuances of that syntax "....AS TBL_ROW".
    Does that mean I cannot use a derived column in a group by?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by pagwu
    No, there is no TBL_ROW or NEW_ROW column in any of the tables. I have derived a new column from such a string concatenation expression before. So, what is really wrong with the way I have it? I truly will like to understand the nuances of that syntax "....AS TBL_ROW".
    Does that mean I cannot use a derived column in a group by?
    You can used a derived column, but you cannot refer to as TBL_ROW in the GROUP BY. As already mentioned above, you must use the exact syntax (with all the concats) in the GROUP BY as in the SELECT.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ...or you could do something like this:
    Code:
    select t.tbl_row, COUNT(*) AS NumOccurrences from (
    SELECT (CHAR(DB2ADMIN.ASSIGNMENT.CD_ASGN_ROLE) ||
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.NM_LST) ||
    ...
    ...
    CHAR(DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER)) AS TBL_ROW,
    
    
    FROM DB2ADMIN.CPS_CASE,
    DB2ADMIN.ASSIGNMENT,
    DB2ADMIN.CPS_ORG_WORKER_ROLE,
    DB2ADMIN.CPS_ENTITY_LOCATION
    WHERE DB2ADMIN.CPS_CASE.ID_CASE=DB2ADMIN.ASSIGNMENT.ID_G RP_LVL1
    AND DB2ADMIN.ASSIGNMENT.CD_ASGN_CTGRY=1
    AND DB2ADMIN.ASSIGNMENT.ID_PRSN=DB2ADMIN.CPS_ORG_WORKER_ROLE.ID_WORKER_ROLE
    AND DB2ADMIN.ASSIGNMENT.ID_UNIT=DB2ADMIN.CPS_ENTITY_LOCATION.ID_UNIT
    AND DB2ADMIN.ASSIGNMENT.CD_COUNTY=DB2ADMIN.CPS_ENTITY_LOCATION.CD_COUNTY) t
    GROUP BY t.TBL_ROW
    HAVING  COUNT(*) > 1
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    ... which causes the TBL_ROW column to be available in a table in the FROM clause. (Exactly what the error message was complaining about not existing before.)

    Note that a subselect just generates a new table, so a subselect in the FROM clause gives you a new temp table on which the select statement can operate on.
    Last edited by stolze; 04-21-08 at 11:47.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Mar 2007
    Posts
    72

    SQL0206N "TBL_ROW" is not valid in the context where it is used. What is wrong?

    Quote Originally Posted by stolze
    ... which causes the TBL_ROW column to be available in a table in the FROM clause. (Exactly what the error message was complaining about not existing before.)

    Note that a subselect just generates a new table, so a subselect in the FROM clause gives you a new temp table on which the select statement can operate on.
    n_i, Stolze, Marcus_A, you guys are geniuses in my book!

    Sincere thanks to all...

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Seriously, that are just basic concepts of SQL.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to see "The SQL Reference for Cross-Platform Development"
    http://www-128.ibm.com/developerwork...206sqlref.html
    -> Chapter 4. Queries -> subselect
    You can see following description.....
    The logical sequence of the operations is:
    1. FROM clause
    2. WHERE clause
    3. GROUP BY clause
    4. HAVING clause
    5. SELECT clause.
    So, you can't refer a derived column in SELECT clause(sequence 5) in GROUP BY clause(sequence 3).

Posting Permissions

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