SQL & PL/SQL :: Values To Columns Of Another Table

Dec 20, 2012

I need to add values of one column values from a table to another table each value as a column. Below i am considering only for 3 values in real time i have more than 50 values

CREATE TABLE TEST_REG ( VAL VARCHAR2(1));

INSERT INTO TEST_REG VALUES ('A');
INSERT INTO TEST_REG VALUES ('B');
INSERT INTO TEST_REG VALUES ('C');

CREATE TABLE TEST_HOLD ( COL1 VARCHAR2(1),COL2 VARCHAR2(1),COL3 VARCHAR2(1)); -- in realtime i have 100 columns

Now i need the output as:

when i
select * from test_hold;

COL1 COL2 COL3
A B C

View 6 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Columns With NULL Values?

Dec 17, 2010

We are working on a migration project and we need to move 75 million rows from source system to target system.

Total number of columns in source system - 90 cols.

Out of the 90 columns 10 cols are system fields and rest 80 are properties for each record.

We are required to migrate all system cols and some required properties. In total we will migrate around 25 columns[10+15] for each record.

Before actaul migration , we need to do a data cleansing activity and hence we move the data to a staging table.

To create the staging table, we considered the below appraoches.

1. Create the staging table with around 30 coloumns so as to fit the data from source system[map the columns based on datatype]

2.Create the staging table with actual columns[90 columns] and import only the required properties. The rest all columns will remain NULL.

Do the data cleansing and move to target system.

My question here is, if we go with approach 2, We will not mix the data, as there will be a one-to-one mapping. But many columns will not have data and remain NULL. Will it affect the performance since we deal with 75 million rows.

View 3 Replies View Related

SQL & PL/SQL :: Duplicate Values In Two Columns?

Apr 25, 2011

How to find the duplicate values in two columns.

Suppose we have two columns A and B and the data looks like this

A B
--- ---
1 One
2 Two
1 One
2 Two
3 Three

I need to write a query in such a way that i should find out the duplicate values which are repeating.

View 7 Replies View Related

SQL & PL/SQL :: Getting The Rows And Columns Values

Sep 3, 2010

I want to get the values and put them into html template since i want to configure mime settings. My table's name is rawticket_voip and it has 150.000rows and 20 columns so instead of COL1, COL2 what should i write there? How can i get the spesific row's and column's value and how can integrate loops in this html?

'<html>
<table border="1">
<tr bgcolor="Light Blue">
<td>COL1</td>
<td>COL2</td>

[Code]...

View 4 Replies View Related

PL/SQL :: Converting A Row Into Columns Values?

Sep 6, 2012

I have a row which contains 6 columns where I want that data to be shown in the form of columns as shown here:

From:

select item1, item2, item3, amt1, amt2, amt3 from item_table where sno=1; <----- returns 1 row as below

ITEM1 ITEM2 ITEM3 AMT1 AMT2 AMT3
---------- ------------ ------------- ----------- ------------ ----------
AAA BBB CCC 10.00 20.00 15.00

Data explanation: item1's (AAA) price is amt1 (10.00), item2's (BBB) price is amt2 (20.00) and item3's (CCC) price is amt3 (15.00). OK.

Now I want that data to convert into columns as shown here:

To:

ITEMS AMT
--------- ---------
AAA 10.00
BBB 20.00
CCC 30.00

I want a SQL to display this data.

I found one query which converts a row into columns, but this does not serve my requirement: [for your reference only]

SQL> select substr( the_string
, decode( level, 1, 1, instr(the_string,',',1,level-1)+1)
, decode( instr(the_string,',',1,level), 0, length(the_string), instr(the_string,',',1,level) - decode( level, 1, 0, instr(the_string,',',1,level-1))-1)
) the_value
from ( select (select item1||','||item2||','|| item3 from item_table where sno=1) ITEMS
from DUAL)
connect by level <= length(the_string)-length(replace(the_string,','))+1

View 11 Replies View Related

SQL & PL/SQL :: How To Convert Columns Values Into Row In Oracle

Feb 20, 2012

I have a normal table like step 1 and I want to get a output like step 2. get the output from Columns to row in simple sql.

Step 1)

Table Name: Rat_table
------------------------------------------
Column : year and rating_name
year rating_name
------ -----------
2011 'Sucessful'
2010 'Not Rated'

Step 2)

Table Name: Rat_table
------------------------------------------
rating_name 'Sucessful' 'Not Rated'
year 2011 2010

View 14 Replies View Related

Storing The Values Of Dynamic Columns In Oracle

Nov 18, 2012

I have a requirement to execute the query only once for all the columns mapped to a particular table. Lets take an example..

table t1 has the following columns mapping.

