Home » SQL & PL/SQL » SQL & PL/SQL » Column Data Modification (Oracle Database 11g Release 2: 11.2.0.1–11.2.0.4)
Column Data Modification [message #656174] Mon, 26 September 2016 21:46 Go to next message
LRB495
Messages: 1
Registered: September 2016
Junior Member
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,Cool 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')
Re: Column Data Modification [message #656175 is a reply to message #656174] Mon, 26 September 2016 22:42 Go to previous messageGo to next message
BlackSwan
Messages: 25849
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Column Data Modification [message #656177 is a reply to message #656174] Tue, 27 September 2016 00:42 Go to previous message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

A test case do not need to be the actual problem but a simplified version of it to show us what you want.

Previous Topic: Date Time problem in sql server
Next Topic: Fixed Width Flat file via Oracle table
Goto Forum:
  


Current Time: Sun Feb 18 12:59:44 CST 2018

Total time taken to generate the page: 0.10209 seconds