SQL & PL/SQL :: Pick Up The Data From Table 1

Jul 9, 2010

I have a reqmt that is little complex.

Example: (Real Data)

Table1 (Column Name)
Height
Width
Color

Table2
ID,Height, Width, Color, Remarks
1,2,3,NULL,NULL
2,NULL,NULL,2,NULL
3,1,2,3,4

What I require is pick up the data from table1 and for each row in table1, check the corresponding column to see if null. If null create a counter and add 1 to it.

I would only check for 1 record at a time in table2 (ID is input parameter in pl/sql) In other words, if my ID is 1, my count should be 1 (since COLOr is null in table2 for id=1 and color is a row in table1) if my id is 2, count is 2 (ht and width are NULL and are both separate rows in table1) if id is 3, count=0 since all fields have data even though some of these columsn exists as rows in table1.

View 25 Replies


ADVERTISEMENT

Forms :: How To Pick Excel File And Get Data In Column

Jun 18, 2013

How to pick excel data in oracle form 6i. and data show in forms column ?

View 1 Replies View Related

SQL & PL/SQL :: Pick Up 3% Of Records Per Day?

Oct 12, 2010

create table test1(x number, y date)

insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');

[code]....

I need an sql which would pick 3% of the records per day

select count(*), y from test1
group by y
21 10/1/2010
14 10/2/2010
6 10/3/2010

So I need 3% of 21 (.63) randomely picked up for 10/1.

similarly 3% of total number of rows from 10/2, 10/3 to be picked up.

View 2 Replies View Related

SQL & PL/SQL :: Pick One Out Of Duplicates?

Jul 19, 2010

I have need, where I need to pick-up the one of the records out of 2, which have all the fields same but one key column? how should I do it?

sample data:

IDnamecreation dateupdate date
121233ATR7/19/2010 15:307/19/2010 15:30
1213344MTR7/19/2010 15:307/19/2010 15:30

For most of the cases, i am picking latest data using update date, but here where dates are same, I am stuck.

View 2 Replies View Related

SQL & PL/SQL :: Pick A Value Based On Range

Mar 10, 2012

I need to pick a value based on range like if the range is as below

[code]
if value =2000 then its 2000
elsif value >=2001 and value <=2499 it should be 2000
elsif value =2500 then 2500
elsif value >=2501 and value <=2999 it should be 2500
elsif value = 3000 then it should be 3000
[code]

Like this i need to pick a value by hardcoding this range and this look cumbersome in my program , is there a simple way to substitute this entire thing by passing one single value and getting one single value using a command or builtin.

View 2 Replies View Related

Forms :: Pick Single Value From Range?

Mar 19, 2012

i have to pick single value from the given range based on value entered by user, for example if user enter the length 2499 it should be considered as 2000 if he enters 2000 its 2000 or he enters 2501 it will return 2500 , i have written a huge if condition for this purpose in my program which looks very cumbersome,is there a simple to do this other than this method.

the code is as below.

[CODE]
IF
( :CSDO_STORE_RECD_LENGTH - SUBSTR(:CSDO_STORE_RECD_LENGTH,(LENGTH(:CSDO_STORE_RECD_LENGTH)-1),LENGTH(:CSDO_STORE_RECD_LENGTH))) =2000
THEN M_RCVD_LENGTH := 2000;
ELSIF

[code].....

View 9 Replies View Related

PL/SQL :: Pick All Continues Range Of Values Into One Bundle

Jul 11, 2013

I got a situation to develop a complex query. Here is the scenario. I have to pick all the continues range of values into one bundle. like in following example, rows having values between 20 to 25 should have one bunch and 27 to 28 should have another as there is no from or to value from 25 to 28. with q1 ( select 20 as Frm, 25 as tto from dualunion allselect 20 as Frm, 21 as tto from dualunion allselect 20 as Frm, 22 as tto from dualunion allselect 20 as Frm, 20 as tto from dualunion allselect 21 as Frm, 22 as tto from dualunion allselect 28 as Frm, 28 as tto from dualunion allselect 27 as Frm, 28 as tto from dual)select * from q1; 

View 10 Replies View Related

SQL & PL/SQL :: Audit Trail Trigger To Dynamically Pick Columns During DML?

Jun 13, 2010

I want to make a trigger , to store values that are affected during any DML(insert,update,delete).

CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE

[code]....

View 8 Replies View Related

Reports & Discoverer :: Pick Latest Record Rank And Count?