col1 col2 col3 col4
variable 1 table abc col1_abc ----
variable 2 table abc col2_abc ----
variable 3 table xyz col1_xyz ----
variable 4 table xyz col2_xyz ----
variable 5 table xyz col3_xyz ----
variable 6 table xyz col4_xyz ----

In col4 , we have to update the values of col3 (column) ... it is very easy to update the values by executing the query for each column but my requirement is ... I want to execute the query once for a table ( example select col1_abc,col2_abc from table_abc ... this will return always a single value after adding other conditions in where clause) and why I want to do this? .. because there can be multiple (might be 30 variables values)
from a single table.

I have converted the columns into rows by using listagg function now I have lets a dynamic query ( for the above example).

for i in 1.2
loop

l_query := ' select col1_abc, col2_abc from table_abc';
open c1 for l_query;

end loop;

my problem is : how to hold these values and update the above table?

View 2 Replies View Related

SQL & PL/SQL :: Swap Values Between Columns Across Tables In Database

Jan 28, 2011

I've recieved a recent request wherein the requirement is to swap values between columns across multiple tables in a database.Following is a visual sample of what needs to be done.

Before Swaping:

Columns Values
TABLE1.IDENTIFIER MRN123
TABLE1.MEDICARENO CHI1234567
TABLE2.MRN MRN123
TABLE3.MRN MRN123
OTHERTABLE.MRN MRN123

After Swapping:

Columns Values
TABLE1.IDENTIFIER CHI1234567
TABLE1.MEDICARENO MRN123
TABLE2.MRN CHI1234567
TABLE3.MRN CHI1234567
OTHERTABLE.MRN CHI1234567

View 12 Replies View Related

Forms :: Dynamically Passing The Columns Values?

Aug 5, 2013

I am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string

M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';

Now trying to execute it. using below

M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;

Now what i want is M_STR vairable to executed as

SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'

instead of

SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'

other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e

M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';

View 9 Replies View Related

Forms :: Copy Values Of Two Columns Of First Data Block To Other?

Mar 7, 2012

I am working on a form having three data blocks
Travel_Inconvineance (Master)
TI_Card_Info (Detail)
TI_Limit_Indemnity (Detail)

On working TI_Card_Info i have 2 columns Plastic_Card_Id and Plastic_Card_Type which i want to be paste on TI_limit_Indemnity data block as i enter in TI_Card_Info.

View 1 Replies View Related

PL/SQL :: Dynamically Assign Values To Different ROWTYPE-Columns By Ruletable

Jul 31, 2013

how to assign values from a "rule table" to a rowtype-variable.  The ruletable contains values for different columns in different tables.Now i need to assign those given values for given columns out of that rule table to the equivalent column in a rowtype-variable. 

    CREATE TABLE TBRULES
        (
            TABLE     VARCHAR2(50 BYTE)
        ,    COLUMN    VARCHAR2(50 BYTE)
        ,    VALUE     VARCHAR2(200 BYTE)
        );
  [code].....

View 15 Replies View Related

JDeveloper, Java & XML :: Get Values With Two Columns And Pass It To Informatica To Frame

Aug 29, 2011

I have a scenario to frame an xml as below, below i have given a sample data

Create table xml_type
(msg varchar2(1000),
desp varchar2(1000),
val number) ;

[Code]....

Actually i need the output as below, i tried and succeeded doing it in Procedure , but they are asking me to do it in SQL query. How to get from an sql query.

<DETAIL>
<ERROR> 'Invalid Name ID' </ERROR>
<ID> 'Name ID' </ID>
<VALUE> 6 </VALUE>
<VALUE> 7 </VALUE>
<VALUE> 8 </VALUE>
</DETAIL>

[Code]...

Or i was planning to get the values as below with two columns and pass it to informatica to frame the XML.

Col1 Col2
Invalid Name Id Error
Name ID ID
6 Value
7 Value
8 Value
Invalid FAQ ID Error
FAQ Id ID
9 Value
10 Value

Let me know the best option and how to do this task.

View 1 Replies View Related

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

Feb 17, 2011

My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.

SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......

What will be the case when I need to concatenate for more number of values.

like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.

View 10 Replies View Related

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

Oct 28, 2013

I am searching the simplest way for ad hoc MINUS.I do:

SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....

and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?

View 6 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z

Requirement :

From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this

-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.

View 3 Replies View Related

Server Utilities :: Import A Table With Table Already Present With New Columns

Mar 31, 2010

I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.

