PL/SQL :: How To Extract Records Based On Group

Aug 31, 2012

I have a table with with 2 colums serialnumber and brand .

each brand may have multiple serialnumber .

I want to extract 10 serialnumber for each brand .

View 2 Replies


ADVERTISEMENT

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

I'm trying to group sets of data based on time separations between records and then count how many records are in each group.

In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5

SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';

[code]....

Group 1 = 8:00:22 to 8:41:22

Group 2 = 11:35:47 to 11:35:47

Group 3 = 15:13:46 to 15:13:46

Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.

View 4 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

PL/SQL :: Sum Based On Group 2

Nov 26, 2012

I have the following table and data , I have six employees and carton named A,B,C,D,E and F.

Drop table a ;
Create table a (id number(9), emp_id number(9), cartoon varchar2(20), no_cartton number(9));
Insert into a values(1,1,’A’,10);
Insert into a values(2,1,’B’,20);
Insert into a values(3,1,’D’,25);
Insert into a values(4,1,’E’,15);
[code].......

I have also Third Query: the first and second query already solved in this link . Sum based on group

it is multiple value of carton A of emp_id 1 with 2 + multiple value of B of emp_id 1 and 2 and so on

More detalies : Multiple value of A for Empid 1 and 2 then add it to multiple value of multiple value of b for empid 1 and 2 then add it to multiple value of c of empid 1 and 2

After finished all carton for 1 and 2 then go to 1 and 3 after finished go to 1 and 4 then 2 and 3 then 2 and 4 and so on

Output will be like this
Empid       Total

1 with 2    2050
1 with 3    200
1 with 4    500
1 with 5    2250
1 with 6    700
2 with 3     0
2 with 4    700
2 with 5    4300
2 with 6    1700
3 with 4      0
3 with 5    400
3 with 6     0
4 with 5    400
4 with 6    200
5 with 6    1900

View 2 Replies View Related

SQL & PL/SQL :: Extract Correct Value For Child Records

Mar 22, 2011

Test case: drop table test;

create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))

select * from test;

insert into test values (143,'frank','kadel,watson','j2098,k09876');
insert into test values (143,'steve','kadel,watson','j0987i,kuy765');
[code]....

The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.

For example:- Where id = 143, the resultset should be like this.

ID LAST_NAME FIRST_NAME EMPNO
---------- --------------- --------------- ---------------
143 frank kadel j2098
143 frank watson k09876
143 steve kadel j0987i
143 steve watson kuy765

sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.

SQL> select id, last_name, EXTRACTVALUE(x.COLUMN_VALUE, 'e') first_name, empno
2 from test,
3 TABLE(XMLSEQUENCE(XMLTYPE('<e><e>' || REPLACE(first_name, ',', '</e><e>') || '</e></e>')
4 .EXTRACT('e/e'))) x
5 ;
[code]...

View 8 Replies View Related

SQL & PL/SQL :: Ranking Based On A Group?

Jun 29, 2011

I have a table t

CREATE TABLE T
(ID NUMBER(4),
NAME VARCHAR2(40))

INSERT INTO T VALUES (1,'JAMES');
INSERT INTO T VALUES (1,'DOLLY');
INSERT INTO T VALUES (2,'MICHEAL');
INSERT INTO T VALUES (2,'FLASH');
INSERT INTO T VALUES (3,'JAMES');
INSERT INTO T VALUES (3,'MARY');
INSERT INTO T VALUES (4,'JAMES');
INSERT INTO T VALUES (4,'DOLLY');
INSERT INTO T VALUES (5,'JAMES');
INSERT INTO T VALUES (5,'DOLLY');
INSERT INTO T VALUES (6,'JAMES');
INSERT INTO T VALUES (6,'MARY');

SELECT * FROM T ORDER BY 1

ID NAME
1 JAMES
1 DOLLY
2 MICHEAL
2 FLASH
3 JAMES
3 MARY
4 JAMES
4 DOLLY
5 JAMES
5 DOLLY
6 JAMES
6 MARY

each 'ID' has two values always. I want to rank the data based on same pair 'name' in an 'ID'

for example, my desired output is:

ID NAME RANK
1 JAMES 1
1 DOLLY 1
2 MICHEAL 1
2 FLASH 1
3 JAMES 1
3 MARY 1
4 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
4 DOLLY 2 -----> SAME AS ABOVE
5 JAMES 3 ---> THAT IS RANK 2 BECAUSE THIS IS THE 3RD TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
5 DOLLY 3 -----> SAME AS ABOVE
6 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND MARY ARE IN THE SAME 'ID'
6 MARY 2 -----> SAME AS ABOVE

