PL/SQL :: Split Column Values

Mar 11, 2013

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.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Split Values From A Column

Jul 14, 2010

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

View 5 Replies View Related

PL/SQL :: Split String Values

Feb 28, 2013

I have a column in a table that contains a string seperated by .

e.g.

IT.HARDWARE
IT.APPS
IT.SOFTWARE

I would like to split the two values out on two columns e.g.

Column1 - IT
Column2 - Hardware
etc.

View 3 Replies View Related

Query To Split Records Based On Values From Another Table?

Feb 20, 2012

I have a table with following values in a column

Table A

col1
10
35
20
25

I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.

Table B- 90 records (10 + 35 + 20 + 25)

Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,

with first group having start value -1 and end value -10

second group - start value -11 and end value-45

third group - start value -46 and end value -65

fourth group - start value - 66 and end value-90

one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.

note that the values in Table A is dynamically changing, so not able to hard code values.

View 2 Replies View Related

SQL & PL/SQL :: Split Column Value To Two Different Target Column

Dec 26, 2011

I have a table with date_field and ID number

Now based on the id number I have to redirect the date_field value to two different column

I/p

CASE_DT DT_TYP_CD
2006-07-01 00:00:001
2007-07-01 00:00:001
2008-07-01 00:00:001
2007-07-01 00:00:0011
2008-07-01 00:00:0011
2009-07-01 00:00:0011

Now I want the case_dt value to two diff column PL_Date and PL_Aniv_dt

If dt_typ_cd is 1 the case_dt value will go to PL_Date and if dt_typ_cd is 11 the case_dt value will go to PL_Aniv_dt

O/P
PL_dt PL_Aniv_dt
07/01/2006 0:0007/01/2007 0:00
07/01/2007 0:0007/01/2008 0:00
07/01/2008 0:0007/01/2009 0:00

View 8 Replies View Related

SQL & PL/SQL :: Split Gender To Two Column?

Feb 8, 2012

I have a table like follows

Name Gender
-------------
Arun M
Anitha F
Bala M
Banu F

I need the output as follows

Male Female
-----------------
Arun Anitha
Bala Banu

What are the ways can we generate the above query

View 14 Replies View Related

SQL & PL/SQL :: Split Result Set Into Column

Aug 17, 2012

i have a table emp with three column

columns are (empid varchar,empnomini varchar,nominitype varchar), data in table like

empid empnomini nominitype
1 x B
1 y c
2 xx B
2 yyyy c

and i want data comes like

empid nominitype b nominitype c
1 x y
2 xx yyyy.

View 6 Replies View Related

Split Date Column Into 4 Month Periods

Feb 11, 2011

I have a table having a code column A and a date column D1 which represents the days when the code was inserted. I can have more then 1 date for a code.

The idea is to create another column (an id) which will correspond to all the inserted days of a code for a period of 4 month. if, after 4 month of the first insert, another records appears, then another id will be generated for my code. If a code has the date included in one of the intervals existing already, then it will just receive the corresponding id.

Example:
id code date
id1 cd1 01/01/2010
id1 cd1 04/03/2010
id1 cd1 22/04/2010
id2 cd1 27/05/2010
id2 cd1 21/06/2010

If a cd1 appears now on 22/05/2010, then i have to give it id2.

View 3 Replies View Related

PL/SQL :: How To Split Column Data Based On Identifier

Oct 3, 2012

I am using oracle database.

I am having data in this format in my column 1234~2345~3456~4567.

I need a query to split the data in the column based on the identifier '~',so that i can pick out the value after the second occurrence of the identifier.

View 3 Replies View Related

Split Text Using Comma In Column Using Rtf Template In BI Publish

Nov 2, 2012

I have one issue regarding this column values displaying ,in one column value has contains big length ,total i have 8 columns ,now i want to show all columns with fixed width even data in particular column had big length will display next line not next row.

example: oracle and "oracle,Business,intelligence" are the data in two columns these two columns data column 1 and column 2 now i want to display in column1 'oracle' and column2 first line 'oracle' ,second line 'Business ' and next line 'intelligence' like below

oracle
Business
intelligence

View 2 Replies View Related

PL/SQL :: Split Result Of Table Data Comparison To Key(s) / Column / Old - New Each Row

Nov 8, 2012

I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)

how to compare these two tables to generate something like the following. I can join these two tables to generate the diff but it is one row per account.

client_nbr branch_cd, account_cd, action column, old_value, new_value
8888 123 45678 C account_clsfn_cd 004 005
8888 123 45678 C buy_cd 98 99
8888 012 34546 A sell_cd 12
8888 321 98765 D dividend_cd 1

I am using Oracle 10g so Unpivot cannot be used.

CREATE TABLE T1
(
CLIENT_NBR CHAR(4 BYTE) NOT NULL,
BRANCH_CD CHAR(3 BYTE) NOT NULL,
ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
ACCOUNT_CLSFN_CD CHAR(3 BYTE),
SELL_CD CHAR(2 BYTE),
BUY_CD CHAR(2 BYTE),

[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My 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]...

View 9 Replies View Related

PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column

Jul 25, 2013

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 Related

Application Express :: Hyperlink Column With Link Parameters As Region Column Values

Apr 10, 2013

On 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"

View 2 Replies View Related

Application Express :: Can Pass More Than 3 Column Values With A Column Link

Jun 19, 2012

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 Related

SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column

Jul 19, 2013

CREATE 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

View 4 Replies View Related

SQL & PL/SQL :: Column Values To Column Names

May 20, 2011

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

View 13 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

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

View 4 Replies View Related

SQL & PL/SQL :: Max Row Values Into Column

Oct 3, 2011

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

View 2 Replies View Related

SQL & PL/SQL :: Column Values In Row

May 10, 2011

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.

View 6 Replies View Related

Get Values From Table Column Using PL/SQL Or Just Using SQL

Dec 3, 2008

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 ?

View 3 Replies View Related

SQL & PL/SQL :: Multiple Values In One Column?

Nov 20, 2010

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...

View 8 Replies View Related

SQL & PL/SQL :: Update Same Column With Different Values?

Dec 6, 2012

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...."

View 16 Replies View Related

SQL & PL/SQL :: Multiple Values In Column

Jan 10, 2013

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.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Multiple Column Values In A Row

Oct 30, 2012

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

View 4 Replies View Related

SQL & PL/SQL :: Return Column Name Corresponding To Values

Apr 28, 2011

I want to search a some values in oracle table and then return the corresponding column names.

View 1 Replies View Related

SQL & PL/SQL :: How To Get Column Values In Procedure

Apr 5, 2010

My 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?

View 2 Replies View Related

SQL & PL/SQL :: Distinct Values In A Column?

Jul 18, 2013

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.

View 12 Replies View Related

SQL & PL/SQL :: Values Which Are Not Present In Column

Jun 2, 2011

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)

View 12 Replies View Related

SQL & PL/SQL :: Product Of Values In The Column?

Jul 29, 2010

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved