Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PIVOT

GmoneyJun 19 2015 — edited Jun 20 2015

I am trying to use a pivot to display 2 specific tasks and the date on which the tasks were either Completed or are scheduled to Complete.

Here is the same code:

DROP TABLE TASKS;

CREATE TABLE TASKS

(

DOCUMENT_NUMBER NUMBER(9),

TASK_TYPE VARCHAR2(8),

TASK_STATUS VARCHAR2(8),

ACTUAL_COMPLETION_DATE DATE,

SCHEDULED_COMPLETION_DATE DATE);

INSERT INTO TASKS VALUES (2269734, 'DD', 'Complete',TO_DATE('6/1/2015 6:21:06','MM/DD/YYYY HH:MI:SS') ,TO_DATE('6/18/2015 12:45:00','MM/DD/YYYY HH:MI:SS'));

INSERT INTO TASKS VALUES (2269734, 'CAD', 'Pending', NULL, TO_DATE('6/25/2015 12:45:00','MM/DD/YYYY HH:MI:SS'));

INSERT INTO TASKS VALUES (2269734, 'RAD','Complete',TO_DATE('6/1/2015 6:24:10','MM/DD/YYYY HH:MI:SS'),TO_DATE('6/18/2015 12:30:00 ','MM/DD/YYYY HH:MI:SS'));

Here is the query that I am using thus far:

SELECT DISTINCT MSTAT.*

FROM

(SELECT TASKS.DOCUMENT_NUMBER,

       -- TASKS.TASKS_TYPE,

       -- TASKS.TASKS_STATUS,

       TASKS.ACTUAL_COMPLETION_DATE DD_COMP_DT,

       TASKS_1.TASK_TYPE PTYP,

       TASKS_1.TASK_STATUS PSTAT

  

       ,

  CASE WHEN TASKS_1.TASK_TYPE = 'CAD'

       AND TASKS_1.TASK_STATUS IN ('Pending','Ready')

    THEN TASKS_1.SCHEDULED_COMPLETION_DATE

    WHEN TASKS_1.TASK_TYPE = 'CAD'

       AND TASKS_1.TASK_STATUS = 'Complete'

    THEN TASKS_1.ACTUAL_COMPLETION_DATE

  END as CAD_COMP_DT

    -- TASKS_1.SCHEDULED_COMPLETION_DATE

  FROM TASKS

       LEFT OUTER JOIN TASKS TASKS_1

          ON (TASKS.DOCUMENT_NUMBER = TASKS_1.DOCUMENT_NUMBER)

WHERE (TASKS.DOCUMENT_NUMBER = 2269734) AND (TASKS.TASK_TYPE = 'DD'))

PIVOT(max(PSTAT), max(DD_COMP_DT) as DD_COMP_DT, max(CAD_COMP_DT) as CAD_COMP_DT for PTYP in ('DD' DD,'CAD' CAD)) MSTAT

My results are as shown below:

       

       

DOCUMENT_NUMBER DD DD_DD_COMP_DT DD_CAD_COMP_DT CAD CAD_DD_COMP_DT CAD_CAD_COMP_DT
2269734 Complete 6/1/2015 Pending 6/1/2015 6/25/2015

My desired result is as follows:

DOCUMENT_NUMBER DD DD_DD_COMP_DT CAD CAD_CAD_COMP_DT
2269734 Complete 6/1/2015 Pending 6/25/2015

I appreciate anyone taking the time to look and suggest the correct way to implement.

Thanks

G

This post has been answered by James Su on Jun 19 2015
Jump to Answer

Comments

Mike Kutz
Answer

Oracle Multimedia ORDImage Object Type

This won't work in XE.

Otherwise, you'll need to create your own Web Service that does this for you.  (or hunt one down)

MK

Marked as Answer by Reza.Gh. · Sep 27 2020
Reza.Gh.

Hi,

Ok, thanks.

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 18 2015
Added on Jun 19 2015
7 comments
1,051 views