Sep 1, 2010

Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this

name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010

I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1

For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'

View 3 Replies View Related

SQL & PL/SQL :: Delete Previously Stored Data And Enter Only Current Data In A Table?

Jul 24, 2012

how to insert data in a table by deleting previous entered data and only inserting current data like:

CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);

[code]....

I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:

INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);

it must show aaa,8 and aaa,9 bt not the previous values.

NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.

View 4 Replies View Related

Forms :: First Form Clear Previous Data And Then Populate Data From Table

May 7, 2012

when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below

go_block('show');
clear_block(NO_VALIDATE);
declare
cursor c1 is select *
from qtr_demand order by 1;
begin
[code]..........

View 26 Replies View Related

Forms :: 6i - One Data Block Based On Table / Fetching Data

Nov 3, 2011

I am working on forms 6i. I have one data block based on table EMP.

Datablock-A: Empno, Ename, Sal

I have FIND Screen: Empno, Ename, FIND Button.

I can search the data through FIND Screen and populate data in Datablock A.

I am using below logic to search data through FIND Screen.

When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.

In Pre-query trigger of DatablockA:

copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');

It's working fine. But below case is failing.

Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.

But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.

why it's failing to pull records if i try to query data in first case only.

View 2 Replies View Related

Inserting Data In New Column Where Table Has Huge Data

May 26, 2013

I am trying to add a new column in a table and insert data from another column of same table.

alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit

As there are huge number of records in the POSITION table ...its taking for ever to execute this query.

View 1 Replies View Related

SQL & PL/SQL :: File Data Versus Table Data Comparison

Jun 21, 2010

I have written a java code which reads 2 millions of data under a particular column from CSV file and store it into a set. Now there is a table in Oracle database which contains 10 millions of records for that particular column. Now, I want to form a SQL query which select those records under that particular column from the database table which is in CSV file but not in database table. For e.g.

If I consider the CSV file name as employee.csv and it has column called employee_name under which the records are as follows

employee_name
---------------------
Sudip
Kaushik
Joyanta
Soumya
Ritesh
Gautam

And the database table name called cmp_employee and it has column called employee_name under which the records are as follows

employee_name
--------------------
Sudip
Kaushik
Joyanta
Soumen
Souvik
Sahoo

Now, the "SQL Query" should return ---- "Soumya, Ritesh, Gautam" this three names.

View 2 Replies View Related

SQL & PL/SQL :: Import Data From Csv To Temp Then Alter Data As It Goes Into Another Table?

May 30, 2012

I need to import some data from .csv files. There is one file each day, so I want them to be automatically imported into the DB. This is the format it comes in:

DSM,LOD,20120524,01,01,9999AMP02,1.1262240,M,0.6397380,M
DSM,LOD,20120524,01,02,9999AMP02,1.1315700,M,0.6450840,M
DSM,LOD,20120524,01,03,9999AMP02,1.1297880,M,0.6450840,M

I want this data to go into TEMP_TABLE. It then needs to reformatted as it goes from temp_table to my_table:

filename,readingID, field1,field2,date,num1,num2,meterid,read1,m1,read2,m2

so filename is the actual name of the .csv file that this row came from. And reading id is date, num1, num2, meterid combined. And the remaining fields coming from temp_table

This is what I have:

procedure import_data()
begin
TEMPFILENAME CHAR(60)='DSM_2010_Husky_Oil_20120525064122_20120525065011.csv';
create table temp_table
(dsm char(3),lod char(3),usage_date date,he char(2),reading char(2),loc_id char(9),mwh number(15,10),eormmwh char(1),mvar number(15,10),eormmvar char(1));

[code]....

, which does not work at all.

View 2 Replies View Related

PL/SQL :: Queries To Get Data In Batches From A Huge Data Table

Jan 3, 2013

Here is my problem, i need to create some files with my own format(let say 5000 records each) from a huge data table (May contain 5 Million records). And i want this creation to be multi threaded.

so how can i form queries efficiently to fetch records like 1..5000 and 5001..10000 and so on. I can form some thing like select * from table where rownum<5000 and not exists ( already fetched records) . but it is not the efficient one.

View 5 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....

View 24 Replies View Related

SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database

Jul 19, 2010

There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.

Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.

View 19 Replies View Related

SQL & PL/SQL :: Pull In Last Loaded Data From Staging Table To Target Table?

Mar 9, 2011

I have a staging table and a target table. How do I pull in last loaded data from staging table to target table?

