SQL & PL/SQL :: Updating Another Table For Equal Strings
Jun 8, 2010
There are two tables:
create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);
im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.
here is what i've done so far:
drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);
What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist
View 4 Replies
ADVERTISEMENT
Aug 20, 2010
I've created a query so I can easily compare two sets of data for two different instruments:
select a.CalId, a.AtName, a.NRef, a.VaLoat, a.ValTime, a.ValRing,
cvs.NRef, cvs.CalId, cvs.AtName, cvs.VaLoat, cvs.Valtime, cvs.ValRing
from CalcAttribute a, CalcAttribute cvs
where a.NRef like '438815' and cvs.NRef like '438813'
and a.CalId *= cvs.CalId
and a.AtName *= cvs.AtName
union
[Code]...
This works great - however I want to add an addtional condition, basically so it only returns where the two are not equal.
I thought I should just be able to add an extra:
and a.ValLoat *<> cvs.ValLoat
and a.ValLoat <>* cvs.ValLoat
But it doesnt seem to like this (Incorrect syntax near '<'.)
View 3 Replies
View Related
Sep 23, 2011
I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';
CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......
This is an except of what's in the external text file. The full text file has been attached.
000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------
Here is the error message I am receiving. I believe this is caused by the blank fields in the data.
INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?
View 12 Replies
View Related
Jan 24, 2011
I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
View 1 Replies
View Related
Nov 19, 2008
I am using oracle 9i, and having trouble with updating a table.
I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.
why this error occurs or why this error did not occur on my PC,
[Update sql](key is CD and SDATE)
Update TBL1
set CD = 'A',
[Code]....
View 4 Replies
View Related
Jun 27, 2011
I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:
LOAD DATA
INFILE 'table1.dat'
INFILE 'table2.dat'
APPEND
INTO TABLE table1
WHEN tid='1'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(pid "pid.nextval", p_fname, p_lname, tid)
[code]....
The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:
table1.dat
,John,Doe,1
,Joe,Smith,1
table2.dat
10,,abc,1,
11,,xyz,1,
The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.
View 1 Replies
View Related
Oct 20, 2011
Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table. I have the trigger and it works except it is not loading my col1/PK values. I understand I need to do a new/old value. Col1 is my PK on Table that I want to load anytime there is an update/delete/insert on the table. How do I code the old/new variable?
My
CREATE OR REPLACE TRIGGER TRIGGER_NAME
AFTER INSERT OR UPDATE OR DELETE
ON TABLE_NAME
FOR EACH ROW
DECLARE
v_col1 TABLE_NAME.COLUMN%TYPE;
BEGIN
[code]...
View 5 Replies
View Related
Aug 23, 2013
I have two tables e.g. test_bb and test_sub
I would like to select test_sub.price as per the below conditions. If test_bb.value_date, test_bb.vehicle matches to test_sub.value_date,test_sub.vehicle then display test_sub.price
If there is no match then as above find the closest test_sub.value_date which is less than test_bb.value_date and select corresponding test_sub.price for the same vehicle combination.
e.g.
select * from test_sub;
VEHICLE VALUE_DAT PRICE
---------- --------- ----------
10 12-APR-12 2
10 08-JAN-10 4
10 14-APR-14 6
10 06-AUG-47 8
20 24-JAN-14 10
20 20-FEB-06 12
20 18-FEB-04 14
20 28-FEB-06 16
20 22-DEC-07 19
8 rows selected.
select * from test_bb;
VEHICLE VALUE_DAT
---------- ---------
10 12-APR-12
10 10-FEB-10
20 24-JAN-14
20 22-FEB-08
Required output:
VEHICLE PRICE VALUE_DAT
---------- ---------- ---------
10 2 12-APR-12
10 4 08-JAN-10
20 10 24-JAN-14
20 19 22-DEC-07
create table test_bb(vehicle number, value_date date);
begin
insert into test_bb values(10,to_date('12-04-2012','dd-mm-yyyy'));
insert into test_bb values(10,to_date('10-02-2010','dd-mm-yyyy'));
insert into test_bb values(20,to_date('24-01-2014','dd-mm-yyyy'));
insert into test_bb values(20,to_date('22-02-2008','dd-mm-yyyy'));
end;
/
create table test_sub(vehicle number, value_date date,price number);
begin
insert into test_sub values(10,to_date('12-04-2012','dd-mm-yyyy'),2);
insert into test_sub values(10,to_date('08-01-2010','dd-mm-yyyy'),4);
insert into test_sub values(10,to_date('14-04-2014','dd-mm-yyyy'),6);
insert into test_sub values(10,to_date('06-08-1947','dd-mm-yyyy'),8);
insert into test_sub values(20,to_date('24-01-2014','dd-mm-yyyy'),10);
insert into test_sub values(20,to_date('20-02-2006','dd-mm-yyyy'),12);
insert into test_sub values(20,to_date('18-02-2004','dd-mm-yyyy'),14);
insert into test_sub values(20,to_date('28-02-2006','dd-mm-yyyy'),16);
insert into test_sub values(20,to_date('22-DEC-2007','dd-mm-yyyy'),19);
end;
/
I could write as below but I would like to know if there is a better way of doing it.
select bb.vehicle
,sub.price
,bb.value_date
from test_bb bb
,test_sub sub
where bb.vehicle=sub.vehicle
[code].........
View 8 Replies
View Related
Oct 29, 2011
I am working on an application that allows a user to create a KPI formula, and the app dynamically creates a view for this formula. However as we started using it we have found that we are encountering the ORA-10476 (divisor is equal to zero) often. I have read about options of using decode or case to prevent this but it doesn't cover all our options. As a result of the fact that the user can create any equation he wants , and it can be as complicated as they want ( 2 examples:
A/(B/C-D/E) - If C or E or (B/C-D/E) are 0 the whole select will fall
A/(B-C/(D-F)) - if (D-F) or ((B-C/(D-F)) is 0 then again the select will fall.
I have seen that in MS SQL they have an option of arithabort which allows the database to return the rows that don't fall under the "divisor by 0".
either by setting something similar to arithabort, or maybe a procedure to check an equation and return the arithmetical steps in order that athey are performed?
View 2 Replies
View Related
May 26, 2010
The below code gives me error (ORA-01476: divisor is equal to zero)
SELECT
((COUNT(DECODE(SUBSTR(A.ASSETNUM,6,3),'ACS','ACS',0,null))/COUNT(DECODE(SUBSTR(A.PMNUM,1,3),'ACS','ACS',0,null)))*100)
FROM WORKORDER A
WHERE TO_CHAR(A.REPORTDATE,'MON-YYYY') = :WO_DATE;
View 8 Replies
View Related
Oct 30, 2012
I have data
Type, Month, Amount
=========
4, 1, 43333.33333
4, 2, 43333.33333
4, 3, 43333.33333
8, 5, 16000
8, 6, 16000
8, 7, 16000
8, 8, 16000
4, 2, 100
4, 3, 100and
I want to group and SUM() equal amounts and find MIN(month) and MAX(month), so the output should look like
Type, MIN(month), MAX(month), SUM(amount)
=========
4, 1, 3, 130000
8, 5, 8, 64000
4, 2, 3, 200
View 2 Replies
View Related
Nov 1, 2011
I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.
DECLARE
conditionXML CLOB;
ls_xml_2 Clob;
ls_xml_3 clob;
ls_xml_4 Clob;
ls_xml_5 Clob;
ls_xml_6 clob;
ls_xml_7 Clob;
[code]...
View 1 Replies
View Related
Aug 25, 2011
pgit_policy is transaction table having producer code field.
pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.
I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.
update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')
View 4 Replies
View Related
Oct 22, 2010
how many rows certain tables have.
updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?
when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).
Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?
View 3 Replies
View Related
Sep 12, 2013
I have created the following trigger whcich will track all the column changes and insert the row in log table. here i have some doubt while substituting the cursor value.
create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);
[Code]..
are the below assignement of values will work ?
in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;
i want to take the coulmname from the cursor and assign tat to psuedo columns like :new.r.column_name .
View 8 Replies
View Related
Mar 17, 2011
DROP TABLE TESTING CASCADE CONSTRAINTS;
CREATE TABLE TESTING
(
DATAPERIOD DATE,
EMPLID VARCHAR2(20 BYTE),
B_OS_REASON VARCHAR2(9 BYTE)
)
TABLESPACE USERS;
[Code] ........
SQL> select * from testing order by 1;
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972
In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972 XXXXXX
View 5 Replies
View Related
Nov 8, 2011
this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.
My ultimate goal is to populate the "children" field with a count of children for each household id.
create table NON_GBC_Members nologging as
select distinct hcp.household_master_ID
from mrtcustomer.household_child_profile hcp
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
group by hcp.household_master_id
minus
[code]....
View 5 Replies
View Related
Aug 1, 2012
I need to update column of a table with +,- 70,000,000 records.
If I perform an update it lasts......too much and does not finish!
View 6 Replies
View Related
Jul 7, 2013
I am trying to learn Oracle and trying to use Visual Basic 7 to connect to an Oracle database, upload a text file to an empty table and then update a second Oracle table based on the info in the first table. I know you can do this with SQL*Loader, but how about VB? I did some research and found this posting: Re: Using Bulk insert or SQL Loader in VB6 I borrowed the code from there and altered it for my application (see below).
Dim con As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim rst2 As New ADODB.RecordsetDim rst3 As New ADODB.Recordset con.ConnectionString = "Provider=OracleDB;User ID=john;Password=doe;Data Source=TestDB;"con.Open 'open the text filerst.Open "C:OracleTestCombinedCombined.txt" 'rst2 is an empty recordset because TempTable is empty at this time.rst2.Open "select * from TempTable", con, adOpenDynamic, adLockOptimistic 'adding rows into TempTable.Do Until rst.EOFrst2.AddNew Array("PACKET", "DATERET"), Array(rst.Fields("PACKET"), rst.Fields("DATERET"))rst.MoveNextLoop rst.Closerst2.Closecon.Close
My second question is this - say I had a second Oracle table in the same DB called "MasterTable", with the same two fields as "TempTable" (PACKET and DATERET). I want to update the DATERET field in "MasterTable" with the DATERET field in "TempTable" ONLY when the "PACKET" field matches in both tables.
View 5 Replies
View Related
Oct 29, 2012
OS:Solaris
DB:10G
I have a situation where there are multiple records for a join criteria. I am trying to find a way to update a particular column for all the records returned by the join criteria. Example :
Table A
id number
1 1000
2 2000
3 3000
4 4000
Table B
id number
1 9999
1 9999
1 9999
1 9999
1 9999
1 9999
2 8888
2 8888
3 6666
3 6666
Result after update:
Table B
id number
1 1000
1 1000
1 1000
1 1000
1 1000
1 1000
2 2000
2 2000
3 3000
3 3000
update query ? When I use a update statement with where exists it errors out because the query returns more than one row for the join condition.
View 2 Replies
View Related
Jun 13, 2012
When running the query below, I receive this error: ERROR:ORA-01476: divisor is equal to zero.
Select Sum ( Decode ( ACCT_CLASS_NO
, 'TF', Round ( SUB_FUND_TNA_USD_IN_M, 2 )
, Null
[Code]....
how can I get around the ORA-01476: divisor is equal to zero error?
View 2 Replies
View Related
Apr 26, 2012
I have a table A, whose table structure is in the below format.
Table A
ID DESC VALUE
123 A 454
123 B 1111
123 C 111
123 D 222
124 A 123
124 B 1
124 C 111
124 D 44
Now i need to insert the data from this table to another table B, the sturcture of which is as below
Table B
ID A B C D
1234541111111222
124123111144
How do i frame a query to fetch data from table A and insert that into table B? I don't want to use max and decode combination. as it would return only single row for an ID. I need all the id's to be displayed.
View 1 Replies
View Related
Sep 5, 2011
I create the sample for master/detail form. In detail for prdcode,rate,qty,amount is there. When select prdcode it fetching prdcode,rate in a record and if you type the qty the amout will come based on formula(property) :qty*:rate.
It is available on screen. But when i store the data, in backend table the amount is be a null.
View 9 Replies
View Related
Feb 18, 2013
I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.
Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".
But however I am still facing the same issue.
Test case:
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID VARCHAR2(1),
STATUS NUMBER(9)
);
INSERT INTO EMPLOYEE VALUES ('A',1);
INSERT INTO EMPLOYEE VALUES ('B',1);
commit;
[code]....
Also, any alternate options (rather than using 2 triggers with temp table).
View 7 Replies
View Related
Oct 30, 2013
i m doin simple application, in which all data is retrieving from table in to form(all the
field are textbox and button on form) ename,dept,salary,dept_id etc.
i took one textbox field on form which is non table field
(no db field bt linked to another table..via LOV m retrieving value into this textbox which suppose dept_id ) and now after selecting one record from list box into textbox which is non table field.... after immdiately selecting i want to update one field into db field which all other form field contains...
so how to do this??which trigger i hav to use for this?
if :dept_id is not null then
begin
update dept
set loc =:emp.loc
where dept_id = :emp.dept_id;
forms_ddl('commit');
exception
when others then
null;
end;
end if;
it giving error.......if m using commit in this...i hav used post query trigger ...is dere
any other trigger hav to use
View 3 Replies
View Related
Apr 27, 2011
EBS version is 12.1.3. we need to update the e-mail address in per_all_people_f in Oracle EBS based on a trigger that will fire against a column in Oracle Internet Directory (OID). A trigger will be built against table ct_mail, column attrvalue. When a value is inserted or updated this value will use an api on the EBS database to update per_all_people_f.
Q => Is ct_mail the correct table to use in OID for email addresses? or can you confirm a different table?
Q => If Ok to use ct_mail as the table can you see any problems with using a trigger in OID?
Q => Finally is a database link between OID and EBS database the best way for the trigger to work?
View 2 Replies
View Related
Aug 9, 2012
I am trying to update all rows of 100 column of a table with '0'. The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3, etc. I tried using the below code but I am getting ora-06550 and ora-00927 error's.
begin
FOR i in 1..100 loop
UPDATE EMP_DETAILS SET EMP_'||i||' =0
END LOOP;
COMMIT;
END;
View 6 Replies
View Related
Feb 19, 2010
I want to use java-script to open a prompt to see if the user wants to change the date of a work order (Located in Work_order_info called "opened_date")
What I have so far:
var n = prompt("Is the checklist starting now? (Type either y or n)", "y or n");
if (n == "y" || n == "Y"){
alert("The opened date has now been changed!");
}else if (n = "n" || n == "N"){
alert("The opened date has not been changed!");
}else{
alert("You needed to type either n or y, nothing else!");
}
I'm not entirely sure how to tell the table to change the opened_date to sysdate. I'm sure it's really simple >.
View 2 Replies
View Related
Jun 28, 2011
When I run the code below It runs very Long. It updates SUSR5 in the TEMPTABLE3 that has 112000 records. If I Change it when c>m to 2 to test. It runs very fast. The value for m is always between 10000 and 12000. That How many times it must loop to update the correct records.
DECLARE
a VARCHAR(50);
c NUMBER:= 1;
m NUMBER;
[Code]....
View 23 Replies
View Related
Sep 14, 2007
I have set up a view that pulls news & events records.
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.category, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType
FROM(so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
[Code].....
For events I want only the events that have a p.startDate equal to today. I have tried setting that line up a few different ways.
p.startDate = sysDate didn't provide any records for events
p.startDate = TO_CHAR(sysDate, 'MM/DD/YYYY') freaked the system out and nothing appeared for either parts of the union
how to output only events that are for today?
View 3 Replies
View Related