SQL & PL/SQL :: Getting Rows Which Are Not Duplicated
Aug 9, 2010I'm having table like this:
deptno
10
10
20
20
30
40
my query isI need the records which are not duplicated.
my output must have the records 30 and 40 only.
ADVERTISEMENT
Server Utilities :: Why Duplicated Rows When A PK Is Present Running Sqlldr
Dec 10, 2010Why duplicated rows when a PK is present running Sqlldr?
View 1 Replies View RelatedSQL & PL/SQL :: Delete Duplicated Record
May 21, 2010I want to delete the duplicated record using following methods, it delete the records if all the record are matched according to condition, but if all records are not matched then it display error messages.
SQL> SELECT * FROM XYZ;
NAME FNAME ADDRESS JOB
-------------- -------------------- -------------------- -----------------------------------------
Bilal Khan Wali your rehman name district abbottabad student
Bilal Khan Wali your rehman name district abbottaba student
Bilal Kh Wali your rehma name district abbotta studen
Bilal K Wali your rehm name district abbotta studen
Bilal Khan Wali your rehman name istrict abbottaba tudent
lal Khan i your rehman name strict abbottaba tudent
[code]....
PL/SQL :: How To Add Asterisks To Row When One Item In Row Is Duplicated / Show One Row
Sep 7, 2013I totally realize that but here is what im trying to accomplish. I have a table with one column that repeats.the table looks like this.
create table testCheck (check_num varchar2(20), amount number(17,2), invh_Code varchar2(20))
and it has data like so
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('002', '100', '1234'
I would like to write a query that would give me the distinct check_num's but put an asterisk on the items that are actually duplicated.so in other words expected result in this case would look like this.
"CHECK_NUM" "AMOUNT" "INVH_CODE"
"001*" 50 "1123"
"002" 100 "1234"
I've been able to get as far as getting the unique values but what i cant do is figure out for the life of me how to put the asterisk in there.here is my query.
with Checkquery as (
SELECT count(*) over (partition by CHECK_NUM order by CHECK_NUM ROWS UNBOUNDED PRECEDING ) thiscount, CHECK_NUM, AMOUNT, INVH_CODE
FROM TESTCHECK
group by
check_num,
AMOUNT,
INVH_CODE)
select check_num, amount,invh_Code from Checkquery where thiscount ='1';
I simply want to identify the dups put an asterisk on the record and then only select one of the records.
SQL & PL/SQL :: Duplicated Entries In A Valid Unique Index?
May 26, 2010I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.
Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.
And it is still in VALID state
SQL> set autotrace on
SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);
select ri from scratch1_p where scnum='444393975';
RI SCNUM
---------- ----------
536955574 444393975
[code].....
PL/SQL :: Foreign Key Can Be Implemented In One Table And Cannot Be Duplicated To Work
Oct 17, 2013I have now created my tables and now I want to populate them with my data but I get errors below is my full sql command. From last time I learned that one foreign key can be implemented in one table and cannot be duplicated to work around this I changed my foreign key as you can see to "fkone" . but why am I getting errors when trying to add information I mean I have not gotten errors in the first two tables so what am I "doing wrong/ not understanding"
CREATE TABLE CUSTOMER (CUSTOMERNAME varchar (25),STREET varchar (25),CUSTOMERCITY varchar (25),CONSTRAINT CUSTOMER_pk PRIMARY KEY (CUSTOMERNAME)); CREATE TABLE DEPOSIT (CUSTOMERNAME varchar (25),BRANCHNAME varchar (25),ACCOUNTNUMBER int,BALANCE decimal (7,2),CONSTRAINT DEPOSIT_pk PRIMARY KEY (ACCOUNTNUMBER)); CREATE TABLE LOAN(CUSTOMERNAME varchar (25),BRANCHNAME varchar (25),LOANNUMBER int,AMOUNT decimal (7,2),CONSTRAINT LOAN_pk PRIMARY KEY (LOANNUMBER)); CREATE TABLE BRANCH (BRANCHNAME varchar (25),BRANCHCITY varchar (25),ASSETS decimal (7,2) ,CONSTRAINT BRANCH_pk PRIMARY KEY (BRANCHNAME)); ALTER TABLE DEPOSITadd
[code]....
Application Express :: Report Columns Duplicated When Region Is Copied
May 15, 2013Re: Report Columns duplicated when region is copied.
When I copy a region which I have created via master-detail form, the report columns are duplicated. I'm actually copying the whole page but I have had other cases where I just copying the desired region and the columns were duplicated there as well. And, of course, there is no way that I know to delete columns from the form. But I doubt because -- in at least some cases I've tried -- the columns seems to be "linked" in that a change to one copy of the column was duplicated in the other when changes were applied.
In the example (below), there are three regions on the page and the problem occurs in region 2. (I think I will likely delete the first region.) It's been agonizing trying to get the report of region 2 and the tabular formish edits of region 3 on the same page. I made this in Oracle 11.2 in our APEX 4.1 and imported it to 4.2. You can see it here:
WS apex_examples_01
demo
demo
Appl. 991204
Page 57 is the original page created in 4.1 and imported
Page 58 is the page copied from 57 under 4.1 and imported
Page 59 is the page copied from 57 under 4.2 (Same result as in 4.1.)
If you want to try the page, select DSD01 from the DSD LOV, click "Query Process" and then click the edit icon. Ignore the region 1 errors -- I'll probably delete this region. The data is region 3 is for Run Date 12-May-13.
Is there some setting that allows this region to be copied without duplicating columns?
P.S. I think I know why it happens: APEX hates me!
SQL & PL/SQL :: Compare Current Rows With Previous Rows
Apr 12, 2013The Table having 3 columns
col0col1 , col2
P112
P112
P102
P113
P115
I want to retrieve the changes rows only.
Output like
col0col1 , col2
P112
P102
P113
P115
PL/SQL :: Select 4 Rows And Then Next 4 Rows?
May 27, 2013in 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 ?
SQL & PL/SQL :: Subdivide Rows Into Rows
Apr 10, 2013drop table test
/
create table test
(
lib varchar2(100)
)
/
insert into test values ('111/aaa/bbb/ccc');
insert into test values ('222/aaa/bbb/ccc');
insert into test values ('333+444/aaa/bbb/ccc');
insert into test values ('333/aaa/bbb/ccc');
insert into test values ('222+333+444/aaa/bbb/ccc');
insert into test values ('222+333+444+555/aaa/bbb/ccc');
I want to get this :
lib count
--------------- -----
111/aaa/bbb/ccc 1
222/aaa/bbb/ccc 3
333/aaa/bbb/ccc 4
444/aaa/bbb/ccc 3
555/aaa/bbb/ccc 1
Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?
Dec 14, 2007I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.
View 4 Replies View RelatedPL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows
Aug 22, 2012what 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.
SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows
Oct 6, 2010I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query
MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762
and I need to display this as
COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762
SQL & PL/SQL :: Put Two Rows Into One Row?
Sep 14, 2013We are using PL/SQL Release 11.2.0.2 .I would like to pull a query with each student each day an attendance record.Our database setup an AM and PM Period for all elementary students. I will pull if they absent both periods(AM, PM), then count that as one day absent.The hard part is I need to put the AM absent code and PM absent code - which is basically to put two records for each student's AM and PM absent code into one row.
Below is the query I use, but it violates the key of database, for PK is studentid+ attendance date. My query result turns out for some students they have different attendance code in AM vs PM, there are two records returned.
SELECT DISTINCT TO_CHAR(S.Student_Number) ,
TO_CHAR(Am.Schoolid) ,
Sps_School_Year('C') ,
TO_CHAR(Am.Att_Date,'MM/DD/YYYY') ,
(
[code]....
SQL & PL/SQL :: Best Way To Get Several Rows From SCD For Each Key
Nov 20, 2012We have large (millions of records) Slow changind dimension (SCD) type 2 (see "Creating another dimension record " URL>.....We need to get several rows from this SCD for each key (AGREEMENT_ID) in a SQL query - to join to facts table and get several data points of each agreement (on several different points in time) stored in SCD.Here is SCD table structure:
CREATE TABLE AGREEMENT
(
"AGREEMENT_ID" NUMBER(*,0) NOT NULL ENABLE,
"ACTUAL_DATE" DATE NOT NULL ENABLE,
"ACTUAL_END_DATE" DATE NOT NULL ENABLE,
"OPEN_DATE" DATE NOT NULL ENABLE,
"LIMIT" NUMBER(23,8)
--++ a lot of other fields not needed for this task ....
CONSTRAINT "PK_MD_AGREEMENT" PRIMARY KEY ("AGREEMENT_ID", "ACTUAL_DATE") USING INDEX
)
The 1st simple approach would be to join facts to SCD as many (N) times as many different points of time you need - resulting in N Full Table Scans for SCD:
select ...
from fact,
AGREEMENT agr1,
AGREEMENT agr2,
AGREEMENT agr3
where
fact.AGREEMENT_ID = agr1.AGREEMENT_ID and agr1.open_date between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr2.AGREEMENT_ID and :dateBOP between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr3.AGREEMENT_ID and :dateEOP between actual_date and actual_end_date
2nd approach: 1 Full Table Scan for SCD + group by:
select ...
from fact,
(
Select AGREEMENT_ID,
max(case when open_date between actual_date and actual_end_date then LIMIT end) LIMIT_At_Open_DATE,
max(case when :dateBOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_BeginOfPeriod_DATE,
max(case when :dateEOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_EndOfPeriod_DATE
from agreement
-- ++optionally WHERE for those 3 dates, but possibly with no effect on non-partitioned table? Or WHERE to put less data on MAX() input (3 row for each agreement instead of 4...1000 without WHERE?)
group by AGREEMENT_ID
) agr
where fact.AGREEMENT_ID = agr.AGREEMENT_ID
PL/SQL :: ANY ALL When No Rows
Jun 5, 2013Simple question, Why comparison operator ANY returns FALSE if no rows returned, and why operator ALL return TRUE if no rows returned? I dont know is this some kind of language or math assumption or is this just oracle rule?
View 18 Replies View RelatedConverting 2 Rows Into 1
Aug 16, 2007i have 2 tables
A B
id a.id
name
type
There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:
id male_name female_name
1 paul paula
the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.
Throughput Rows Per Second
Nov 7, 2012I would want to know how can we predict how many rows are fetched per second for a particular query. What are the factors which are responsible for this.hoe does this whole process of fetching records from Source happens. Like when a query is fired how does it try to access the table and fetch records and how what are the factors whichc are responsible for this and how can we predict how many rows can be fetched per sec
View 8 Replies View RelatedSQL & PL/SQL :: How To Get Reduplicate Rows
Jul 12, 2011There are many reduplicate rows in a big table,what is the best way to get the reduplicate rows?
View 5 Replies View RelatedSQL & PL/SQL :: Rows Into Column
Mar 18, 2011I have following table.
CREATE TABLE ORAFAQ
(
DPT NUMBER,
EMP NUMBER,
SAL NUMBER
)
[Code]....
DPTEMPSAL
1110
12100
131000
241500
25100
26500
27100
but i need data like
Dept
1
1 2 3 --3 columns show each employee
10 100 1000 --3 columns show salary below each employee
2
4 5 6 7
1500 100 500 100
SQL & PL/SQL :: How To Commit For Every 500 Rows
Jul 10, 2010How can we commit for every 500 rows in pl/sql block.
begin
for i in 1 to 10000
loop
Insert into t1 values(i);
commit;
end loop;
end;
Here I am commiting after all the rows are inserted ,but i want to commit for every 500 rows are inserted .
SQL & PL/SQL :: All Rows Value To Single Row
Jul 15, 2011i want to all rows value into single rows.
for example;
Input data:
Sec_SSC_ID Column_nameAs of dateOld valu New Value
IBM Mat_dt 10/10/20101/1/2001 1/1/2002
IBM Bid Market 10/10/201075 85
IBM asset_nm 1/1/2011International IBM
MSFT asset_nm 1/2/2011Microsoft Intel
MSFT Bid Market price 1/1/201189 90
Output data
I have attached sample output file with it.
how to get this via sql?
SQL & PL/SQL :: Rows From Each Table In DB
May 5, 2011Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding
system tables ..
how can i write query for this.
SQL & PL/SQL :: Multiple Rows Into One Row
Apr 10, 2011tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.
Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.
CREATE TABLE Person_Lang
(
Person_ID NUMBER NOT NULL,
Language_ID NUMBER NOT NULL,
Contact_Name VARCHAR2(255 CHAR),
Main_Phone VARCHAR2(255 CHAR),
Secondary_Phone VARCHAR2(255 CHAR),
[Code]...
Data Returned from Select is:
1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown
Ideally what is required back is:
1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown
Forms :: How To Sum Rows In 6i
Mar 10, 2012i want to sum different rows and want get wum in nest column.
for example i have fee column and there is some different student fee in specific month for example(jan).i want to sum this fee against the month of Jan.
SQL & PL/SQL :: Get Previous Value Of Rows?
Dec 29, 2010How to get the previous value of row with calling function to add value in SELECT statement for the row value.
Consider the example Table A1 having column a with values 1,NULL,NULL,NULL
SELECT CASE WHEN a IS NULL THEN (prev_row_value+function_return_Value) ELSE a END as A from A1
And my result-set should be like
a
----------------------
1
1+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Below is sample code but doesn't fulfill my criteria
[code]....
Output is
A A2
---------------------------
1 1
3
3
3
SQL & PL/SQL :: Convert Many Rows To One
Sep 17, 2012I use Oracle 11g and a Middleware which sends the SQL Statements to the database. That's why I can't send you loggings.
However, I' got this SQL:
With CTE
AS
(
select * from
data A2
WHERE
A2.ID ='536031'
AND
A2.AttrID = '6'
)
That code gives me the following result:
ID.........Version....AttrID.......group
536031......3.........6..............test5
536031......3.........6..............test6
What I try to do. I want to get all groups. The Groups should be in one column...the the result must be the following:
ID.........group
536031...test5, test6
In SQL server I always used
SELECT ID,
STUFF((SELECT ',' + [group] FROM CTE WHERE ID = c.ID ORDER BY [Version] FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ID FROM CTE)c
to achieve that. Is there a similar function in oracle like XML PATH? Or another way the get the result?
SQL & PL/SQL :: Cumulative Some Last 5 Rows?
Jun 25, 2013I need to get the sum of next 5 rows that are order by one specific column .
Ex :
DROP TABLE TEST_5;
CREATE TABLE TEST_5 AS
SELECT TO_NUMBER(200 || ROWNUM) YEAR, ROWNUM TOT
FROM ALL_OBJECTS
WHERE ROWNUM < = 15 ;
SQL> select * from test_5 order by year;
YEAR TOT
---------- ----------
2001 1
2002 2
2003 3
2004 4
2005 5
2006 6
2007 7
2008 8
2009 9
20010 10
20011 11
20012 12
20013 13
20014 14
20015 15
15 rows selected
SQL>
Now Expected Results :
SQL> select * from test_5;
YEAR TOT Exp_res
---------- -----------------------
2001 1 1+2+3+4+5 = 15
2002 2 2+3+4+5+6 = 20
2003 3 3+4+5+6+7 = 25
2004 4
2005 5
2006 6
2007 7
2008 8
2009 9
20010 10 10+11+12+13+14
20011 11 11+12+13+14+15
20012 12 12+13+14+15
20013 13 13+14+15
20014 14 14+15
20015 15 15
15 rows selected
SQL>
SQL & PL/SQL :: Update All Rows At Once
Aug 28, 2011I want to update salary column of emp table in a way that every value of salary column be increased by 1000, is this possible I can do this one statement only??
(Just FYI-
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
)
[Code].....
SQL & PL/SQL :: Merging Rows
Oct 17, 2011there are 2 different types of bar-code which get store in single table
table_structure
item_id
group
bar_code
values are like
item_id group bar_code
1 FG 09878
1 FG 81011
i want output like
item_id group bar_code1 bar_code2
1 FG 09878 81011
What should i need to do...?