Inserting Into A Table By Selecting Multiple Rows Into A Single Row

Jul 27, 2012

I have a flat file as source wherein I am getting values like

Comp_id, Comp_name, ISIN, column_name, column_value

The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.

E.g. of Feed File -

Comp_id, Comp_name, column_name, column_value

1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023

Target table structure

Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode

I need to insert the above data to a table by selecting data from above scenario.

View 10 Replies


ADVERTISEMENT

Precompilers, OCI & OCCI :: Using Pointer To Structure For Selecting Multiple Rows From Table

May 11, 2010

I am working on Pro*C and i have a requirement where i need to select all the rows from a table into a c - structure variable. Since i get to know the no of rows in the table which is getting selected only at run time, i need to create a pointer variable to the structure and i'll allocate the size to it based on the count of rows in the table using malloc or calloc.I tried allocating memory using calloc and it does not show any error. But when i when the exec select statement run it shows an error.

Statements i have used:
struct common *comp;
struct common_ind *comp_i;

comp = (struct common*) calloc(rowcount, sizeof(struct common));
comp_i = (struct common_ind*) calloc(rowcount, sizeof(struct common_ind));

exec sql at db1 select * into :comp indicator :comp_i from tab1;

Error i get :

Stop Error: -2112
Stop Error: -1012
Stop Error: -1012

View 2 Replies View Related

SQL & PL/SQL :: Inserting Multiple Rows From Stored Procedure

Jun 20, 2012

Below are the data for rows that I want to insert into CUSTOMER_PRODUCT table from a stored procedure.

Instead of making round trips twice to insert these two rows, I looking for a way to pass in the data for both those rows and then insert them from within the stored procedure in one shot.

The stored procedure will be invoked by Java and .NET.

Sample Data for CUSTOMER_PRODUCT:

ROW 1:
CUSTOMER_ID : 1000
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

ROW 2:
CUSTOMER_ID : 1001
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

Question 1:
Should collection be used? (or) is there any other approach that could be utilized?

Question 2:
Are there any performance concerns in passing collection and iterating it to fetch value to insert into CUSTOMER_PRODUCT table?

I'm running Oracle 10g.

View 3 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name  

Null Type  ------ ---- ------------ ID_P        NUMBER       NAME_P      VARCHAR2(12) DATE_P      TIMESTAMP(6) Records---------------------1 loosi     22-AUG-13 01.27.48.000000000 PM1 nammi  26-AUG-13 01.28.10.000000000 PM2 kk        22-AUG-13 01.28.26.000000000 PM2 thej      26-AUG-13 01.28.42.000000000 PM 

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej  26-AUG-13 01.28.42.000000000 PM

View 4 Replies View Related

SQL & PL/SQL :: Getting Multiple Rows To Single?

Mar 24, 2010

I have records:

owner company
A X
A Y
A Z
B X
B Y
C X

owner companyX companyY companyZ
A 1 1 1
B 1 1 0
C 1 0 0

How do I write the SQL?

View 2 Replies View Related

PL/SQL :: Selecting Rows Where All Values Not In Another Table?

Jul 18, 2013

This is my table design: 

REQUEST(R_ID,attr1,attr2)WIPS(R_ID,WIP,attr3,attr4)SHIPPING(WIP,attr5,attr6) 

How do I limit my query to show the information from REQUEST but only where the all of the wips associated between REQUEST and WIPS are not in the SHIPPING table.  For example, the SHIPPING table has all of the WIPS that have been shipped, I only want to show the REQUEST rows where all of the WIPS have not shipped. 

View 2 Replies View Related

SQL & PL/SQL :: How To Convert Single Row Into Multiple Rows

Feb 28, 2012

CREATE TABLE T1 ( id NUMBER,
START_date DATE,
end_date DATE,
end_date1 DATE,
end_date2 DATE,
end_date3 DATE,
LEVEL1 number
)
/

[Code]...

I have data in the first table as mentioned above I need to insert multiple rows into the second table for the same ID depends on the level, If it is level 1 then two rows for same ID first reocrd start_date as the start_date and end_date as end_date from the table t1 for second record start_date is end_date in t1 and end_date for this record is end_date1 column in table t1.

If the level is 3 then the table t2 should have four records for one id and the phase is the value for each record for one ID for example in level 3 we have 4 records for one id and phase should be 1,2,3,4.

View 3 Replies View Related

SQL & PL/SQL :: Transform Single Row Into Multiple Rows?

Mar 22, 2013

writing the sql, to transform a single row into multiple rows. I am trying to create multiple rows based on a value of a column in the table.In the below example, I am trying to create the rows based on the 'Col2' values. find the below example:

Original table data:

Col1 Col2 Col3 Col4

Row1 a1 a,b,c 01 ON
Row2 b1 d,e,f 02 OFF
Row3 c1 g,h 03 ON

I want the above table to be transformed into below:

Col1 Col2 Col3 Col4

Row1 a1 a 01 ON
Row1 a1 b 01 ON
Row1 a1 C 01 ON
Row2 b1 d 02 OFF
Row2 b1 e 02 OFF
Row2 b1 f 02 OFF
Row3 c1 g 03 ON
Row3 c1 h 03 ON

View 2 Replies View Related

Fetching Multiple Rows For Single Column

Jun 25, 2012

I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.

Table is P_Order
Columns are DELIVERY_POSTCODE and TEL_NO...
Condition DELIVERY_POSTCODE has more than 3 TEL_NO

View 1 Replies View Related

Update Multiple Rows With Different Values In A Single Statement

Jul 24, 2009

Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.

Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.

Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.

MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.

Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row

UPDATE MEMBER M SET M.CORPIDENTIFIER=
(SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B
WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))

Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.

