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
ADVERTISEMENT
Oct 4, 2013
I've below table.
with tmp_tbl as
(select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt,
[code].....
Now i need to rank each cust_id in each hh_id based on below conditions.
1) If atleaset one cust_id in hh_id has f_rnk then gender 'F' with highest f_rnk (more then one F with same f_rnk then the one with oldest first_dt), if no 'F' then gender 'U' with highest f_rnk ((more then one F with same f_rnk then the one with oldest first_dt)), if no 'F' and 'U' then consider 'M' (more then one M with same f_rnk then the one with oldest first_dt).
2) If the above is not met (no cust_id in hh_id has f_rnk populated) then i've to rank based on purch_dt. Gender 'F' with recent purch_dt (if more than one F in household with same purch_dt then the one with oldest first_dt), if no 'F' then gender 'U' with recent purch_dt (if more than one U in household with same purch_dt then one with oldest first_dt), if no 'F' and 'U' then consider 'M' (more than one M in household with same purch_dt then the one with oldest first_dt).
3) If the above criteria is also not met, then rank based on gender_cd. Gender 'F' will have first preference then 'U' and then 'M'.
My output :
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT F_RNK_RANK PURCH_RANK GENDER_ONLY_RANK
----- ------- ---------- ------ ----------- ----------- ------------ ------------ -----------------
H1 C1 2 F 1/2/2013 2/3/2010 1
H1 C2 1 M 1/2/2013 3/3/2010 2
H1 C3 U 1/3/2013 4/3/2010 3
H2 C4 F 4/3/2013 10/2/2009 2
H2 C5 M 5/5/2013 8/8/2010 1
H3 C6 F 5/6/2008 1
H3 C6 M 7/8/2010 2
I've tried below query with one condition, but it's giving f_rnk_rank for all records. How can i include multiple conditions in the rank function.
with tmp_tbl as
(select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, s hh_id, 'C5' as cust_id,
[code]....
View 3 Replies
View Related
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
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
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
Nov 5, 2012
I am new to the forum as well as SQL programing and I need to have the following criteria writen in my WHERE so all three of these criterias are included in the same report (perhaps, each will have its own section).
1. PCT_To_Goal < 60
AND (round ( (opened_period / expected_Goal ), 2 ) * 100) >= 100
2. opened_period >= 3 and number_to_date = 0
3. PCT_To_Goal < 30
AND (round ( (opened_period / expected_Goal ), 2 ) * 100 > = 50
AND round ( (opened_period / expected_Goal ), 2 ) * 100 <= 100)
View 2 Replies
View Related
May 12, 2012
Scenario 1 Query should check for priority record(25), if the start_date and end_date of that priority record is the max in that group, records will not have any split.output will be the same.
DC Store St Date End date Priority
955 3 1/1/2010 12/31/9999 25
966 3 4/5/2011 10/10/2011 50
977 3 10/12/2011 12/12/2012 100
output
DC store St Date End date Priority Rank
955 3 1/1/2010 12/31/9999 25 1
966 3 4/5/2011 10/10/2011 50 2
977 3 10/12/2011 12/12/2012 100 3
Scenario 2 If priority record is not covering the max range, then split the records as shown below,
1. during the time period 1/1/2011 & 4/30/2011 there were no other DC for that store so rank would be 1
2. the next range would be 5/1/2011 to 6/29/2011 we have 2 records in service so the record with low priortiy would be ranked 1 and second priority would be ranked 2
3. similarly, for 6/30/2011 to 10/1/2011 we have 3 records in service and it will be ranked accordingly on the priority.
DC Store St Date End date Priority
966 3 6/30/2011 10/1/2011 25
955 3 5/1/2011 11/30/2011 50
977 3 1/1/2011 12/31/2011 100
output
DC store St Date End date Priority Rank
977 3 1/1/2011 4/30/2011 100 1
955 3 5/1/2011 6/29/2011 50 1
977 3 5/1/2011 6/29/2011 100 2
[code]....
Scenario 3 This works similar to scenario 2
DC Store St Date End date Priority
966 3 2/1/2011 12/31/2011 25
955 3 1/1/2011 12/31/2012 50
977 3 5/1/2011 06/31/2011 100
output
DC store St Date End date Priority Rank
955 3 1/1/2011 1/31/2011 50 1
966 3 2/1/2011 12/31/2011 25 1
955 3 2/1/2011 12/31/2011 50 2
977 3 5/1/2011 6/30/2011 100 3
955 3 1/1/2012 12/31/2012 50 1
Note: Number of records in the input can vary and ther can be duplicates in the date interval
View 5 Replies
View Related
Dec 20, 2012
I have a table and it's having duplicate records.
for one particular employee, he is having multiple records with the same data in the table
EMPNO ENAME JOB SAL DOB
-------------------------------------------------------
1 A X 100 1956
2 B Y 200 1974
1 A X 100 1956
3 C Z 300 1920
[Code]....
like this am having multiple times the duplicates.
I have written the below query to delete the duplicate records. But it is deleting only one record (if we have 5 duplicates it is deleting only 1 ). But I am looking to delete if we have 5 duplicates need to delete 4 duplicates and keep 1 record in the table.
query which am using to delete the duplicates is
DELETE FROM Table1 a
WHERE ROWID IN (SELECT MAX(ROWID)
FROM Table2 b
WHERE a.ID = b.ID);
it is deleting only one row but I want to delete 4 records out of 5 and keep one record.
View 12 Replies
View Related
Jul 12, 2011
I have a database block that refreshes using a timer. In short, I list all open work pieces for a certain garage, displaying route, run, on_time, of_time, location grouped by garage, on_time, off_time
I need to highlight every garage with a different color. I am highlighting the on_time every time the garage changes.
The problem is, every time I move down the records, instead of keeping the previous garage color, and highlight the new garage on_time with a new color, the whole on_time column will be set to the current garage color.
I need to break down the block by color so the user can distinguish between the different garages, and the start and end time. Here is my code, I am putting it in when_new_record_instance of the work_block:
IF :work_bLock.current_garage = 9006 THEN
Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r191g223b191');
ELSIF :work_bLock.current_garage = 9002 THEN
Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r255g228b196');
ELSE
Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r255g250b250');
END IF;
I need the block to be 3 or more different colors depending on the garage.
View 2 Replies
View Related
Apr 29, 2013
Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.
Method I have used:
1. Created Temp table
2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.
delete from B where exists (select 1 from temp where b.col1=temp.col1);
3. why it is taking too much time for deleting records in table A.
View 5 Replies
View Related
Aug 2, 2011
I want to delete records from block in the form.could you explain where(in which trigger) should i write set_block_property.
View 9 Replies
View Related
Sep 29, 2011
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
Expected result after deletion
GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26
View 5 Replies
View Related
Sep 7, 2010
I executed the following delete statement.
DELETE FROM sre_t WHERE TO_CHAR(end_dt,'yyyy')<'2000'
or TO_CHAR(start_dt)<'yyyy')<'2000';
It's executing for 15 to 20 minutes after that i got the error "session timed out"..The table is having four crore records.The delete statement is deleting 12,00000 records.
View 4 Replies
View Related
Jan 8, 2011
Actually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.
View 6 Replies
View Related
Apr 20, 2011
SELECT
MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE,
MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)) as COMMENTS,
MAX(fnddoc.description) as REASON
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl,
WSH_NEW_DELIVERIES DLVRY
[code]....
I have three tables, I have to merge those three tables, all three tables having same conditions and filter conditions(in each table one filter condition changed), I highlighted in the red difference the filter conditions in each table, finally my result should be 7 columns like
LAST_UPDATE_DATE, COMMENTS, REASON, CORRECTD_ACTUAL_DELIVERY_DATE, LAST_UPDATE_DATE, CORRECTD_PROMISE_DATE, LAST_UPDATE_DATE
View 2 Replies
View Related
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
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
Dec 1, 2011
I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.
View 8 Replies
View Related
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
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
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
View Related
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
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
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
Mar 24, 2013
I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.
create table ow_oper_setup (wo_no varchar2(12),mrk_no varchar2(20),pos_no varchar2(30),mc_code varchar2(60))
insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ')
insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ')
insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ')
insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')
commit;
SELECT * FROM ow_oper_Setup;
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR HO BV
12701270001W1332IR BV
12701270001W1367RE HO SC BV
12701270001W389RE HO SC BV
--i want the output in the following way or the same table data to be replaced as below
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR
12701270001W165HO
12701270001W165BV
12701270001W1332IR
12701270001W1332BV
12701270001W1367RE
12701270001W1367HO
12701270001W1367SC
12701270001W1367BV
12701270001W389RE
12701270001W389HO
12701270001W389SC
12701270001W389BV
View 12 Replies
View Related
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
Sep 14, 2012
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
-- ITL Table
Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)
[code]....
-- Issued Removed table
create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)
[code]....
View 1 Replies
View Related
Dec 28, 2012
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
I want to delete dups from a table based on 3 columns
with sample_table as (
select '101' as ID1, '201' as ID2, '4' as weight from dual union all
select '101' as ID1, '201' as ID2, '5' as weight from dual union all
select '105' as ID1, '205' as ID2, '6' as weight from dual union all
select '105' as ID1, '205' as ID2, '6' as weight from dual union all
[code].........
Desired Output
with sample_table as (
select '101' as ID1, '201' as ID2, '5' as weight from dual union all
select '105' as ID1, '205' as ID2, '6' as weight from dual union all
select '110' as ID1, '215' as ID2, '9' as weight from dual
)
select * from sample_table
View 5 Replies
View Related
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
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
