Hi Team,
There is a one requirement in my report,
In my report one of the column C04 COLUMN data is dynamically coming as
AS,NZ,AU,IN
IN,JP
KP,MN,PK,BZ
US,UK,MN
I need those abrivations in my report.
Those Codes and abrivations are stored in one table
I am attaching query below.
SELECT P.Username,P.FNAME,P.LNAME,P.EMAIL, ROW_NUMBER() over(PARTITION BY P.USERNAME,TEMP.TITLE,SELF.PART_NO ,P.FNAME,P.LNAME,P.EMAIL,temp.offering_template_no ORDER BY SE.ATTEMPT_NUMBER DESC ) H, Temp.Title AS "Offering name", SELF.PART_NO AS "Offering ID", temp.offering_template_no "Course ID", SE.ATTEMPT_NUMBER AS "Attempt Number", --TO_CHAR (SE.ATTEMPT_ON, 'dd/mm/yyyy hh24:mi:ss') AS "Attempt On", A.COMPLETION_DATE, OS.STATUS_DESC AS "Status", --SD.SUSPEND_DATA1 --SD.C01, -- SD.C02, SD.C03 AS DIVISION, SD.C04 AS Acess_Countries, --SD.C05, DECODE(SD.C06, 1 , 'Completed', 0 , 'In Complete') SOP, SD.C07 as SOP_COMPLETED_ON, --SD.C08 , DECODE(SD.C09, 1 , 'Completed', 0 , 'In Complete') CHA, SD.C10 AS CHA_Completed_on, --SD.C11, DECODE(SD.C12, 1 , 'Completed', 0 , 'In Complete') MPO, SD.C13 AS MPO_Complete_On, --sd.C14, DECODE(SD.C15, 1 , 'Completed', 0 , 'In Complete') SQA, sd.C16 AS SQA_Completed_ON, --sd.C17, DECODE(SD.C18, 1 , 'Completed', 0 , 'In Complete') DSR, sd.C19 AS DSR_Completed_On, --sd.C21, DECODE(SD.C21, 1 , 'Completed', 0 , 'In Complete') SDV, sd.C22 AS SDV_Completed_On, SD.C23 AS Last_Completion_Date FROM tp2.tpv_pub_employees p, tp2.tpv_pub_reg tr, tp2.tpt_offering_action a, tp2.tpv_pub_lov_off_action_status os, tp2.cnt_content_context con, tp2.cnt_cmi_registration cr, tp2.cnt_cmi_session se, tp2.let_ext_offering_selfpaced self, TP2.CNT_CONTENT_SUBSCRIPTION SUB, TP2.CNT_EXT_CONTENT_INVENTORY INV, TP2.FGT_DOMAIN DOM, TP2.FGT_DOMAIN DOMN, TP2.LET_EXT_OFFERING_TEMPLATE TEMP, --TP2.CNT_SUSPEND_DATA sd (SELECT AICC_REG_ID, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,1) C01, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,2) C02, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,3) C03, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,4) C04, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,5) C05, REGEXP_SUBSTR(replace(SUSPEND_DATA1,':','|'),'[^|]+',1,6) C06, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,1) C07, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,2) C08, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1, C09, --REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,9) C09, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,1) C10, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,9),'[^,]+',1,2) C11, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,10) C12, --REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,11) C11, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,1) C13, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,11),'[^,]+',1,2) C14, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,12) C15, --REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,13) C16, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,13),'[^,]+',1,1) C16, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,13),'[^,]+',1,2) C17, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,14) C18, --REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,15) C18, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,15),'[^,]+',1,1) C19, REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,15),'[^,]+',1,2) C20, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,16) C21, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,17) C22, REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,18) C23 from TP2.CNT_SUSPEND_DATA) SD WHERE TR.STUDENT_ID = P.ID and SD.AICC_REG_ID=SE.AICC_REG_ID AND p.domain_id = domn.id AND inv.id = sub.content_inventory AND sub.subscriber_id = self.id AND tr.id = con.source AND DOM.ID = INV.SPLIT --and cr.id not in (select aicc_reg_id from cnt_suspend_Data) AND con.id = cr.context_id AND cr.id = se.aicc_reg_id AND cr.subscription_id = sub.id AND self.offering_temp_id = temp.id AND tr.offering_id = self.id AND tr.offering_action_id = a.ID AND a.status = os.code AND tr.student_id = a.party_id AND self.locale_id = 'local000000000000001' AND temp.locale_id = 'local000000000000001' AND inv.LOCALE_ID = 'local000000000000001' and temp.offering_template_no='00060827')
|
] 
Blog
Search
Help
Members
Register
Login
Home
