I have this requirement in Oracle FORMS ver 6i where I populate some records in a table in a datablock and display them on the screen. A facility needs to be provided to the end users to select the records randomly by just entering the count of the records in a non-d/b text field. The number entered in the count field will decide how many records are to be selected randomly.
I tried using the query -
UPDATE <table a>
SET <col 1> = <value 1>
WHERE rowid IN (SELECT rowid FROM (SELECT * FROM <table a> ORDER BY DBMS_RANDOM.VALUE)
WHERE <col 2> = <value 2>)
/
which I found on the link [URL] but while compiling, the Form gives a compilation error for the ORDER by clause - i'm sure there's no syntax error in the way I have written it...
how to update the middle of plenty rows in the middle of the columns
sample_data
id name state REGION LOC 1 v A.p 1 1 2 a 3 g K.A 0 3 4 y 5 i T.N 1 0 6 l M.P 0 1 7 c U.P
This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,
trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.
Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.
Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.
I am trying to create a csv file with summarized data. We have a huge table with claim details that is constantly being updated. I am pulling a subset of records that match my criteria into a tempory table (not technically an Oracle temporary table, a regular table that will only exist until I drop it when I am done). This table has multiple entries per claim with different effective dates, paid dates and amounts paid. The result file needs to have one entry per claim with the oldest effective and paid dates and the total of all of the amounts paid on that claim.
Originally I was doing create table new_table as select claim_nbr,other data...,min(ymdeff),min(ymdpaid),sum(amtpay) from my_table group by claim_nbr,other data...
If I ran a select sum(amtpay) from my_table and select sum(amtpay) from new_table I was not getting the same results... If I ran select count (*) from (select distinct claim_nbr from my_table) and select count (*) from (select distinct claim_nbr from new_table) or select count (*) from new_table I was getting the same number of rows. So I wasn't completely losing claims from one table to the next, just some of the details. So, I tried running this:
select * from (select claim_nbr,sum(amtpay) paysum from my_table group by claim_nbr order by claim_nbr) m, (select claim_nbr,sum(amtpay) paysum from new_table group by claim_nbr order by claim_nbr) n where m.claim_nbr = n.claim_nbr and m.paysum <> n.paysum;
It came back with the claim number causing the issue. I looked at all the entries in my_table for that claim and every field was identical except the ymdeff, ymdpaid and amtpay. There were 4 records in my_table however the amtpay in new_table was only a sum of 2 of the records... I our admin look over my shoulder to see what was wrong and they wanted me to recreate new_table. So I dropped new_table and ran the exact same SQL to recreate the table. The number of distinct claim numbers was still the same in both tables and the sum of new_table was off but not by the same amount. I ran my comparison to see which claim was off and now there were two claims where the totals didn't match and neither were the same as the claim that was wrong that first time. We dropped new_table and recreated it several times and every time we got different results... No one else knows the name of my_table so no one was messing with it at the same time plus the sum of amtpay in my_table always comes back the same.
Our admin said he thought he remembered there being something "funny" with the min function sometime so he had me remove those fields. Ran the query several times and the total came out correct each time. Well I still need the dates so I came up with another way (very convoluted) using subqueries and ranking. It seemed to work at first then it started losing random numbers of claims (fewer rows in new_table than distinct claims in my_table) or keeping all the claims but dropping detail lines like I had using the min functions.
Here is the backwards way around using min that drops whole claims sometimes but works fine other times:
CREATE table new_table as (select claim_nbr,other data...,amtpay,ymdeff,ymdpaid from (select claim_nbr,other data... ,sum(amtpay) amtpay from my_table group by claim_nbr,other data...
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
i have a parameter form where user will input from item to item and items within this range will be come as output , but i want an option to select the random items from the list, like i may not select all the items from 1 to 10 , but instead i want to pick or select randomly , i may opt for 1,3,4 and 7.
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
for Inserting records created below cursor, records are inserted and I can see on my screen (form) also.
DECLARE CURSOR InvestIS select * from Tempinvest; BEGIN OPEN Invest; GO_BLOCK('ReturnReport'); last_record; [code]......
2nd step:-
Updating records I wan to update some columns in this table so I created a 2nd cursor to update records but this cursor is not working accordingly. my requirement is: before Update first of all Find inv_co_code & inv_fnd_code when find then update column Redunits
2nd Cursor
DECLARE ddate date; refdate date; co_code number; co_name varchar2(50); fnd_code number; fnd_name varchar2(50); units number; amount number; stat varchar2(1); CURSOR bnr IS
select Ddate,refdate,co_code,co_name,fnd_code,fnd_name,units,amount,stat from ( select inv_date DDate,Vdate RefDate,inv_co_code Co_code,inv_co_name Co_name,inv_fnd_code Fnd_code,inv_fnd_name Fnd_name, inv_nofu Units,inv_amount Amount,Status Stat from ( ------------- Bonus ------------------- select inv_date,vdate,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_nofu,Inv_amount,inv_uprice,'B' Status from investment where code is null and inv_date <= :dd1 ---------------- Redemption -------------- Union All select red_date,null,red_co_code,red_co_name,red_fnd_code,red_fnd_name,red_nofu,red_amount,red_uprice,'R' Status from redemption where red_date <= :dd1 )) where co_code = 13 and fnd_code = 1 order by co_code,fnd_code,ddate ; ------------------------------------------ vddateddate%type; vrefdate refdate%type; vco_codeco_code%type; vco_nameco_name%type; [code].......
I am trying to develop a form consisting of a key block and a single data block. The problem is that the driving table is not the table that needs to be updated.
user wants the following layout:student Advisor ID# name degree major Concentration ID# name The driving table (TABLE A) will supply the first 5 fields. The advisor ID comes from (TABLE B).
The user needs to update the adviser ID# field associated with the student ID# field. The form is to be tabular listing all students. I've seen some info on using procedures to insert, delete, update, query and lock tables, but i'm just not sure if that's what is needed.
i have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.
how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept
1.Insert all data from global table to base table. 2.Update all data (that means retrieved all data from base table to global table and update this data). Question: How to Insert and Update from Global temporary table ??
I would like to UPDATE the columns p1 and p2 of my table student (studentid:pk,name,p1,p2,...) for a given studentid.and I have a when-button-pressed trigger with this
UPDATE student SET student.p1=:validation.proj1, student.p2=:validation.proj2 where UPPER(student.studentid)=UPPER(:validation.studentid);commit_form;
when I run my form with a correct studentid, I got this error: FRM-40508: ORACLE error: UNABLE to INSERT record
but it is cworking correctly in sqlplus; and I have all priveligies.
I 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)
I have 2 tables with 3 common columns (Col1, col2,Primary). One common column name is primary (oracle key word).Around 2 lakhs rows to be updated and No indexes are used on these tables. I need to write an update query as shown below.
, im having this trouble to update some records, the problem is i have I have this table with some transactions and each row have a cd_cli (client code), dt_ven (date of the transaction) and a product id (cd_prod) the trouble is, i insert a row for each product in this table, then i have like this
110001YURI AGUIRREWALLMA400V44-B469,0019/9/2013210001YURI AGUIRRESU-7N-B78,0019/9/2013310001YURI AGUIRREWALLSF35V22-S78,5719/9/2013410003WILLIANWALLSA470V22-BS449,002/9/2013 but i need to update the CD_TRANS for each row with the same number like all the YURI AGUIRRE recode must have like CD_TRANS 1, and the WILLIANs CD_TRANS must be 2 and the next must be cd_trans 3, but I've tried to update the table for a lot of ways but i don't have success trying to do this, the last thing i had tried was
MERGE INTO TVEN_TEMP a USING (SELECT CD_CLI, DENSE_RANK () OVER (ORDER BY DT_VEN) new_seq_num FROM TVEN_TEMP WHERE CD_TRANS IS NULL) b ON (a.CD_CLI = b.CD_CLI)
I can get it through this query : select PARTY_ID from XXWFS_CUSTOMER_EXT group by PARTY_ID having count (PARTY_ID) > 1;
Now for the records which i got for each duplicate row i want to update the second row with a specific value.. so that duplicate rows does not exist anymore
Ex: I got party id's 12, 14, 16, 18 two times each
Now as 12 is two times.. i want to update the second row of 12 with some x value same is the case for other values like 14,16, etc
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end BEFORE INSERT OR UPDATE OF issued ON shares_amount FOR EACH ROW DECLARE BEGIN INSERT INTO shares_amount(date_end) VALUES(SYSDATE); END set_date_end; /
This is a surprisingly common one I've found on the web...even on devshed forum
I am updating one table from another (Updating Table A from Table B):
Table A ID, Value -- ----- 1 A 1 A 2 B
Table B ID, Value -- ----- 1 Animal 2 Box
Table A (modified) ID, Value, Name 1 A Animal 1 A Animal 2 B Box
No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...
Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?
This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).
Assume that we have table Test with a column TestId and Status. This table is having 10,000 records. Now, we need to write a stored procedure that does the following:
1) Select top 10 rows whose "Status = NEW'
2) Update the selected top 10 rows with "Status = PROCESSING"
3) Return those top 10 rows.
Assuming there are hundreds of such requests coming to Oracle database, how can we make sure each request NEVER gets a duplicate record at any given point of time and how to fetch the records that have been updated individual request?
I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?
for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?
Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.
Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.
Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.
MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.
Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row
UPDATE MEMBER M SET M.CORPIDENTIFIER= (SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))
Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.
I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.
The table has fields like: category - string, item - string, creation_date - string ('YYYYMMDDHHMI'), complete_flag - boolean, etc.
Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date. Is there a way to accomplish this?
