SQL & PL/SQL :: Compare Columns Of Two Table?
Apr 5, 2013
I need to compare columns of two tables in oracle 10g. If columns of both tables match with each other, then i need to create new table dynamically with matched column name and datatype. For example, table1 contains name, ID, Phone_no and address table2 contains name, Id, address, area and pincode. now , i need to create table3 which will contains name, ID,address, Phone_no, area and pincode as columns( I mean matched columns should not be repeated in table3). how to do this..
View 17 Replies
ADVERTISEMENT
Apr 29, 2011
I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.
Col1Col2
1106011060
1106011640
1106011142
1106011095
1106013029
1106014058
I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b
where a.Col1 = 11060
and a.Col1=b.Col1
and a.Col2 <> b.Col2
order by Col1, Col2
[code]....
HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.
View 1 Replies
View Related
Oct 8, 2010
I need to compare records in the two columns (varchar). I need to find almost the same names from that columns.
For instance:
'MAPE STUD' <-> MAPE STUDY
And I should see that these records are the same because different is only in the one word.
View 31 Replies
View Related
Oct 25, 2010
I have to compare 2 tables on a columns's value.The 2 tables have same column as Regn_no.I just need to have the result in True Or False from a Query if the particular value of Regn_no is found in both the tables.
Temporarily I using a bit different and not good query which is as follows:
select count(*) from tab1 where regn_no in (select regn_no from tab2) and regn_no = 'UP78AN7890';
View 2 Replies
View Related
Oct 19, 2010
My tables looks like this:
Desc Table A (account)
Account1_id
Account2_id
name,
empid
Table B (Bill )
BillNo
Advertiserid
agencyid
total vvalue
I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.
In other words my output should be like
Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....
objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .
I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:
select.......from a,b
where b.advertiser_id = a.account1_id and b.agencyid = b.account2id
Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.
View 3 Replies
View Related
Jul 11, 2011
I want to compare two tables column by column and find out which columns are matching and inserting matching columns value into another table.
View 1 Replies
View Related
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
Mar 5, 2010
How to identify the columns which are different between two oracle tables. I have nearly 30 columns in each table comprising of million rows
View 11 Replies
View Related
Mar 12, 2012
create type nesttype as table of clob;
create table emp
(empno number,
ename varchar2(1000),
language_known nesttype
)
I want to check whether language is already there in database or not.
i have written the below query
select * from emp where language_known =nesttype('english','hindi');
i am getting the below error
SQL Error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause: Comparison of nested table or VARRAY or LOB attributes of an
object type was attempted in the absence of a MAP or ORDER
method.
*Action: define a MAP or ORDER method for the object type.
How to compare data in nested table
View 16 Replies
View Related
Jan 13, 2009
I am trying to compare records in a table in both Oracle and MSSQL database against a single standard dataset in my test case. However, I'm getting different results for each when I sort using a "order by".
select COL1 from TABLE1 order by COL1
In MSSQL, I get:
COL1
=====
A
A.
A++
A++.
In ORACLE, I get:
COL1
=====
A
A++
A++.
A.
I mean, oracle result some what makes sense, because "." has bigger ascii value than "+". But is there anyway to make the ORACLE sort order look exactly like MSSQL result?
View 1 Replies
View Related
Oct 1, 2013
create table a(sourcerow number(2), test_level number(2), dpn varchar2(1), qty number(5)); T
he insert scripts are as follows:
insert into a values(1,3,'Y',5); insert into a values(2,2,'Y',4); insert into a values(3,3,'N',3); insert into a values(4,4,'Y',3); insert into a values(5,1,'N',6); insert into a values(6,2,'N',5); insert into a values(7,2,'Y',4); insert into a values(8,3,'N',2); insert into a values(9,4,'Y',2); insert into a values(10,1,'Y',3); . SQL>select * from v$version; Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
My logic should have the output as follows without 5th column: sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has
dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.
It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent.
View 5 Replies
View Related
Jul 10, 2012
Suppose you have the below table, same ID's occur for same month as well as different month
ID Month Value
--------------------------------------------------------------
226220 201203 100
1660 201204 200
26739 201204 1010
7750 201205 31.1
I need a query to determine the below laid result
ID Month Prior_month_value Prior_Month Value
----------------------------------------------------------------------------
1234 201203 10 201201 100
3456 201206 56.1 201204 78
View 10 Replies
View Related
Jun 1, 2010
1) how to to compare sybase table data with oracle database table data?
2) how to connect sybase database from oracle database?
View 5 Replies
View Related
Jul 23, 2012
I want to compare two tables , and delete the common rows from the first table
Here is what i have done :
Create table test1(Test1C1 Number,
Test1C2 varchar2(50));
Create table test2(Test2C1 Number,
Test2C2 varchar2(50));
Insert into test1 values(1,'testdata1');
Insert into test1 values(2,'testdata2');
Insert into test1 values(3,'testdata3');
[code].......
it deletes all the records from Table test1. What should I modify here ? or should I write a different query ?
The desired contents in table test1 will be
2 testdata2
4 testdata4
6 testdata6
8 testdata8
10 testdata10
View 5 Replies
View Related
Aug 28, 2013
How can I compare two rows from the same table and show the field and the difference?
Example:
select ename, job, mgr, hiredate, sal, deptno
from EMP t
where t.empno = 7782
or t.empno = 7788
ENAME JOB MGR HIREDATE SAL DEPTNO
1 CLARK MANAGER 7839 6/9/1981 2450.00 10
2 SCOTT ANALYST 7566 6/9/1981 3000.00 20
My report comparing fields should be:
FIELD BASE COMPARE DIFFERENCE
ENAME CLARK SCOTT --
JOB MANAGER ANALYST --
MGR 7839 7566 --
SAL 2450 3000 550 --(Show difference only for numbers)
DEPTNO 10 20 --
View 9 Replies
View Related
Dec 26, 2011
the following proble.The emp table is having 14 records.
SELECT * FROM emp ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
[code]...
The emp table is having 10 records.
SELECT * FROM emp_10 ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020
[code]...
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4
Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13
Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
View 10 Replies
View Related
Jan 23, 2013
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
View 5 Replies
View Related
Mar 28, 2013
I use APEX 4.2.0. In Tabular form, I have column of Select list type. This list has too many values and the end user must choose all these values along the tabular form rows. In other words, If the LOV consists of X, Y , Z, The End user should add three rows and choose a unique value for each row.
The problem is : This LOV is supposed to have too many values. What comes to mind is:
1- Whenever the end user picks a value, this value should disappear from the LOV of the second row ? But I have no clue how to do it?
OR
2- Whenever the end use picks a value, I should notify him/her about the remaining values that he should pick . But I have no clue how to do it ?
At the end the purpose is to make the user aware that he still has some rows to add ( values to choose ... )
I simplified the issue in simple example here:
[URL]......
workspace: somefeto
user: test
pwd: test
View 0 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
View Related
