SQL & PL/SQL :: Table Being Split Into Two

Jun 2, 2011

i'm working on this database assignment...and basically, no matter what I do my table seems to automatically split into a different table after 8 rows...i've tried googling it and using commands like....

set wrap off;
set numwidth 20;

and fiddling with the format of each column but nothing seems to work...i just want my table to show as one table

here's my script
create table patient
(patient_number number(4) primary key,
patient_name varchar(15) NOT NULL,
address varchar(30) NOT NULL,
telephone number(7) NOT NULL,
patient_status char(1) NOT NULL,
next_appt date,
balance number(5,2),
CHECK (balance >= 0),
CHECK (patient_status='N' OR patient_status='A' OR patient_status='I'));
[code]...

12 rows selected.the columns are actually aligned so don't worry about that...it's just the splitting of the rows.

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Split Table Into Two Tables

Oct 16, 2013

There is a table with almost 60 million records. This is a temporary table or rather a staging table. I want to be able to split the table into two tables. This is an example of what the staging table looks like.

sales_persondeploy_adddayobjxobjyobjz
--------------------------------------------------------------
1001 900201201506080
1001 900201201646040
1001 958201202506080
1001 958201202407050
1002 141201201356080
1002 147201203502080
1002 147201203506010
1002 141201201506080
1002 147201203506010
1002 147201203506080

I am trying to split the table like the following

--------
Table 1:
--------
trns_idsales_persondeploy_addday
----------------------------------------------------------------------
65500 1001 900 201201
65501 1001 958 201202
65502 1002 141 201201
65503 1002 147 201203

And the detail line table which looks like following

------
Table 2:
--------
tr_dt_idtrns_trns_id xyz
----------------------------------------------------------
100165500 506080
100165500646040
100165501506080
100165501407050
100265502356080
100265503502080
100265503506010
100265502506080
100265503506010
100265503506080

I come up with two ideas for the problem.

1. The straightforward loop. Read each record > Go through table 1 , get a count of similar records> If exists, use that ID to populate detail table > If doesn't exist create a new ID and populate the detail table

2. Use two cursors. First cursor goes through all the records (*). Takes 1 line, creates an ID on 1st table. Second cursor finds all records that are similar to the first line. Populates the detail table using the ID and then deletes currently inserted rows from the staging table.

I have got the first idea working. It works fine but takes forever since it has to go through the whole table for selecting the count for each record.I tried implementing the second idea but I believe the cursor creates a snapshot of table at runtime so within the loop if it finds out record has been deleted, throws me an error.

View 9 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 :: Maintaining Data Integrity When Single Table Split Into Two

Aug 7, 2013

I've an Oracle Table which has around 300 columns. I've a requirement to split this single table into two tables (150 columns each) by a foreign key.

Now I want to know how to maintain the data integrity while I insert the data into two tables. which means each table should have equal number of rows as we insert the 300 columns data into tables each at a time.

View 8 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

Performance Tuning :: Split Large Table To Small Pieces?

Mar 28, 2011

I have several large tables in the live system! Those table are store historical information.

current situation:

Now, A table record was 129 million rows.

Every month added 4.5M records to this table.

This table data size 17GB and index size 28GB.

I have only 30 GB available free space on disk!

How to split this table to small pieces (partition table by month)?

What is the best approach?

I would like to do partitioning on this table month by month.

View 12 Replies View Related

SQL & PL/SQL :: How To Split A String

Dec 21, 2011

I have strings like

1) ICE_10001 ICE_10002 ICE_10003

2) ICE_10005 ICE_10006

i want to split above strings like

1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006

If it is possible in oracle sql

View 15 Replies View Related

PL/SQL :: How To Split Strings

Oct 18, 2013

PROCEDURE COLUMN_SPLIT (p_def   IN VARCHAR2, p_sch  OUT VARCHAR2, p_table OUT VARCHAR2, p_column OUT VARCHAR2) 
IS  
BEGIN 
NULL;  
END;
END; 

I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column for example p_sch will be like hello.howare.you.I want to split it to hellohowareyouI have very limited knowledge with pl/sql.

View 13 Replies View Related

Split A String In Oracle

