I have two tables one source table and one destination . Column names and data types of both table are same.
source table (source)
ID NAME
1 aa
2 bb
3 cc
. ...
. ..
. ..
destination Table(dest)
ID NAME
1 aa
2 bb
I need destination table like
destination table
ID NAME
1 aa
2 bb
3 cc
. .....
. ....
I want to remove repeated rows
Consider the following tables
MANAGERID
101
102
103
104
MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27
write sql query to get the following output without using minus,union,intersect.
MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27
how to remove duplicate rows from table?
View 6 Replies View RelatedI have a table table1 with 2 crore records.
select * from table1;
id code updateddatetime
------------------------------------
1 10001 2011-10-21 15:31:21.390
2 10001 2011-10-21 15:31:22.390
3 10001 2011-10-21 15:31:21.390
4 10001 2011-10-21 15:31:22.390
5 10002 2011-10-21 15:31:22.390
I want to delete records like id 2 which has odd updated time which is more than id 3 updated time.
is there any alternatives without using cursors as it taking so much time to process.
I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.
my requirement in view like
And..........
And ((select count(*) from table A where conditions)=1 )then name is null
AND
in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.
I have a sql like
select TRIM(column_name)
from user_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_VL' AND COLUMN_NAME IN ('DESCRIPTION ');
[Code]....
Like this i have many rows. What clause other than trim i should apply to no cosider the spaces?
I have a large table with 450 column and we are only using nearly 170 columns and our BD block size is 8k.The DBA informed that there is an row chaining happening in the Database.My question is if we have data available in 170 column .why row chaining is happening.
The DBA informed us to remove the unnecessary columns .. Does those empty columns have any impact on the chaining.If we increase the size of DB block to 32k . does it will resolve the issue.
The following query take lot of time when exectued, even after I drop the indexes, is there a better way to write the following query?
DELETE from pwr_part
where ft_src_ref_id in (select ft_src_ref_id
from pwr_purge_ft);
--Table:pwr_part
--UIP10371 foreign key (FT_SRC_REF_ID, FT_DTL_SEQ)
[Code]...
Explain Plan:
Description Object owner Object name Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 224993 5492829 395483688
HASH JOIN RIGHT SEMI 224993 5492829 395483688
INDEX FAST FULL SCAN PWR_OWNER PWR_PURGE_FT_PK 43102 23803770 142822620
PARTITION HASH ALL 60942 27156200 1792309200
TABLE ACCESS FULL PWR_OWNER PWR_PART 60942 27156200 1792309200
I have a simple column in a table which store a description. This can include line breaks so when you select from it, it looks similar to below
'Part number: X
Batch: Y
Size: Z'
I would like to be able to create a view (so not having any impact on the original data) to return it as below
'Part number: X Batch: Y Size: Z'
I have a query in oracle report in which i am getting this output.Manager Arnav have 2 employees Inder and kaushal whose salary is 10000 and 20000 respectively,
And another manager is Anjali whose employees are Kavya and inder whose salary is 40000 and 10000 respectively .as Inder is repeated I want the salary become 0 in place of 10000 second time.I am in dilemma,What should i do ,if i want to change 10000 to 0
Manager employee salary
Arnav Tiwari Inder 10000
kaushal 20000
Anjali Kavya 40000
Inder 10000[/b]
What should i do in the formula of salary.according to employee name .means if Name exists already then salary value should be 0 and if it comes for the 1st time then its actual value i.e 10000 should be printed.
in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?
I have fifty rows inside the table
select * from student where rownum between 0 and 4 order by stuid ->it work
select * from student where rownum between 5 and 9 order by stuid -> did not work
what is the correct way to do it ?
I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.
I have a table with the following column and data is like this.
SQL>CREATE TABLE test (
column1 varchar2(50));
SQL>INSERT INTO test VALUES('ABC XYZ');
SQL>INSERT INTO test VALUES('MNO PQR');
SQL>INSERT INTO test VALUES('ABCD ABC');
SQL>INSERT INTO test VALUES('PQR MNOP');
[code]....
Is there any function to count number of specific character inside string. For example :
my string is 'ABCEF AYZA'
I want to calculate how many 'A' exist in my string. result should be = 3
I am using the SQL-Developer to access and manipulate a database. I am not very sure about the format of the database (I'm new to databases), but I had to setup the TNS-folder.
Anyway, I guess the problem is the same for any database.
I am having a table with the BOM (bill of material) positions of certain articles and I want to change the BOM quantities of some of the articles. What happens is that I can only change some of the rows. For other rows I get the message like (it is in German, so I try to translate it):
"data was commited in another/the same session already. row cannot be updated"
This error message looks like there is somebody else locked on the database and manipulating it, correct? Is that possible to see somewhere which processes/people are currently accessing to the database?
I saw that there is one process/another database, which is having the authorization to access to the database. But where can I check if this process is accessing to the database?
BTW: I used to do this process before, and it worked. I had been able to manipulate arbitrary entries on the database. I guess that the process or the person, mentioned above, hasn't been accessing to the database at that time.
I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).
I am using SQL 11g Express.
The report would look something like this:
{OrderDate PartID PartDesc NumOfParts(Total for that day)
10-24-2011 2001 12" X 12" Solid Shelf 108
10-24-2011 2003 12" X 24" Solid Shelf 32
10-24-2011 3001 96" Side Panel 50
[code].......
My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.
Below I have what the calculations should look like
SKU PartID Total Parts Needed by SKU
DVCK1212 1001 42 (7 orders * 6 shelves)
DVCK1812 1002 0 (NONE)
DVCK2412 1003 42 (7 orders * 6 shelves)
DVCK3012 1004 78 (13 orders * 6 shelves)
[code].......
Here's my script:
/**PRODUCT**/
CREATE table Product
(
SKU VARCHAR2(10) NOT NULL,
ProdDesc VARCHAR2(50) NOT NULL,
Price NUMBER(5,2),
PRIMARY KEY(SKU)
);
[code].......
Query 1
SELECT ProductPart.NumOfParts
FROM ProductPart,
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder
[code].......
Result: I am only getting a total of 2 parts
Query 2
SELECT ProductPart.Qty,
ProductPart.PartID,
ProductPart.PartDesc
(SELECT COUNT(CustOrder.SKU)
FROM CustOrder
GROUP BY CustomerOrder.SKU)TotalProducts,
[code].......
ORA-00936: missing expression
Query 3
SELECT Part.PartDesc.
ProductPart.NumOfParts
FROM ProductPart, Part
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder
[code].......
ORA-00933: SQL command not properly ended
Query 4
SELECT o.OrderDate AS "Date Ordered",
o.OrderDate + 5 AS "Date Due",
pp.PartID AS "Part No.",
pp.NumOfParts * COUNT(o.SKU) AS "Qty"
FROM CustOrder o
[code].......
ORA-00934: group function is not allowed here
i nee a function which accepts 4 digit number and in four digit number the number should not be repeated. i want all the number in the output.
ex:1234
2367
1262(is not valid)
In my report I'm using inner join in queries .I got correct record but itz repeated.How to deduct that repeat values?
this is my query:
SELECT f.feeid,
f.studentid,
( s.first_name + last_name )AS studentname,
b.branchname,
c.coursename,
y.yearname,
[Code].....
My Table structure is
SL# Start_ts
1 7/31/2012 3:53:42.864070 AM
2 7/26/2012 2:13:58.280928 PM
3 7/25/2012 2:13:41.692569 PM
4 7/21/2012 2:13:26.821070 PM
5 7/18/2012 2:13:07.827278 PM
6 7/13/2012 2:05:35.023520 PM
Question
How to select last 10 days rows only (from sysdate)
Error
1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)
2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected
[Code]...
I have been trying to find out how to write the following query:
Suppose the following table:
ID seq
24 1
24 2
24 3
67 1
67 2
67 3
67 4
67 5
13 1
13 2
I would like to retrieve the rows for every different ID with its max value. For one ID that would be:
select * from TABLE where seq in(
select max(seq) from TABLE where id=24)
How can i do this for all the rows??
When I use the below code in my perl script (it is oracle database):
$query = "select a.sub_id, b.name from subscribertable a, invitationBin b where a.subscriberid=b.subscriberid";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
everything is ok... and when I try to add a third table...
$query = "select a.sub_id, b.name, c.phone from subscribertable a, invitationBin b, personalDet c where a.subscriberid=b.subscriberid and b.subIdx=c.subIdx";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
this fails... it seems like it doesnt let me add 3 tables in the SELECT query through perl script. The strange is that when I test this query with Oracle SQL Developer, it works fine...!
i work on oracle 8i with around 950 tables in my database. when i export or import it gives me the no of rows exported / imported from each table. is it possible to take a print out of the no of rows in each table through a single query .
select count(*) from each table takes a long time , since there are 950 tables.
We have two tables, TableA and TableB that contain list of accounts and balances.The requirement is to compare the balances of accounts in both the tables, and if there is a difference, then record that difference with account number in another table.
Both TableA and TableB contain more than 10 million rows.What is the best way to do this task in PL/SQL? A join on TableA and TableB to know the differences has become very slow due to large volume.
I have a table as shown below,
SQL> select * from Main_Table;
AA
--------------------
SUB1
SUB2
Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.
Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like
create or replace view sample
as
select * from SUB1 union all
select * from SUB2
How can this be achived. I tried as shown below:
spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off
i will end up in a union all "extra" , if i do like this.
how can achieve this ?..
I have table with the values as below.
C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ
I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ
I could do it using the below query using union with the rownum.
select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3
Is there any other way of displaying using a single sql query.
I have a query that seems to repeatedly call an index scan on a table for reasons I'm not sure about. Why it would be doing the index scan on totaldwellingarea in the dimensions table (DIMEN_PID_TDWELLAREA) repeatedly? This only seems to happen when I put on the range clause d.totaldwellingarea between scr.lowvalue and scr.highvalue.
I am using Oracle version 9.2.0.3.
select d.propertyid,d.totaldwellingarea, e.size_, scr.size_
from eqid e, dimensions d, brt_eval.size_code_ranges scr
where e.style not in ('1','A','G','L') and e.size_ = '0'
and d.propertyid = e.propertyid and e.style = scr.style and d.totaldwellingarea between scr.lowvalue and scr.highvalue;
CREATE
TABLE "BRT_ADMIN"."EQID"
(
"PROPERTYID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"EQID" CHAR(10 BYTE),
"ZONE_" CHAR(1 BYTE),
"AREA" CHAR(1 BYTE),
[code].....
I have 3 tables that I want to select all rows from and then order by the timestamp of when the row was inserted.
View 7 Replies View Relatedi want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work
select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;
this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records
I would like to know how to increase the result set of a 'Select' statement? I did a 'Select' that should have returned 36,000 rows and got only 5000 rows. What access level do I need to change this and what do I need to change? I am trying to do a migration of data from a delimited file to a table in Oracle. Yet, the data has to be filtered out prior to loading to the table?
SQLSELECT MIN(major_zipcode)
FROM TEMP WHERE MAJOR_CITIES IN (select distinct major_cities from temp);
My table has the follwoing 3 columns (in addition to others)
Col Name = active ; type=number ; values=1 (true) or 0 (false)
col name start_date ; type=date; format=dd-mmm-yy
col name end_date ; type=date; format=dd-mmm-yy
I need to select all rows where all active=1, start_date=<today and end_date=>today
my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');
However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08
This is not correct. Due to the end_date this row should not be part of the results.
