PL/SQL :: How To Get Substring From A Column With Many Carriage Returns
Sep 21, 2012
I've got this situation.
In a not normalized table loaded with data from an excel file, I have a column containing a long string with many carriage returns so that after each of them it begins a new string I have to manipulate. I mean, in the column PERSON I've got something like this:
Of course I have to write a Pl/Sql procedure extracting each string and I know that each string ends with a carriage return chr(13)||chr(10)
I think I should use the INSTR built-in function but I it'd be useful whether some of you had something ready.
I need to substr the transaction number which starts with PIT to susbst (trxno,1,12) .. and anything other than PIT i need full number without substr . But when i use the above code :" susbstr(trxno,1,12) ..": it will substr entire column,. Is there any way to substr only PIT and leave others,
I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.
Table B is a reference table. It holds the 4 character code and the description.
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.
I have a reference number of application number which is sent to 3 different address as outward number . for example application number is intimated to the office , the employee and the treasury. this number is generated as follows
P/11/10/133 P/11/10/133A P/11/10/133B
in my outward report when i run it all three numbers are getting listed.
i want only P/11/10/133 to be listed and ignore the rest of the two. i tried to use SUBSTR(INOUT_NO,9), but it reads only from the left gives me only the number ie 133, 133A and 133B. i tried rtrim and ltrim but it only clears the extra space. how do i ignore the other two dispatch numbers.
How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE ( trim the values before ^ ) 121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.
I have to update a table by getting values from two other tables. While doing that the inner query returns more than one value. I am not sure how to implement the logic without returning more than one row in sub query.
My query: update buf_office_str o set o.manager_ident = (select sp.ident from se2_r_src_sourceperson sp , (select distinct director_name, team_name from buf_sales_dump )t where SP.SRCNAME = upper(substr(t.director_name,instr(t.director_name,' ')+1,length(t.director_name))||', '||substr(t.director_name,1,instr(t.director_name,' ')-1 ) ) and o.office_descr = t.team_name )
Basically the query gets the manager id from sp table where sp.srcname = t.team-name. The office_descr should be equal to the team_name.
This is the logic I am working towards:
For each office, i get the office_descr and get corresponding team_name. Match the team's director_name (from table t) with the sp.name and return the employee's id (sp.ident) for that office_descr. I need to update all 50 offices with corresponding managerid for that office in buf_office_str table.
Is it possible to get done in one update? Or, does this need a plsql proc to do this logic.
I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.
select b.value , b.name, p.value ...... (case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c where c.record_id = b.c_bpartner_id and c.ad_table_id = 291 and c.ad_column_id = 4216 ) end) as oldtradeName from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us where a.c_bpartner_id = b.c_bpartner_id and a.zz_receipt_id = r.zz_receipt_id and us.ad_user_id = r.createdby and p.m_product_id = a.m_product_id
I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.
UPDATE ps_ntsr_gf_stufile a SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR') FROM PS_CLASS_TBL b WHERE a.CLASS_NBR = b.CLASS_NBR AND a.STRM = '1118' AND a.INSTITUTION = 'NT752') WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL);
/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/
when i am trying to run this query i am getting an error'single row subquery returns more than one row'.
What i'm trying to do is count the number of rows those sub-queries return.
As i understand it, that error happens when more than 1 row is returned.
Here is my query:
SQL> SELECT 2 a.ssg_nme, 3 b.sftw_prod_nme, 4 COUNT((SELECT db_srvr_id FROM inv.db_srvr_t WHERE node_id = (SELECT node_id FROM inv.node_t WH ERE node_loc_cde IN (5, 7, 8)))), 5 COUNT((SELECT db_srvr_id from inv.db_srvr_t WHERE row_status_cde = 2)), 6 COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207)) 7 FROM 8 inv.cde_ssg_t a, inv.sftw_prod_t b, inv.db_srvr_t c, inv.appl_t d, inv.db_t e 9 WHERE d.appl_id = e.appl_id 10 AND e.db_srvr_id = c.db_srvr_id 11 AND d.appl_ssg_cde IN (6, 9) 12 GROUP BY b.sftw_prod_nme, a.ssg_nme; COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207)) * ERROR at line 6: ORA-01427: single-row subquery returns more than one row
F1 F2 F3 ---------- ---------- ---------- 1200015 0 1200.015
In above result F3 represent the actual result, which is nearest value where mod returns the 0, but i want nearest integer value which is 1206. how it is possible. In above case consider 1200 as Kgs and 45 as Grams.
If it returns a value, I want to print the value returned; If it returns no value, I would like to return a string like 'NO_DATA'.
I have already tried several things, and the last one is: ================================================================== Select NVL2(dnh.DiaNoHableData, dnh.DiaNoHableData, 'NO_DATA') From (Select Dia_No_Habil || '#' || Trim(Descripcion) DiaNoHableData From Dias_no_Habiles Where Dia_No_Habil = To_Char(To_Date('20080704', 'YYYYMMDD'), 'YYYYMMDD')) dnh; ==================================================================
The subquery returns no data, so the first query should print 'NO_DATA', but it returns nothing.
I have an existing database with several hundred tables and triggers on the tables in the schema S1. Most of the triggers are for storing audit information USER and SYSDATE in the respective columns.
We have built procedures what will accept incoming XML parameters from a BPMS application on the intranet which will have information about which table to update, which columns to update, what values to update and what the O/S user id is. The O/S user ids are mapped to oracle user ids in a specific table. Therefore when we receive the request we can easily figure out the oracle user id. The application always connects to the database using a specified user id S2.
The trouble is, the existing tables have triggers which read :NEW.userid := USER;and whenever a DML is fired from the procedure, the function USER always returns S2, since that is the user id the BPMS application connects to and therefore the audit columns do not capture the correct information as required.
The impact of changing all the existing triggers/procedures will be a mammoth task including regression testing and will certainly not be approved by the project sponsor.
override the value that USER returns? Using session contexts, etc? If so, we can possibly set it at the point of connect and have to do nothing else.
when i executr the query it gives a error message "ORA-01427: single-row subquery returns more than one row"
select distinct a.CUSTOMER_NAME, a.CUSTOMER_NUMBER,b.BILL_TO_CUSTOMER_ID,b.TRX_NUMBER,b.TRX_DATE,b.STATUS_TRX,c.ATTRIBUTE1 product,C.PAY_FROM_CUSTOMER, (SELECT(SUM(E.AMOUNT))FROM RA_CUST_TRX_LINE_GL_DIST_ALL E WHERE 1=1 GROUP BY E.CUSTOMER_TRX_ID) from ra_customers a, ra_customer_trx_all b, ar_cash_receipts_all c, ar_payment_schedules_all d, ra_cust_trx_line_gl_dist_all E where a.CUSTOMER_ID=b.CUSTOMER_TRX_ID and a.customer_id=c.PAY_FROM_CUSTOMER and c.CASH_RECEIPT_ID=d.CASH_RECEIPT_ID AND B.CUSTOMER_TRX_ID=E.CUSTOMER_TRX_ID;