CODEWITHSUNDEEP
sqloracle-databasedateplsqloracle11g
Sid
Sid

Reputation: 349

Unpivot date columns to a single column of a complex query in Oracle

Hi guys, I am stuck with a stubborn problem which I am unable to solve. Am trying to compile a report wherein all the dates coming from different tables would need to come into a single date field in the report. Ofcourse, the max or the most recent date from all these date columns needs to be added to the single date column for the report. I have multiple users of multiple branches/courses for whom the report would be generated.

There are multiple blogs and the latest date w.r.t to the blogtitle needs to be grouped, i.e. max(date_value) from the six date columns should give the greatest or latest date for that blogtitle.

Expected Result:

Resultset that is seen currently and what needs to be the desired output

select u.batch_uid as ext_person_key, u.user_id, cm.batch_uid as ext_crs_key,    cm.crs_id, ir.role_id as 
 insti_role, (CASE when  b.JOURNAL_IND = 'N' then 
'BLOG' else 'JOURNAL' end) as item_type, gm.title as item_name, gm.disp_title as    ITEM_DISP_NAME, be.blog_pk1 as be_blogPk1, bc.blog_entry_pk1 as bc_blog_entry_pk1,bc.pk1,

 b.ENTRY_mod_DATE as b_ENTRY_mod_DATE ,b.CMT_mod_DATE as BlogCmtModDate, be.CMT_mod_DATE as be_cmnt_mod_Date,
 b.UPDATE_DATE as BlogUpDate,  be.UPDATE_DATE as be_UPDATE_DATE,
  bc.creation_date as bc_creation_date, 

be.CREATOR_USER_ID as be_CREATOR_USER_ID ,  bc.creator_user_id as bc_creator_user_id,
b.TITLE as BlogTitle, be.TITLE as be_TITLE,
 be.DESCRIPTION as be_DESCRIPTION, bc.DESCRIPTION as bc_DESCRIPTION
FROM users u
 INNER JOIN insti_roles ir on u.insti_roles_pk1 = ir.pk1
 INNER JOIN crs_users cu ON  u.pk1 = cu.users_pk1
 INNER JOIN crs_mast cm on cu.crsmast_pk1 = cm.pk1 
 INNER JOIN blogs b on b.crsmast_pk1 = cm.pk1 
 INNER JOIN blog_entry be on b.pk1=be.blog_pk1 AND be.creator_user_id = cu.pk1 
 LEFT JOIN blog_CMT bc on be.pk1=bc.blog_entry_pk1 and bc.CREATOR_USER_ID=cu.pk1
JOIN gradeledger_mast gm ON gm.crsmast_pk1 = cm.pk1  and b.grade_handler = gm.linkId     
WHERE cu.ROLE='S' AND BE.STATUS='2' AND B.ALLOW_GRADING='Y' AND u.row_status='0'
 AND u.available_ind ='Y' and cm.row_status='0' and and      u.batch_uid='userA_157'

I am getting a resultset for the above query with multiple date columns which I want > > to input into a single columnn. The dates have to be the most recent, i.e. max of the dates in the date columns.

I have successfully done the Unpivot by using a view to store the above resultset and put all the dates in one column. However, I do not want to use a view or a table to store the resultset and then do Unipivot simply because I cannot keep creating views for every user one would query for.

The max(date_value) from the date columns need to be put in one single column. They are as follows:

* 1) b.entry_mod_date, 2) b.cmt_mod_date ,3) be.cmt_mod_date , 4) b.update_Date ,5) be.update_date, 6) bc.creation_date *

Apologies that I could not provide the desc of all the tables and the fields being used.

Any help to get the above mentioned max of the dates from these multiple date columns into a single column without using a view or a table would be greatly appreciated.*

Upvotes: 0

Views: 601

Answers (2)

Sid
Sid

Reputation: 349

              select <columns>, 
                case 
                    when greatest (b_ENTRY_mod_DATE) >= greatest (BlogCmtModDate) and greatest(b_ENTRY_mod_DATE) >= greatest(BlogUpDate) 
                        then greatest( b_ENTRY_mod_DATE )
                    --<same implementation to compare each time BlogCmtModDate and BlogUpDate separately to get the greatest then 'date'> 
             ,<columns>
            FROM table
             <rest of the query>

             UNION ALL 

             Select <columns>, 
                case 
                    when greatest (be_cmnt_mod_Date) >= greatest (be_UPDATE_DATE) 
                        then greatest( be_cmnt_mod_Date )
                     when greatest (be_UPDATE_DATE) >= greatest (be_cmnt_mod_Date) 
                        then greatest( be_UPDATE_DATE )

             ,<columns>
            FROM table
             <rest of the query>

             UNION ALL

             Select <columns>, 
                GREATEST(bc_creation_date)
             ,<columns>
            FROM table
             <rest of the query>

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

It is not clear what results you want, but the easiest solution is to use greatest().

with t as (
      YOURQUERYHERE
     )
select t.*,
       greatest(entry_mod_date, cmt_mod_date, cmt_mod_date, update_Date,
                update_date, bc.creation_date
               ) as greatestdate
from t;

Upvotes: 2

Related Questions