SQL & PL/SQL :: Distinct And Individual Records In Single Or Minimum Pass(es)
Feb 21, 2012
I have following data
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPE
------------------ ------------------------------ ------------------
AAA/wuAAHAAAW73AAA CON$ TABLE
AAA/wuAAHAAAW73AAB I_COL2 INDEX
AAA/wuAAHAAAW73AAC I_USER# INDEX
AAA/wuAAHAAAW73AAD C_TS# CLUSTER
AAA/wuAAHAAAW73AAE I_OBJ# INDEX
AAA/wuAAHAAAW73AAF I_CON2 INDEX
6 rows selected.
I want it in the following manner
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPEGROUP
------------------ ------------------------------ ---------------------------
AAA/wuAAHAAAW73AAA CON$ TABLE2
AAA/wuAAHAAAW73AAB I_COL2 INDEX1
AAA/wuAAHAAAW73AAC I_USER# INDEX1
AAA/wuAAHAAAW73AAD C_TS# CLUSTER1
AAA/wuAAHAAAW73AAE I_OBJ# INDEX1
AAA/wuAAHAAAW73AAF I_CON2 INDEX1
6 rows selected.
Here the GROUP is changing when the data type is changing and thus for same data type the group shall remain the same As of now this is achieved by - first selecting distinct object_type, then it's mod(rownum,<input variable) and this result of 'mod' is doing the grouping which is the retrieved along with rowid of all individual record
Present query is as following and it is not much efficient
SELECT DO.ROWID RWID, RID
FROM DO,
(
SELECT OT,CASE MOD(ROWNUM,:v) WHEN 0 THEN :v ELSE MOD(ROWNUM,:v) end as RID
FROM(
(SELECT DISTINCT OBJECT_TYPE OT
[code]....
I tried using sequence with cycle but it gave different results. Even I tried following but it did not gave satisfactory results
select d.rowid,d.object_type,x.x1 from do d,(select distinct object_type,mod(rownum,:v) x1 from do where
created>'01-jan-2008')x where d.object_type=x.object_type and created>'01-jan-2008';
In short the query needs Distinct with mod(rownum) and individual records in a single pass The mod(rownum) i.e. group shall change when the object_type changes but then shall remain constant through out the particular object_type.
View 9 Replies
ADVERTISEMENT
May 13, 2013
Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this content is not allowed", without telling where or what it is! So I had to post it in the stack-overflow forum, here is the link: [URL] .........
View 6 Replies
View Related
Feb 23, 2010
create table x (
field1 varchar2(10) );
insert into x values ('001-002');
insert into x value ('0150-0152');
insert into x value ('0100-0101');
create table y (
field varchar2(6));
recortd in table y
001
002
0150
0151
0152
0100
0101
View 15 Replies
View Related
May 9, 2011
I have to write a sub query / build a logic for the below.
There are several accounts which should have a zero balance i.e sum of all the amoutns in that account should be zero. If they are non zero , i have to report which amounts make up non zero balance.
If i have amts as +20 , -20 , -30,-10 i.e the sum is -40 indicating a non zero amount. I need the entire details of the records which makes up non zero sum. So in above case details related to -30 aand -10.
I'm using a sum group clause on several fields at which sum is required to be checked ie. date , account , currency . query that will bring individual records that don't make the sum zero.
Is it possible to write a outer query which will bring individual records which don't sum up to zero.
View 14 Replies
View Related
Mar 18, 2010
the below is the csv file data. 1st value is for transaction id, 2nd one is for order id and 3 rd one is category_id
"1","45678","a"
"2","45478","b"
"2","45278","b"
"3","45678","d"
"4","45278","e"
I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this? In the above case distinct transaction id count is 4
View 15 Replies
View Related
Jul 13, 2010
I have table as below :-
Table ABC(
ID Number,
startDate date,
City varchar2(10)
)
I need to write query which will get me all the CityNames for which there are no Startdate differs,
i.e. To get all the city name records for which all of the Startdate are same across all the records.
I dont want to go after Self Join due to performance issues, do we have any better way?
View 2 Replies
View Related
Sep 25, 2012
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..
How to write a query for this inside a loop.
View 5 Replies
View Related
Dec 23, 2012
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
View 2 Replies
View Related
Jul 20, 2011
In my application (forms6i) initially data will be displayed in a non-database block after a lot of calculations and validations which is time consuming.
Now, if the user want to print it as a report, we store the displayed data in a dummy table and run the report using that dummy table and then delete records from it.
Is it possible to pass the records displayed to the report as a parameter so that I can avoid Unnecessary add/delete records?
View 1 Replies
View Related
May 27, 2013
i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies
View Related
Sep 18, 2013
I have a table as col_1 number,col_2 varchar2(10)
col_1 col_2
1 abcdefghijkl
what i want is i've to split
col_1 col_2
1 abcdefghij
1 kl
View 4 Replies
View Related
Jul 8, 2013
I want to insert multiple records in a single row. Example: I have a below query
select '"'||c1||'","'||c2||'","'||c3||'"'from (select 'ABC' as C1,'ZYX' as C2,'TEST' AS c3from dual unionselect 'A1' as C1,'a2' as C2,'A3' AS c3from dual)
And I want to insert the above 2 records in table T1 as a single row as below:
"A1","a2","A3""ABC","ZYX","TEST" Column1"A1","a2","A3""ABC","ZYX","TEST"
View 6 Replies
View Related
Mar 15, 2011
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
View 1 Replies
View Related
Jul 25, 2013
The problem I am facing analyzing a certain table s trying to get a proper start and end date for a specific field such as TICK_COL, because there are so many other fields are being updated in this table - all using MTC_DATE - this is causing iterations of TICK_COL.
So first step was to just use lead to get the end date for all iterations so I could picture how this might look with a start and end date
CODE A (see below)
FROM_DATE TO_DATE
SKU TICK_COL MTC_DATE LEAD (MTC_DATE)
21524804 RIBG 10101 20080615
21524804 RIBG 20080615 20080625
21524804 RIBG 20080625 20080628
21524804 RIBG 20080628 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20080930
[Code] .......
My first bright idea? I tried using Rank as well, hoping to rank each of this tick_color changes as 1, which works for the exception of when tick_col changes to RIWH or RIGR again.
The ranking function doesn't see the 2nd change to RIWH as entirely unique and assigns it a 2 and 2nd change to RIGR a 3. If I could rank each of those as 1 I could query these results as an in-line view where rank = 1 and do lead to get the start and end date, finished,
CODE B (see below)
21524804 RIBS 20130725 20130725 8
21524804 RIBS 20130327 20130725 7
21524804 RIBS 20130317 20130327 6
21524804 RIBS 20130312 20130317 5
21524804 RIBS 20120813 20130312 4
21524804 RIBS 20100916 20120813 3
21524804 RIBS 20100518 20100916 2
21524804 RIBS 20091120 20100518 1
[Code] ........
I am Expecting to see this below:
21524804 RIBG 10101 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20081031
21524804 RIWH 20081031 20090311
21524804 RIGR 20090311 20091120
21524804 RIBS 20091120 20130725
The code I used to generate the first table was which obviously, does not get me as far as I�d like.
CODE A
SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].......
CODE B
SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].........
View 3 Replies
View Related
Dec 18, 2008
I am using toad to output the details of the database. (I need these details in a format which i can then import into excel) However, When I run sporadic individual records through my query, the results come back correct and as expected. But when I run the full data set through my query a lot of the records have different values than what they do when run individually!
Ive spent all day on this script and its really a hack of various other cursors and scripts brought together. Its driving me mad as my code doesn't seem to be wrong (since the results are correct when a single record is run) yet there must be something wrong as my full data set does not return correctly!
View 2 Replies
View Related
May 30, 2010
I have a Master block and a Detail Block. They are related using two columns, Document_ID and Page_no. I want to display all the records in master table with corresponding detail records beside them in single line. i.e., as shown below:
MasterItem1 DetailItem1 DetailItem2 DetailItem3
I created the relation between them and executed query. When I execute query in the form, I can find that all the Master Items are displayed in vertical records, but i can only see one record of detail fields. They are displayed as shown below:
mitem1-value1 ditem1value1 ditem1value2 ditem1value3
mitem1-value2
mitem1-value3
mitem1-value4
When the navigate down in master items, then the corresponding detail values are being shown on the same first record.
View 1 Replies
View Related
Jun 8, 2010
I want to insert multiple record in diff. table by using single query...
how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..
But i want to do this task by using single query....how can i do it?
View 4 Replies
View Related
Jan 27, 2012
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form
2)Reservation Form
3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
View 9 Replies
View Related
Aug 27, 2010
After setting up a data entry page,The logic in my page requires that all the records in my tabular form should be displayed .The tabular form is based on a collection, and the user selects rows through a checkbox.Any reload of the page due to pagination will break the logic, as some calculation and display events occur during page load, based on the previous page.
I need to display about 25 rows.I have changed the report attribute to display 50 rows, but it displays rows only up to the bottom of the screen, i.e. 11 rows.
Unfortunately I only just added some new rows and saw this behaviour else I would not have gone that way, as I expected APEX to display all rows if required.
View 19 Replies
View Related
Aug 12, 2011
Can i set individual tab page bar color?
Like the attached screenshot..
View 3 Replies
View Related
Jun 29, 2013
I have a table structure as follows Student(Id,First_Name, Last_Name, email, Contact, Address1 ,Address2, City, Edit_Date,Create_Date,Archived)
Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and
the archived status of all rows with same email except this master row must be set to 1.The Create_Date must be set to the minimum of all the create_date values of rows with same email value
View 2 Replies
View Related
Sep 3, 2008
I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.
I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?
View 3 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
[Code]...
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Jul 22, 2010
SELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC
[Code]...
This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.
View 2 Replies
View Related
Oct 11, 2011
I have the following case:
create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)
[Code]....
I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used
select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;
I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.
View 4 Replies
View Related
Apr 25, 2013
i am trying to enable flashback on in my database and i am getting the below error.
idle> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
my db_recovery_file_dest_size is 1g. If i change it to 20g i am able to "alter database flashback on".
Why does a size of 1g give this proble? Any simple steps or recommendation that i am missing? Or is the size of the DB which impacts the db_recovery_file_dest_size also?
idle> select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
View 14 Replies
View Related
Apr 16, 2013
I am trying to find out what is the minimum Oracle User Role required for the GetSchema command to work using ODP.NET and the Oracle.DataAccess library.
Dim cn As New OracleConnection(ConnectionString) 'Oracle.DataAccess.Client.OracleConnection Return cn.GetSchema("Tables")
View 0 Replies
View Related
Jun 1, 2010
Script for test:
CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2))
insert into test values(0001,'A',2);
insert into test values(0002,'B',5);
insert into test values(0003,'C',2);
insert into test values(0004,'D',7);
insert into test values(0005,'E',9);
Now I want to get empno for the particular employees based upon subscript and I have written below
DECLARE
CURSOR cur_rec
IS
SELECT *
FROM TEST;
TYPE cur_type IS TABLE OF cur_rec%ROWTYPE;
v_cur_rec cur_type;
BEGIN
OPEN cur_rec;
FETCH cur_rec
BULK COLLECT INTO v_cur_rec;
[code]....
how to access individial item froma table of rowtype data.
CM: Added [code] tags, please do so yourself next time, see the orafaq forum.
View 5 Replies
View Related
May 17, 2013
I am upgrading a DB using catupgrd.sql, and one of the prerequisites is to shut down the listener. Now, I have multiple database instances registered with this listener, and I don't want the other ones to become unavailable while I do the upgrade. Is there any way to do this for a particular instance only?
View 9 Replies
View Related
Mar 16, 2012
Would like to know, how to find the size of individual folder in ASM 10g !
Also I am not able to find the PATH/name of the raw devices in ASM 10g !
View 3 Replies
View Related