I need to extract the values and store it in seperate column. write the function to extract the data from column.I am having column DOC_NO from which i have to extract dob, gender, nationality Doc_no:
9005070288080 DOB : 900507- yyddmm Gender : 0288- if less than 5000 then female else male Nationality: 080- 0 means India 1 means foreigner.
extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.
WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =
[code]...
I need to extract values from string to variables as below.
declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or varchar2(100);
l_s varchar2(100);
l_ot varchar2(100);
l_pre varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/
But here challenge is order of alignment change dynamically. ex as below.
str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';
So how to extract Acknowledgement to l_a,Order requester to l_or ...
I need write a query based on a bunch of user supplied IDs. The IDs will be pasted as plain text, one per row, by end user in a memo field in the reporting environment, and I need to do something like this:
SELECT PHONE_NUMBER FROM TELEPHONE
WHERE
ID IN('MEMO_ID1', 'MEMO_ID2',.....)
Reporting environment does not provide any tools to automatically convert plain text into IDs.
I'm stuck on 1 scenario
I have the following table:
Create tabledrop table age_rate;
CREATE TABLE age_rate(age_0_4 NUMBER(4),age_5_20 NUMBER(4),age_21_34 NUMBER(4),age_35_44 NUMBER(4));
-------------------------------Insertion
INSERT INTO age_rateSELECT 45, 50, 60, 90
FROM dual UNION ALLSELECT 45, 50, 60, 88
FROM dual UNION ALLSELECT 40, 50, 60, 90 FROM dual UNION ALLSELECT 5, 50, 60, 88
FROM dual ;
-------------------------------Query on table
SELECT * FROM age_rate; Query Output age_0_4 age_5_20 age_21_34 age_35_44 45 50 60 9045 50 60 8840 50 60 905 50 60 88 Required outputRate Min_age Max_age
----The below rate is for age band 0_445 0 445 0 440 0 45 0 4
--The below rate is for age band 5_2050 5 2050 5 2050 5 2050 5 20
--The below rate is for age band 21_3460 21 3460 21 3460 21 3460 21 34
--The below rate is for age band 35_4490 35 4488 35 4490 35 4488 35 44
Rules--I have all data in rows so each column in row create separate rows and add 2 columns automatically Min_age and Max_age and insert value on these column on basis of column name for example if column name like age_0_4 then put 0 in min_age and put 4 in max_age means values for Min_age and Max_age extract from the basis of column name. I don't know if it is possible or not
In a table, I have a column in a table with the following values:
targetcol
30 WD
180 WD
70%
0
80%
180 CD
70%
0
7 WD
2 WD
0
-
-
-
-
-
-
-
-
-
where WD is working days and CD Calendar days.
I just need to extract number from above.
how to extract DATE from timestamp column
View 2 Replies View RelatedMy scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies View RelatedOn my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
View 3 Replies View RelatedCREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
I had a table( T1). Five columns exists in the table. The table data is like below
C1C2C3 C4C5
--- ----- ---- ----- ----
JAN FEB MAR APR MAY
One Two Three Four Five
I need the output like below one
JANFEBMARAPRMAY
---- ----- ----- ---- ----
OneTwoThreeFourFive
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
I have table output as shown below.
AID UCD U_TXT UDATE PID
116 1 Req Documents 01-OCT-2011 100
116 2 AGG APPR 01-OCT-2011 101
116 3 Docs received 02-oct-2011 102
116 1 Tmp received 02-oct-2011 103
117 2 Notice sent 03-oct-2011 104
UCD - We have total 19 codes (1 to 19), each can have multiple rows for one AID.. like 1 repeated twice for AID 116.
PID - Primary id (Primary key column)
Output I am looking
--------------------
AID COL1 COL1_TXT COL1_DATE COL2 COL2_TXT COL2_DATE..ETC
116 1 'Tmp received' 02-oct-2011 2 AGG APPR 01-OCT-2011
117 2 Notice sent 03-oct-2011
If the same UCD repeated multiple times then we should get the max(PID) record for that UCD and for that AID
I tried with group by AID,PID. but couldn't bring the rows to columns. I have attached the script with the post
i've follow problem:
select x1
from table
and i get
a
b
c
What i want is
a,b,c
The main problem here is, that it can be
a
b
as such as
a
b
c
d
e
f
in detail, i don't know prior how many i will get. With declare a cursor is it easy, but i try to find one solution only with sql.
I'm having a table which has a column which has values given inside square brackets.
[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]
Now, the requirement is to get the values which are inside the innermost square brackets.Such as in
First case : 123412
Second one: werer34
Third : qw2_w3rt
Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?
i'm trying to put more than one value in one column (by putting value '26','63' in column), so i have next problem:
1. Query select cr_pjid from acc_users where username='ACCBTPS121' give result
CR_PJID
---------
'26','63'
2. Query select * from acc_accbtp_nova_view where ed_id=2 and to_char(pj_id) in ('26','63'); return 186 rows.
3. Query: select * from acc_accbtp_nova_view where ed_id=2 and
to_char(pj_id) in (select cr_pjid
from acc_users
where username='ACCBTPS121'); doesn't return any row...
updating the column values.
Test data:
CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER) TABLESPACE ECLP_DATA01
INSERT INTO test_att VALUES(90270091,NULL);
INSERT INTO test_att VALUES(90270091,NULL);
[Code].....
I need to update "nr_id" column of test_at table with the values from test_d table. They can be udpated in any order.
I tried to update using rownum but it failed with "single row subquery returns more than 1 row...."
Most of the code is working properly except when it come to a person with more than one email type in the table:
Below is the
select emal_pidm, emal_email_address, emal_emal_code,
case
when emal_emal_code = 'PER'
and lag(emal_emal_code,1,'?') over (partition by emal_pidm order by emal_email_code) = 'EMPL'
then emal_email_address
[code]......
Below is Sample data for testing
INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emmaus.ferdinand@xxxx.edu','EMPL');
INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emfer1@xxxx.edu','PER');
[code].......
The attachment is what the output should look like.
How to split the values from a column?
For example: i had table T1 with below structue
Table T1
userid
-------
sandy1234
raj6785
Andrew12367
Michael56098
i need output like below structure
Nameid
---- ----
Sandy1234
Raj6785
Andrew12367
Michael56098
Here is my requirement..... I am pretty new to PL/SQL.
This is the procedure
CREATE OR REPLACE PROCEDURE ABCPROC.SP_ABC
(
XML IN CLOB,
P_refcursor OUT SYS_REFCURSOR
)
AS
BEGIN
[code]........
For each account number (I_AC), we have multiple rows in ABC_SVC table. I want to return these values in the refcursor. The issue with above SQL program, the row is returning only when the account(I_AC) have values for all the products 100,101,102. If the row does not exist for one account, then the account row is returned with other products.
I want my output should be in the below format
ACCOUNT_NUMBER COMMISSION CONSUL CONTRA
1YYN
2NN
3N
I want to search a some values in oracle table and then return the corresponding column names.
View 1 Replies View RelatedMy task is I want to retrieve all the Insert Statements of the rows that are present in each table of a particular Schema.
When I give input as Schema name(Scott) in a Procedure, Then the procedure retrieves all the Insert statements of rows of all tables that are present in scott user.
For this I started like this..
CREATE OR REPLACE PROCEDURE MYPROC(UID DBA_OBJECTS.OWNER%TYPE)
AS
CURSOR MYtab
IS
select o.owner, o.object_name
from dba_objects o where O.OWNER=UID
[code]....
When I execute this procedure I got the result as follows.
EXEC MYPROC('SCOTT');
insert into DEPT(DEPTNO,DNAME,LOC) values (
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (
insert into BONUS(ENAME,JOB,SAL,COMM) values (
insert into SALGRADE(GRADE,LOSAL,HISAL) values (
insert into PROJECT(PNO,PNAME,PMGR,PSTART,PEND,BUDGET) values (
[code]....
As you see that I have executed successfully till each table columns.But How to retrieve the values in this?
I have a table with two columns, like:
123 xxx
456 xxx
789 yyy
987 yyy
And in the output I would like to have:
123 xxx
789 yyy
I tried with distinct and unique on the second column, but it doesn't work.
I am trying to get only those values which are not present in the column. I have tried but i think that i am wrong in logic.
SELECT dummy
FROM DUAL
WHERE dummy IN
('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12',
'13')
AND NOT EXISTS (
SELECT pay_list_serial
FROM per_requests_master
WHERE request_status IN ('4', '5')
AND pay_list_no = :list_no)
how to find the product of the values in the column.. assume that the column has number(2) as its data type..
as we use sum(colname) for the sum of the values in the column there s no function to find the product.
I have two string one is comma separated values as a column name and another string which is having the comma separated value corresponding to the column name.
Example:
string1:='col1,col2,col3'
string2:='1,aaple,'2-jul-75'
now i have to create and run a query which will be like that
select * from emp where col1=1 and col2='apple' and col3='2-jul-75'
I have a requirement in which the amount need to be split in to multiple period.
For example if there amount of 3000 and start and end date is 01-jan-2013 and 31-mar-2013 then the output of the query should be
Name Start Period End Period Jan-13 Feb-13 Mar-13
------------------------------------------------------------------------------
XXX 01-JAN-13 31-MAR-13 1000 1000 1000
How to achieve this.
Table creation
SQL> create table concat_test (
2 empno varchar(10),
3 empname varchar (10),
4 description varchar(50) )
5 /
Inserts are
insert all
into concat_test (empno,empname,description) values ('1','pratik','want')
into concat_test (empno,empname,description) values ('1','pratik','to')
into concat_test (empno,empname,description) values ('1','pratik','concat')
select * from dual;
I want the o/p as
Empno empname description
1 Pratik want to concat