Archives of the TeradataForum

Message Posted: Fri, 26 Aug 2005 @ 14:44:46 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You can also eliminate derived table dt using directly the DISTINCT clause, inside dt2:

     SELECT a.deptno,
            a.job,
            a.loc,
            job1, job2, job3, job4, job5,
            job6, job7, job8, job9, job10

     FROM (SELECT deptno, max(frst), max(scnd), max(thrd), max(frth), max(ffth)
                        , max(sxth), max(svth), max(egth), max(nith), max(tnth)
           FROM
             (SEL deptno, case pos when 1 then job end, case pos when 2 then job end
                        , case pos when 3 then job end, case pos when 4 then job end
                        , case pos when 5 then job end, case pos when 6 then job end
                        , case pos when 7 then job end, case pos when 8 then job end
                        , case pos when 9 then job end, case pos when 10 then job end
              FROM (SELECT DISTINCT deptno,
                                    job,
                                    RANK() OVER (PARTITION BY deptno
                                                 ORDER BY job
                                                 ) AS pos
                    FROM emp
                    ) dt2
              ) dt3 (deptno, frst, scnd, thrd, frth, ffth, sxth, svth, egth, nith, tnth)
            )dt4 (deptno, job1, job2, job3, job4, job5, job6, job7, job8, job9,job10)
     JOIN dept a
       ON dt4.dept=a.deptno
     ;

Good idea the use of CASE in the Select.

Regards,

Sergio

	<Prev	Next>		<<First	<Prev	Next>	Last>>