I want the output in exactly above format.

View 3 Replies View Related

JDeveloper, Java & XML :: Duplicate Records After XML Extract

Mar 5, 2011

I've got a table with XML code (CLOB column) and I need to read and write every of parameter(blue marked below) and value (red marked below) record from that.

For example:

create table tmp_mape
(msisdn varchar2(100),
xml_params CLOB )
insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' );
[code]........

I tried to read this every values of xml code by select below:

select
extractValue(value(x),'/parameter/@name', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') b,
extractValue(value(x),'/parameter/value', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') a,
a.*
[code]......

This select returns a duplicate records because of more the one parameter and value records from XML column.

View 30 Replies View Related

SQL & PL/SQL :: Display Data Based On Group And Percentage?

Feb 12, 2012

I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups.
CREATE TABLE target_data
(
T_LINK VARCHAR2(50)
, t_mon varchar2(6)
, t_grp varchar2(30)
, t_views NUMBER

[code]...

When I run this, I get output as

t_grp count_80 goal
grp3 1 0.1

But I want the output as

t_grp count_80 goal
grp3 1 0.1
grp2 1 0.1
grp1 ... ...
grp0 ... ...

View 16 Replies View Related

PL/SQL :: Group By Data Based On Start And End Time In Range

Mar 28, 2013

formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.

create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));

--table rel_data_mf data as comma separated values
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U

[Code]...

--Required output

header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U

View 6 Replies View Related

SQL & PL/SQL :: Group By Matching Records

Dec 19, 2012

I have one table employee where there are 4 fields ,emp_code,emp_locn,emp_job_code,emp_job_desc the problem is i am trying to prepare a group reports based on location and emp_job_code there is a duplication of data in the emp_job_desc ,

For example there is a job_code E2 Which has two different job_descriptions for two different employees like E2-PAINTER-SPRAY, E2- PAINTER -SPRAY, another example is E1-rigger , E2-RIGGER and so on.Is there a method to match them together as one description.

View 7 Replies View Related

SQL & PL/SQL :: Duplicate Records And Group By

Jun 22, 2010

My duplicate records have been detected by First Name, Last Name, Name, and City.

such as

select FirstName, LastName, Name, City, count(*) as Num of Duplicate from TABLE
GROUP BY FirstName, LastName, Name, City
having count (*) > 1

It gives the duplicate record. Now I need all the columns and the each duplicate record in the select, so I can see why these records are duplicate.

View 6 Replies View Related

SQL & PL/SQL :: Group By Gives Wrong Value In Huge Data Records?

Jun 18, 2012

I have table which contains huge data. around 12 lakhs records. when I use sum function on accountname and docdate it gives wrong value. once I restart the server it gives the correct value. one or two days it gives correct value after that again I get the same problem. If I restart again it gives correct value.

I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 64 bit server on Linux.

View 6 Replies View Related

SQL & PL/SQL :: Difference In Number Of Records In GROUP BY And PARTITION BY

Feb 17, 2012

If I run the following query I got 997 records by using GROUP BY.

SELECT c.ins_no, b.pd_date,a.project_id,
a.tech_no
FROM mis.tranche_balance a,
FMSRPT.fund_reporting_period b,
ods.proj_info_lookup c,
ods.institution d
WHERE a.su_date = b.pd_date
AND a.project_id = c.project_id
AND c.ins_no = d.ins_no
AND d.sif_code LIKE 'P%'
AND d.sif_code <> 'P-DA'
AND a.date_stamp >='01-JAN-2011'
AND pd_date='31-MAR-2011'
GROUP BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no;

I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.

SELECT c.ins_no, b.pd_date,a.date_stamp,a.su_date, a.project_id,
a.tech_no,
COUNT(*) OVER(PARTITION BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no)c
[code]....

why I got 1079 records.how to show the two extra columns in the out put whcich are not used in GROUP BY clause.

View 8 Replies View Related

SQL & PL/SQL :: How To Get Records Based On Max Date

May 18, 2011

I am trying to get records from a table based on date column. I should retrieve records if the date column is NULL

and if it is not null ,i shud get the records of MAX date.

How would i do this. Below is my query,by which i can get records based on either maxdate or Null date

select c.id, c.status,c.u_gr_code,
from
(
select id, status,u_gr_code,date_col,
max(record_valid_from) over (partition by entity_user_gr_id, status_id) max_date
[code].........

View 10 Replies View Related

SQL & PL/SQL :: Update Based On Other Table Records?

Nov 4, 2009

CREATE TABLE "SCOTT"."SEATALLOTMENT"
("YEAR" NUMBER(4,0),
"COLLEGECODE" CHAR(4 BYTE),
"COURSECODE" CHAR(3 BYTE),

[Code].....

Now i want to UPDATE reducing the AVAILABLE column by 1 in COURSESEATS table based on common columns collegecode,coursecode for a ROW inserted into SEATALLOTMENT table ,i am confused to what approach i have to follow whether its a procedure or a trigger

CASE:

Here in this case as i insert a row with krcl,cse as college code and course code respectively into seatallotment table the available column in courseseat table for the respective row with mentioned common column must become 59 from 60

View 5 Replies View Related

SQL & PL/SQL :: Fetch Records Based On 2 Status

Jan 25, 2013

create table test_exp (oid number, ioid number, status varchar2(20));

Below are the insert statements.

Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');
[code]...

Now my problem is we should fetch the data based on the below rules

If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records
If an OID has only 1 of these status, then ignore the same
If an OID has none of the 2 status, then ignore the same.

The expected output will be like below:

OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW

View 4 Replies View Related

PL/SQL :: Selecting Records Based On The Flag

Jun 7, 2012

I have records like the following

Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N
ABCD 2/14/2012 N
ABCD 2/20/2012 Y
ABCD 3/01/2012 N
ABCD 3/10/2012 N

[Code]...

I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.

Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N -- I have preserved the first record
ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on.
ABCD 3/01/2012 N
ABCD 3/14/2012 Y
ABCD 3/25/2012 N
ABCD 4/25/2012 Y

If there is no change in the flag, I do not have to pull that record.

View 3 Replies View Related

PL/SQL :: Selecting Records Based On Value Of Field

Dec 5, 2012

I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this

SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.

View 5 Replies View Related

PL/SQL :: Grouping Records Based On Odd And Even Rows?

Apr 22, 2013

I have a sets of data in table ow_ship_det , from which i want to group all the records which are having same sl_desc but with the condition that sl_qty is not more than 1000 and sl_wt not more than 50000, i managed to do it but the problem is i want the wieght(sl_wt) and qty(sl_qty) to be evenly distributed among groups or boxes for example take the first four records which have common sl_desc 'H170' Where the qauntities are 15000,15000,10000,10000 as per the condition and loop written in program it will bring the 2 boxes or serial numbers with first 3 weights into 1 box as 40000 and other box as 10000, which i dont want instead i want to have them as 25000 each.

CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300);
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300);
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300);