Sep 27, 2010

I have a string like '9999999;A' one field as numeric & other as char.Now i want to split this string into two values removing the ; delimiter in oracle using for loop.

View 2 Replies View Related

Split A String Up If It Has Space?

Jun 19, 2008

i have a column called name in a table. now what iwould like to do is to check if it has two parts "paulh some" and then output the second part!

SELECT LTRIM(name,' '), length(name) length
FROM list
WHERE INSTR(name,' ') = 1;

but that doesnt work.. the fucntion is NOT checking for the space! if i use another character (a or b etc) it works..

View 2 Replies View Related

Split Value And Sort By Lastname?

Dec 11, 2008

I have a field called fullname that outputs records with fullname of people.

Here is what I have when I do this sql:

Select fullname from tableOne;

John Jones
Bill Aronsen
Sam Baker
George Williams
Dave Smith

I would like to sort in order of last name but cant figure out how to do the sql:

Bill Aronsen
Sam Baker
John Jones
Dave Smith
George Williams

View 1 Replies View Related

SQL & PL/SQL :: Split Long String

Feb 19, 2013

how I can change this function to pass argument that length is 32676 .

create or replace TYPE "HRS_SPLIT_TBL_T" as table of varchar2(32767);

create or replace function hrs_split
(
p_list varchar2,
p_del varchar2 := ','
) return hrs_split_tbl_t pipelined
is

[Code]...

I got error String literal too long when execute select below

select distinct COLUMN_VALUE Tbat_latn
from table(
HRS_SPLIT( 'PER0000002,PER0000094,PER0000094,PER0000096,PER0000096,
PER0000024,PER0000024,SAB0000001,SAB0000001,PER0000002,
PER0000096,PER0000094,PER0000094,PER0000002,PER0000024,
PER0000024,PER0000096,PER0000096,PER0000094,PER0000094,
PER0000002,PER0000024,PER0000024,PER0000096,PER0000096,
PER0000094,PER0000094,PER0000002,PER0000024,PER0000024,

[Code]....

View 12 Replies View Related

SQL & PL/SQL :: Split String In Two Halves?

Oct 23, 2013

I want to split any user given string into two parts, how can I do it.

suppose some sample inputs may be:

'123456' splits into '123','456' ,'123456789' splits into '12345','6789'

NOTE: the first half splitted string length is greater in case of odd no. string as in second example

How can I do it?

View 13 Replies View Related

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

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 Csv To Multiple Records

Jun 18, 2013

I have a small requirement...

Create table temp_a (source_code varchar2(100), target_code varchar2(1000));

Insert into temp_a values ('1','002.0 AND 002.9');
Insert into temp_a values ('2','729.90 AND 079.99 AND 002.9');

Output :

1 002.0
1 002.9
2 729.90
2 079.99
2 002.9

So, once we get the output, it needs to be joined to another table. I did Google search, but most of them are retuning collections / arrays as output. Not sure how I join the collection with the table.

create or replace function splits
(
p_list varchar2,
p_del varchar2
) return split_tbl pipelined
is
l_idx pls_integer;
[code].......

View 3 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

SQL & PL/SQL :: Split Ranges In Database?

May 5, 2010

I have been wracking my brain on this. Is there a way to write an SQL code that will combine split ranges within a table?

SAMPLE_TABLE
Common_FieldLow_ValueHigh_Value
1123123
11243000
130023005
130064000
135003501
130064500

I would like to combine any ranges that may exist. It is also possible that some row ranges may be nested in other rows.

END_RESULT
Common FieldLow ValueHigh Value
11233000
130024500

View 9 Replies View Related

SQL & PL/SQL :: Function To Split A String?

Oct 18, 2004

I have a string like 10.01.03.04.234 or 234.05.07.032.� Is there a simple function in Oracle that would give me

10
01
03
04
234 as seperate values in a select clause.

I would like to do a

Select function(10.01.03.04.234)
into var1, var2, var3, var4, var5
from ....

I can accomplish the same with substr/length/instrs in a procedure...but looking for a simple function, if any, instead of writing a proc/function....

View 39 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

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 View Related

Use Split Partition Option?

Jan 28, 2013

I am using 11.2.0.2 database.

I got ORA-14074 error says that if you have a MAXVALUE already you will need to either drop the partition that encompasses the MAXVALUE or split partition.So I want to use split partition option through the below statement,

alter table
tablename
split partition
partmax at (XXX)
into
(partition partXXX, partition partmax);

I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.I have to do this on produciton. I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.

View 6 Replies View Related

PL/SQL :: Query To Split Field Value

Jul 31, 2013

In employees table I am having employee_name column data as follows 

Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean  Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->

How to achieve this by SQL query.

View 3 Replies View Related

SQL & PL/SQL :: Split Months In Sql Statement

Oct 29, 2012

I need to display the month as below, in a sql statements.

MONTH
JAN-1-15
JAN-16-31
FEB-1-15
FEB-15-28
MAR-1-15
MAR-15-31

i tired with this, but it dint come

select trunc(sysdate, 'year') one, trunc(sysdate, 'year') + floor((last_day(trunc(sysdate, 'year')) + 1 - trunc(sysdate, 'year'))/2) - 1 two,
trunc(sysdate, 'year') + floor((last_day(trunc(sysdate, 'year')) + 1 - trunc(sysdate, 'year'))/2) three, last_day(trunc(sysdate, 'year')) four from dual

View 2 Replies View Related

Oracle Dataset - How To Split Datetime

Jun 2, 2008

my problem is:

i running a sql-query in visual studio 2005 with the oracle dataset. currently my datetime is in format mm/dd/yyyy hh:mm:ss. I wish to split the datetime in dd/mm/yyyy only(without the hh:mm:ss)

EXEC ('
SELECT
XNP_TIMER_VIOLATION.VIOLATION_TIME,..
FROM XNP_TIMER_VIOLATION,..
) AT npcrpt ;

View 4 Replies View Related

Create View Off Split String

May 22, 2012

How do i split a string based on a space. And then create a view with three columns of that split string.

For example if i have a table with Full name as a column how do i split that string and create a view with firstname, middlename, lastname as column names with the split string as data.

If you need anything clarifying write back.

View 1 Replies View Related

SQL & PL/SQL :: Split A String As Per Required Format

Dec 23, 2010

Split a column with values like 1-2-21-3 into 001-002-21-003 (ie format required is 000-000-00-000) using sql.

View 3 Replies View Related

SQL & PL/SQL :: Split A Number In Unequal Parts

Aug 12, 2013

I have 2 tables:

A) Products
PRODUCTAMOUNT
123 10

b) Appearances
PRODUCTAPPEARANCEID
1231
1232
1233

