Topic
1 reply Latest Post - ‏2013-01-17T15:53:49Z by nivanov1
SystemAdmin
SystemAdmin
5837 Posts
ACCEPTED ANSWER

Pinned topic Some of the row data need to be displayed as column data ...

‏2013-01-17T12:22:25Z |
Some of the row data need to be displayed as column data ...

The query 1:
WITH Types AS
(
SELECT ticketid,ownergroup,ROWNUMBER() OVER(order by ticketid,ownergroup) AS rn
FROM (SELECT distinct ticketid,ownergroup FROM tkownerhistory order by ticketid,ownergroup ) as R
)
SELECT a.ticketid
, MIN(CASE WHEN mod(rn,5) = 1 THEN a.ownergroup END) A1st_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 2 THEN a.ownergroup END) A2nd_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 3 THEN a.ownergroup END) A3rd_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 4 THEN a.ownergroup END) A4th_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 5 THEN a.ownergroup END) A5th_Assigned_Group
FROM tkownerhistory a, Types t
WHERE a.ticketid=t.ticketid and a.ownergroup=t.ownergroup
GROUP BY a.ticketid
is giving correct result...

Query (2) ...
select Ticket.TicketID as Request_ID,
Ticket.Status as STATUS,
Ticket.ITDCHCreatedByGroup as "Created By Group",
Ticket.CREATEDBY as Created_By,
Ticket.CreationDate as Create_Date,
ticket.ACTUALFINISH as Date_Resolved,
ticket.ownergroup as Asgn_Group,
ticket.AFFECTEDPERSON as "Person ID",
ticket.EXTERNALSYSTEM as TouchPoint,
ticket.class,
classification.description PCTIEEvent,
ticket.EXTERNALSYSTEM_TICKETID ReferenceNumber
from Ticket join classification on Ticket.classificationid=classification.classificationid
is also giving correct result ...

But while I am trying to merging these two i.e.

Query 3:

select Ticket.TicketID as Request_ID,
Ticket.Status as STATUS,
Ticket.ITDCHCreatedByGroup as "Created By Group",
Ticket.CREATEDBY as Created_By,
Ticket.CreationDate as Create_Date,
ticket.ACTUALFINISH as Date_Resolved,
ticket.ownergroup as Asgn_Group,
ticket.AFFECTEDPERSON as "Person ID",
ticket.EXTERNALSYSTEM as TouchPoint,
ticket.class,
classification.description PCTIEEvent,
ticket.EXTERNALSYSTEM_TICKETID ReferenceNumber,
IDENTS.ownergroup A1st_Assigned_Group,
IDENTS.ownergroup A2nd_Assigned_Group,
IDENTS.ownergroup A3rd_Assigned_Group,
IDENTS.ownergroup A4th_Assigned_Group,
IDENTS.ownergroup A5th_Assigned_Group
from Ticket join classification on Ticket.classificationid=classification.classificationid
LEFT OUTER JOIN
(
WITH Types AS
(
SELECT ticketid,ownergroup,ROWNUMBER() OVER(order by ticketid,ownergroup) AS rn
FROM (SELECT distinct ticketid,ownergroup FROM tkownerhistory order by ticketid,ownergroup ) as R
)
SELECT a.ticketid
, MIN(CASE WHEN mod(rn,5) = 1 THEN a.ownergroup END) A1st_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 2 THEN a.ownergroup END) A2nd_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 3 THEN a.ownergroup END) A3rd_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 4 THEN a.ownergroup END) A4th_Assigned_Group
, MIN(CASE WHEN mod(rn,5) = 5 THEN a.ownergroup END) A5th_Assigned_Group
FROM tkownerhistory a, Types t
WHERE a.ticketid=t.ticketid and a.ownergroup=t.ownergroup
GROUP BY a.ticketid
) as indents ON Ticket.ticketid=indents.ticketid

I am experiencing the following error:

Statusbar: 01/17/2013 5:46:29 PM: DB2 Database Error: ERROR 42601 IBMDB2/LINUXX8664 SQL0104N An unexpected token "AS" was found following "WITH Types". Expected tokens may include: "JOIN". SQLSTATE=42601

Wpuld u please help me to understand the issue in 3rd query?
Updated on 2013-01-17T15:53:49Z at 2013-01-17T15:53:49Z by nivanov1
  • nivanov1
    nivanov1
    53 Posts
    ACCEPTED ANSWER

    Re: Some of the row data need to be displayed as column data ...

    ‏2013-01-17T15:53:49Z  in response to SystemAdmin
    The common table expression (WITH Types AS ...) is misplaced. It must precede all of the SELECT clauses.