[code]...

--if you see above the weight is not balanced properly in batches 0001 for H170 Desc it should get divided equally as below

ob_batch OB_PM_CODE OB_DESC OB_QTY OB_WT
0001                A           H170      300         15000
0001                C           H170       300        10000
0002                B           H170       300        15000
0002                D           H170       300        10000

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Deleting Multiple Records Based On Conditions

Jun 28, 2010

I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.

Sample Table Script:

CREATE TABLE TEST
(
CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL
);
[code]...

View 8 Replies View Related

SQL & PL/SQL :: Fetch Records Based On Status Priority

Dec 20, 2012

I have a table with below syntax and insert statements.

-- Table creation
-----------------------
create table order_test (oid number,
orderno number,
ordername varchar2(100),
orderstatus varchar2(50),
orderseq number);
----------------------------------
-- Insert statements

[Code]....

The Output looks like below

OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
1 |100 |ORD1 |INPROGRESS |1
7 |100 |ORD1 |START |4
3 |100 |ORD1 |START |2
4 |100 |ORD1 |INPROGRESS |3
6 |101 |ORD2 |CANCELLED |3
2 |101 |ORD2 |INPROGRESS |2
5 |101 |ORD2 |COMPLETE |1
8 |102 |ORD3 |COMPLETE |1
9 |103 |ORD4 |CANCELLED |1
10 |103 |ORD4 |CANCELLED |2

Now, the below rules should be applied to get the output..

1) If status is same, then fetch max(seq) record
2) If status is different, then apply below priority to fetch the records

Start - Priority 1 (High)
In Progress - Priority 2
Cancelled - Priority 3
Complete - Priority 4 (Lowest)

Means, the output for each order should look like below.

OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
3 |100 |ORD1 |START |2
2 |101 |ORD2 |INPROGRESS |2
8 |102 |ORD3 |COMPLETE |1
10 |103 |ORD4 |CANCELLED |2

how to do it.