TABLE_EXISTS_ACTION=TRUNCATE
The new table
SQL> desc "TESTINVENTORY"."TTRANSACTION"
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TRANSACTIONIDNOT NULL CHAR(26)
BRANCHCODE NOT NULL CHAR(3)
EXTERNALSYSTEM NOT NULL CHAR(3)
EXTRACTSYSTEM NOT NULL CHAR(3)
OWNERBRANCHCODE NOT NULL CHAR(3)
TRADEREFERENCE NOT NULL CHAR(20)
[code]...

It giving error while doing an import.

View 4 Replies View Related

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

All Columns Of A Table Are Part Of Primary Key Column Of That Table?

Feb 22, 2011

if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).

should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.

there is a drawback if a most of the tables of a database contain composite primary keys?

View 3 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

Feb 6, 2011

I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:

Query 1

Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)

Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)

When i execute these two above queries, it keeps executing indefinitely.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

Forms :: Retrieving Values From Mater Table In Child Table?

May 21, 2011

I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.

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

Append Columns From One Table To Another

Nov 14, 2011

Perhaps this is a common request : I have 2 tables:

Table A
-------
ID Value
1 a
2 b
3 c

Table B
-------
ID AnotherValue
1 x
2 y

I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:

Table A

ID Value AnotherValue
1 a x
2 b y
3 c (null)

)

I would rather stay away from the standard update statement since it takes far to long and I'd prefer not to use create table as I don't want to duplicate any data...is this possible to do ? (e.g: just insert the columns into this table ?) - or if it's possible the performance overhead just wouldn't make it worth it ?

View 4 Replies View Related

Joining Two Columns In Same Table?

Jan 30, 2007

We have a table for reports. If user A submits a report ...and say the sequence # is 242. When this report goes to Admin ...he submits this request then in same table we add another row with say sequence # 245. THEN we update column called 'Asctd ID' for 242 and add 245 in there. and then update Asctd ID for 245 and add 242 in there.

(This table has many fields, one of which is report Name field)

Now i am running a query like this...

SELECT b.JOB_ID, a.DESC, TO_CHAR(a.CREATE_DATE,'MM/DD/YYYY'),
DECODE(a.DLVRY_TYPE,'','PDF',a.DLVRY_TYPE), DECODE(a.FLG,'','N/A',
a.FLG), ((TO_DATE(a.CREATE_DATE,'DD-MON-YY')) + 21) -
TO_DATE(SYSDATE, 'DD-MON-YY'), c.STATUS_DESC, a.SIZE_NUM,
b.PRVDR_ID, a.asctd_id, d.NM
FROM REQUEST_DIM a, PROVIDER_DIM b, STATUS_DIM c, DIM d
WHERE a.FLAG = 'P' and RTRIM(a.RPT_RQST_USER_ID) = 'TEST02' AND a.RPT_RQST_TYPE = 'D'
AND a.RPT_RQST_ACTIVE_IND = 'A' and a.asctd_id is not null and a.RPT_RQST_ID = b.RPT_RQST_ID
and b.JOB_ID = c.JOB_ID and b.PRVDR_ID = d.PRVDR_ID
AND c.CREATE_DT = (SELECT MAX(d.CREATE_DT)
FROM STATUS_DIM d
WHERE d.RPT_RQST_ID = b.RPT_RQST_ID and d.JOB_ID = b.JOB_ID )
ORDER BY a.RPT_RQST_ID, a.CREATE_DATE

Now this query is run by the admin (job 245) ...it returns a bunch of stuff and also the name of the report that the admin gave this. But when admin sees this we want to be able to displace the report name that user A gave it (asctd ID 242). so the row 245 HAS a asctd ID 242. and there is a row 242 from which we can get the name easily. But i dont know if this is possible in ONE QUERY??

View 2 Replies View Related

Search All Columns In A Table

Jan 15, 2009

I have an application which deploys the data to Oracle database. It has more than 25 tables and many columns. It does not have any document explaining the deployment, so I am kind of doing reverse engineering here.

I need a script which will fetch the column name or at least table names which will match with either some string or number? I found few examples on net to find out number. But I am struggling to make it work for string.

I can not work on stored proc, as I do not have access to create that on server. So, any script will work.

View 3 Replies View Related

SQL & PL/SQL :: Print Columns Of A Table

Jul 5, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
BEGIN
JOBSFILE := UTL_FILE.FOPEN('FILES','JOBS.TXT','W');
UTL_FILE.PUT_LINE(JOBSFILE,'Action = insert');
FOR REC IN JOBSCUR
[code]....

is there anyway to print the values of full table .i have used all column names to print .

View 6 Replies View Related

SQL & PL/SQL :: IOT Table Can Have More Than One Non-PK Indexed Columns

Jul 27, 2011

IOT Table can have more than one non-PK indexed columns.

View 6 Replies View Related







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