RE: Converting Each Column to a Row

RE: Converting Each Column to a Row

 

  

Hey Rai Amit,
Below is the output from the last code you sent me.

SELECT empno, case when (t1.Q_1 is null or t1.Q_1 = '') then '' else
t.Q_1 concat ', ' end concat case when (t1.Q_2 is null or t1.Q_2 = '')
then '' else t.Q_2 concat ', ' end concat case when (t1.Q_3 is null or
t1.Q_3 = '') then '' else t.Q_3 concat ', ' end concat case when (t1.Q_4
is null or t1.Q_4 = '') then '' else t.Q_4 end FROM (SELECT empno,
MAX(CASE WHEN rn = 1 THEN cast(empno as char(6)) END) as Q_1, MAX(CASE
WHEN rn = 2 THEN lastname END) as Q_2, MAX(CASE WHEN rn = 3 THEN job
END) as Q_3, MAX(CASE WHEN rn = 4 THEN cast(salary as char(9)) END) as
Q_4 FROM (select e.empno, e.lastname, e.job, e.salary, e.workdept,
row_number() over(partition by e.empno order by e.empno) rn from
employee e ) t GROUP BY t.empno)t1
SQL0206N "T.Q_1" is not valid in the context where it is used.
SQLSTATE=42703

SQL0206N "T.Q_1
" 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


-----Original Message-----
From: Rai Amit
[mailto:db2udbdba-ezmlmshield-x25066616.[Email address protected]
Sent: Wednesday, November 14, 2007 3:11 PM
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row

A liitle bit more code will give you one column...

SELECT emp_no,
case when (t1.Q_1 is null or t1.Q_1 = '') then '' else t.Q_1 concat ', '
end concat
case when (t1.Q_2 is null or t1.Q_2 = '') then '' else t.Q_2 concat ', '
end concat
case when (t1.Q_3 is null or t1.Q_3 = '') then '' else t.Q_3 concat ', '
end concat
case when (t1.Q_4 is null or t1.Q_4 = '') then '' else t.Q_4 end
FROM
(SELECT emp_no,
MAX(CASE WHEN rn = 1 THEN cast(empno as char(6)) END) as Q_1,
MAX(CASE WHEN rn = 2 THEN lastname END) as Q_2,
MAX(CASE WHEN rn = 3 THEN job END) as Q_3,
MAX(CASE WHEN rn = 4 THEN cast(salary as char(9)) END) as Q_4
FROM
(select e.empno, e.lastname, e.job, e.salary, e.workdept,
row_number() over(partition by e.empno order by e.empno) rn

from employee e ) t
GROUP BY t.empno)t1;


________________________________

From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x1969442.[Email address
protected]
Sent: Wed 11/14/2007 2:39 PM
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row



Hey Rai,
Your output creates 5 columns. I only want one column as output.


-----Original Message-----
From: Rai Amit
[mailto:db2udbdba-ezmlmshield-x86007390.[Email address protected]
Sent: Wednesday, November 14, 2007 10:49 AM
To: LazyDBA Discussion
Subject: RE: Converting Each Column to a Row

Something like this .......

SELECT emp_no,
MAX(CASE WHEN rn = 1 THEN cast(empno as char(6)) END),
MAX(CASE WHEN rn = 2 THEN lastname END) ,
MAX(CASE WHEN rn = 3 THEN job END) ,
MAX(CASE WHEN rn = 4 THEN cast(salary as char(9)) END)
FROM
(select e.empno, e.lastname, e.job, e.salary, e.workdept,
row_number() over(partition by e.empno order by e.empno) rn

from employee e ) t
GROUP BY t.empno;

________________________________

From: Edwards Ed [mailto:db2udbdba-ezmlmshield-x32874772.[Email address
protected]
Sent: Wed 11/14/2007 10:02 AM
To: LazyDBA Discussion
Subject: Converting Each Column to a Row



To All,
Using the code below, how can I eliminate the blank rows in the
output?

select case rn

when 1 then cast(empno as char(6))

when 2 then lastname

when 3 then job

when 4 then cast(salary as char(9))

when 5 then workdept

end emps

from (select e.empno, e.lastname, e.job, e.salary, e.workdept,
row_number() over(partition by e.empno order by e.empno) rn

from employee e,

(select empno, lastname, job, salary, workdept

from employee where job = 'CLERK') x

where e.workdept = 'A00') y;


Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page