View 1 Replies View Related

SQL & PL/SQL :: Create Multiple Records Based On Condition

Oct 10, 2012

CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');

SELECT * FROM test1

STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ

The requirement is the records should be split based on below conditions

1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM

This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.

Expected Output.

STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10

10 20 AB
21 25 AB

12 22 PC
23 32 PC
33 35 PC

22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM

View 7 Replies View Related

Query To Split Records Based On Values From Another Table?

Feb 20, 2012

I have a table with following values in a column

Table A

col1
10
35
20
25

I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.

Table B- 90 records (10 + 35 + 20 + 25)

Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,

with first group having start value -1 and end value -10

second group - start value -11 and end value-45

third group - start value -46 and end value -65

fourth group - start value - 66 and end value-90

one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.

note that the values in Table A is dynamically changing, so not able to hard code values.

View 2 Replies View Related

SQL & PL/SQL :: Deleting Duplicate Records Based On Repetition Of Certain Fields

Sep 15, 2011

I have the following situation and need support:

create table try_x
(a number PRIMARY KEY,
b NUMBER,
c NUMBER,
f_text VARCHAR2(10));

insert ALL
into try_x values (0,1,1,'abc')
into try_x values (1,1,1,'abc')
into try_x values (2,1,1,'xyz')
into try_x values (3,1,2,'abc')
into try_x values (4,1,2,'abc')
into try_x values (5,1,2,'abc')
into try_x values (6,1,3,'abc')
into try_x values (7,1,3,'abc1')
into try_x values (8,1,3,'abc2')
into try_x values (9,1,3,'abc2')
select * from DUAL;

Although a is the PK, records with similar b,c,f_text are considered redundant and I need to delete all occurrences in the table where b, c, d are redundant and leave the unique ones. So I need the result to look like:

a b c f_text
-----------------
0 1 1 abc
2 1 1 xyz
3 1 2 abc
6 1 3 abc
7 1 3 abc1
8 1 3 abc2

View 15 Replies View Related

PL/SQL :: Updating Count Data Based On Records In Another Table

Nov 6, 2013

I have 2 tables

Table 1Name Item   DateJon  Apples  06/11/2013 00:30:00 hrsSam  OrangesNish Apples  
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon  Apples 8 

I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar

View 2 Replies View Related

PL/SQL :: Select Records Based On First N Distinct Values Of Column

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

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

SQL & PL/SQL :: Filtering Dataset Based On Records Returned By Table Join

Nov 10, 2010

I need to work on this requirement.

There are FOUR tables ( T1 , T11 & T2, T22) ALL store order information.

One of four conditions are possible for each Supply Reorder Number:

•Both table queries return no records

oPopulate all the output fields with nulls

•T1 returns a record, but T2 returns no records

oPopulate output fields with values from the join of T1 and T11.

•T1 returns no records, but T2 returns one record

oPopulate output fields with values from the join of T2 and T22.

•T1 returns a record, and T2 returns a record

oIf the latest order is in T1, then populate output fields with values from the join of T1 and T11.

oIf order dates are equal from both join results, then populate output fields with values from the join of T1 and T11 .

oIf the latest order is in T2, then populate output fields with values from the join of T2 and T22.

How do we filter the dataset based on result of table join ?

View 1 Replies View Related

SQL & PL/SQL :: Join For Bringing Master And Child Records Based On Condition?

Jul 30, 2013

The attachment contains the table info. The condition is that when there are childer for the master ,the master record shd be negated . Excuse me if this very easy. Example -DEL HAS children so the record with DEL and DEL SHD not be in the result query. Example -RAG HAS NO children. so the MASTER record should be taken.

Input

COL1COL2COL3COL4
MASTERDELDELP1
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1

My expected output
COL1COL2COL3COL4
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1

View 3 Replies View Related

Forms :: Filter Records In A Data Block Based On Yes Or No Criteria

Aug 13, 2010

I need to filter records in a datablock based on Yes or No criteria.So i created a listitem(INCLUDE_ZERO_QTY_ITEMS) for that and i am using following code in WHEN-LIST-CHANGED trigger.

BEGIN
IF :XGEC_SW_ITEM_LOAD_ITEM_DETS_V.INCLUDE_ZERO_QTY_ITEMS = 'Y'
THEN
GO_BLOCK ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V');
SET_BLOCK_PROPERTY ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V',
default_where,
'1=1');
[code]....

I am able to filter the records but the list item is not displaying initial value and the value that i am selecting from poplist.

View 1 Replies View Related







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