View 1 Replies View Related

SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?

Feb 16, 2011

The requirement I have is :

I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)

update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))

It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.

View 1 Replies View Related

SQL & PL/SQL :: Updating Multiple Rows With Single Update Statement?

Aug 20, 2013

create table temp_tst
(
FILENAME VARCHAR2(200),
EDITED_BY VARCHAR2(50),
EDITED_TO VARCHAR2(50)
)

[code]....

Can I write a single update statement to update filename column replacing "_tst" with "_check"?

View 1 Replies View Related

PL/SQL :: Concatenating Columns (in Multiple Rows) To One Column In Single Row

Nov 28, 2012

column1    column2            column3    column4

12                Mar-21-2005 BDW        blah blah blah
11               Feb-07-2001    ZV            ha ha ha
12                Jan-02-2002   YM           zuck zuck zuckI want a view that has that data like this:

column1    column2         

12                Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM     -      zuck zuck zuck
11               Feb-07-2001    ZV            ha ha haCan you help with SQL ?

I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"

View 3 Replies View Related

SQL & PL/SQL :: Split Single Row Into Multiple Rows Containing Time Periods Per Year

Nov 26, 2010

I have a table like this:

ID1 ID2 Ini_date End_date
1 1 2008-05-14 2010-09-16
1 2 2010-01-21 2010-08-26
..... ..... ............. ...................

and I would like to have a row for each year between ini_date and end_date.

ID1 ID2 YEAR
1 1 2008
1 1 2009
1 1 2010
1 2 2010

View 2 Replies View Related

SQL & PL/SQL :: Use Model Clause To Get Comma Separate Single Row For Multiple Rows?

Feb 19, 2010

I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:

SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)

SQL> select * from test1 order by id;

ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8

The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;

ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,

what I want is : null value should not come and duplicate value should not come (Value4 in output above)

View 11 Replies View Related

SQL & PL/SQL :: Exclude Rows While Inserting Into A Table

Jul 12, 2011

I have an insert statement like below.

insert into emp (select empno,ename,sal);

Here I need to exclude the rows having sal<0 from the SELECT query and insert those into some other table simultaneously.

View 4 Replies View Related

Forms :: Inserting Multiple Records In Table Through Tabular Form

Apr 1, 2012

i have a master-detail form.

detail block is tabular.

when-button-pressed trigger iam inserting records in another table.i write

insert into ONHAND_QTY_LOCATION(sno,matid,matcode,description,partno,onhand_qty,location)
values (:ship_dtl.slno,
:ship_dtl.mat_id,
:ship_dtl.mat_code,
:ship_dtl.description,
:ship_dtl.part_no,
:ship_dtl.rec_qty,
:ship_mstr.place_from)

it is inserting only one record in onhand_qty_location table that too the last record.

i want all the records which iam entering in detail block should get entered in onhand_qty_location table.

View 8 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

SQL & PL/SQL :: Update Multiple Table In Single Query

Jan 27, 2012

Actully i am updating two table of data.. but below error message came..

update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/

Error at line 1
ORA-01776: cannot modify more than one base table through a join view

View 10 Replies View Related

SQL & PL/SQL :: Delete Multiple Table Data By Single Query Without Any Condition?

May 10, 2012

can we delete multiples table through the single query?

suppose we have 2 table first one is emp and second is client

i want delete all data from emp and client through the single line query

View 1 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.

View 11 Replies View Related

Selecting Via Multiple Tables

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]...

What I get is in green (services are calculated more than 2 times BUT in the same report).

What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]...

One person (zmo_kodas) can have a lot of reports (ats_nr).

Every report can have one or more services (pas_kodas).

View 1 Replies View Related

SQL & PL/SQL :: Selecting First Row From Multiple Joins?

Apr 2, 2010

I'm putting together a path to select a revision of a particular novel:

SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s

[code]...

My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.

When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.

One other challenge is the format of the revision- a revision sequence could look like this:

A
B
C1
C2
C
D
E1
E

So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:

A1
A
B1
B

I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).

View 12 Replies View Related

SQL & PL/SQL :: Selecting Via Multiple Tables?

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]

What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]

One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).

View 2 Replies View Related

Selecting 100 Number Rows?

Jan 15, 2011

how can I select whole table in parts of 100 rows?

If I have primary key I can:

CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;

How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?

View 1 Replies View Related

SQL & PL/SQL :: Joining One Row To Multiple Rows In Same Table?

Feb 8, 2011

I have two tables. The first contains all Segment information within Oracle i.e;

Table 1
Segment Description Flex_Value
1 North 1234
1 South 1235
1 East 1236
2 Car C001
2 Boat B001

and the second table contains financial data, but only the segment code;

Table 2
Date Segment1 Segment2 Value
01/01/11 1234 C001 10,000
02/01/11 1235 C001 10,000

what I want to return is some of the columns within table two as well as additional columns for the segment descriptions.

Below is an extract of what I have put together, and it does return what I need, but I'm sure there is a much more efficient way of creating the query;

SELECT

b.DATE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,

[code].....

View 6 Replies View Related

SQL & PL/SQL :: Inserting Single Quotes

Dec 4, 2012

I have a string: 'VOLT,AGE'..

How can I convert this string to: 'VOLT','AGE' using REGEXP_REPLACE...

I am having trouble escaping the single quotes in my query

View 6 Replies View Related

SQL & PL/SQL :: Comparing Multiple Columns According To Rows From Same Table

Aug 25, 2011

I am new to oracle, I have request to build a query,

we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.

Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...

View 12 Replies View Related

Replication :: Selection Of Multiple Rows In OAF Table

Sep 7, 2012

how to select multiple rows in an OAF table and access them in the code.

View 0 Replies View Related







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