View 4 Replies View Related

Forms :: Read Data From Table And Insert To Another Table With A Cursor?

Feb 20, 2013

I have a table with a BLOB column ;

I want read data from table and insert to another table with a cursor

My code is :

procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;

[code]....

when I run form , error FRM-40734 occurred

error in line " fetch .... "

View 1 Replies View Related

SQL & PL/SQL :: Select Data From Test-1 Table Where ID Values In Table Exists In 2?

Aug 31, 2010

I have the below data in table test_1.

select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100

test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3

My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.

SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)

create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)

View 4 Replies View Related

SQL & PL/SQL :: Inserting Data In Target Table From Source Table

Nov 22, 2012

I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.

ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h

How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.

View 4 Replies View Related

SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table

Jul 18, 2012

I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.

CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......

View 6 Replies View Related

SQL & PL/SQL :: Table Is Missing In User_segments Data Dictionary Table

May 20, 2012

In my database I found some anomalies like

SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....

The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.

SQL> SELECT * FROM COUNTRIES;

CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...

why the country table is missing in user_segments data dictionary table.

View 8 Replies View Related

PL/SQL :: Load Data From Index By Table To Table

Jun 27, 2013

We need to load data from index by table to table.Below code is working fine. 

declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin

[Code]....

But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............ 

create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;

[Code]....

Its throwing. How to implement this scenario .

View 2 Replies View Related

Moving Data From Table To Table

Dec 13, 2010

I need to move data from non-partinioned table to partitioned. The volume is about 60 millions rows. What is the fastest way to do that? I think about pareller insert and nologging. What do you think about this? May data pump be faster?

View 4 Replies View Related

SQL & PL/SQL :: Data Migration From Table To Table?

Nov 18, 2010

I Have two DB s with same tables, I need to insert the data from db1 tables to db2 tables. Primary ID starts from 1 on both the tables.

EXample:

Table1:
ID Name c3 c4 c5
1 Oracle1 x y b
2 Oracle2 x n b
3 Oracle3 x f b
4 Oracle4 x f b
5 Oracle5 x f b

Table2:

ID Name c3 c4 c5
1 ONT1 t y t
2 ONT2 t m h
3 ONT2 t b n

All the table1 data should be moved to table2..... Desired out put should be like following

ID Name c3 c4 c5
1 ONT1 t y t
2 ONT2 t m h
3 ONT2 t b n
4 Oracle1 x y b
5 Oracle2 x n b
6 Oracle3 x f b
7 Oracle4 x f b
8 Oracle5 x f b

Data volume is huge... There are 1500 tables and on an average there 10 Million records per table. Currently I am using DB links

View 2 Replies View Related

PL/SQL :: Moving Data Into From One Table To Another Table

Sep 2, 2013

 I have a table called Daily_usage (only 1 day data) which contains daily transaction records. After a day I have to move this data to another table named Daily_30days_usage table. this table contained the 30 days data. After 30 days the 31st day data should be deleted from Daily_30days_usage table. 

How can I implement this requirement without INSERT statement?

View 4 Replies View Related

Export/Import/SQL Loader :: Import Table Without Messing Up Existing Data In Table

Sep 6, 2012

table already exist & its little data too, may have to imp rest of lost data, is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y CONSTRAINTS=n

scenerio2 (if have to drop & recreate the entire table) is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y

just for single table imp

View 2 Replies View Related

SQL & PL/SQL :: How To Sum Data Of One Table Into Another

Jul 19, 2011

I have this table that stores following information

SQL> select * from data1;

CUST_ID IU OHB
--------- ----- ----------
001 1 10
001 2 10
001 3 20
001 5 25
002 4 30
003 7 10
003 8 35
004 11 40

8 rows selected.

I need to insert data from this table to another table. ( result1)

SQL> desc result1
Name Null? Type
----------------------------------------- -------- ---------------------------

CUST_ID CHAR(9)
SUM_OHB NUMBER

The output when result1 is queried should look like

SQL> select * from result1;

CUST_ID SUM_OHB
--------- ----------
001 65
002 30
003 45
004 40

Want to do in PL/SQL.

I have tried to simplified my original requirement. This is crux of that. Can not post other details.

Edited to add create and insert script

create table data1( cust_id char(9), iu char(5), ohb number);
INSERT INTO DATA1 ( CUST_ID, IU, OHB ) VALUES (
'001 ', '1 ', 10);

[Code]....

View 1 Replies View Related







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