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
The data in the table was imported from a csv file and there is a relationship between the rows. Each combination of col1, col2 and col3 describes a full route of a journey. The row with an entry in col6 describes the full route and the other rows describes each leg in the route.
For example, for R1, the route is AA to BB via CC. Another example for R4 the route is FF to SS via XX, PP, and OO.
What i would like to do is missing a route. For example the route for R3 is DD to EE via FF. There is an entry for DD to FF but is missing an entry for FF to EE.
The results should return the following rows which are incomplete
Here is what i have come up with but it doesnt quite returned the correct result.
select * from tableA a Where not exists( select 1 from tableA b where instr(col6,col4,1)>0 and instr(col6,col1,1)>0) And a.col1=b.col1 And a.col2=b.col2 And a.col3=b.col3 )
i have a table where there are codes of length 6 or length 12 some times, i need to add the summary of amount based on two different types of codes, one is adding the distinct codes which are of 6 char long and other sum will be based on from substr(7) till last.
create table strings ( strings_var varchar2(12),strings_amt number); insert into strings (strings_var,strings_amt) values ('02.01',10 ); insert into strings (strings_var,strings_amt) values ('02.01_A11111',15); insert into strings (strings_var,strings_amt) values ('02.02_A11111',15); insert into strings (strings_var,strings_amt) values ('03.01_B11111',15); insert into strings (strings_var,strings_amt) values ('03.02_B11111',15);
I can give you the logic first i'll sort the start_date(already sorted in given example), then i'll compare the 2'nd id start date with 1'st id end date if it is less than the 1'st id end date, which means overlapping is there, then i'll group those 2 id's in to same group if not group them into 2 different groups.
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].....
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
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.
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.
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.
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.
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
name|address|qty alan,1 the street,5 bert,2 the road,3
what I want to do is create an output that takes the value in the qty column and repeates the number of rows e.g. the alan record 5 times and the bert record 3 times
name|address|qty alan,1 the street,5 alan,1 the street,5 alan,1 the street,5
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]...
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
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338); INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
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.
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)
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
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
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..
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
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.
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.