1 product has a price; and can have several appearances. Now we want to know the price not by product but by appearance, for that we'll just dived the amount of the product by the nr of appearances it has. In this case we would like the result to be:

PRODUCTAPPEARANCEIDamount
1231 3,34
1232 3,33
1233 3,33

The remainder is added to whatever appearance; but the sum of all equals the amount in the products table.

View 16 Replies View Related

SQL & PL/SQL :: How To Split Time Frame In Intervals

May 26, 2010

I want to split the time frame into 3 hours interval as my requirement is that the number of application submitted between 12.00 am in morning to 12 pm in night and between that time interval i have to calculate in every 3 hours the no of application coming to DB

12:00am to 3:00am
3:01am to 6:00 am
6:01am to 9:00am
9:01am to 12:00pm
12:01pm to 3:00pm
3:01pm to 6:00 pm
6:01pm to 9:00pm
9:01pm to 11:59pm

View 18 Replies View Related

SQL & PL/SQL :: Split Varchar Field Into 2 Fields?

Mar 22, 2010

I need to separate 1 field into 2 fields. The source is varchar2 and is like:

[VOUCHER]
CGJ0000617
CG0001442
CGJ0001444
CMOV0000200
CXAR00000001
CXAR00000002

Result should seperate numeric value from characters so that the result would be:

[VOUCHER_char] [VOUCHER_num]
CGJ 0000617
CG 0001442
CGJ 0001444
CMOV 0000200
CXAR 00000001
CXAR 00000002

I need one or two SQL statement.

View 9 Replies View Related







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