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 )
;

I think what's wrong here is that the statement you've posted is not the one that gives you the error.

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.

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

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?
You don't have TBL_ROW as a column in the tables listed in the FROM clause, do you?

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.

...or you could do something like this:

... 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...

Seriously, that are just basic concepts of SQL.

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.....
So, you can't refer a derived column in SELECT clause(sequence 5) in GROUP BY clause(sequence 3).