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