I want to check to see if that string contains any of the values in:
select code from codes_table;
For example, if
select code from codes_table;
returns: code ----- AB LM NO PQ
then the query should return 'True'. Using the string above it would return 'True' because 'AB' from the string exists in the table codes_table. Pseudocode would look something like this I guess:
if the input string contains any of the codes in the field 'code' from table 'codes_table' then 'OK' otherwise 'No good!'
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 have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)
update eim_asset1 set emp_emp_login = (select login from s_user where row_id in (select row_id from s_emp_per where row_id in (select pr_emp_id from s_postn where row_id in (select position_id from s_accnt_postn where ou_ext_id in (select row_id from s_org_ext where row_id in (select owner_accnt_id from s_asset where owner_accnt_id is not null)))))
It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.
how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I'm trying to insert only a few columns (not all of them) from temp_ioi_010209 into mtl_system_items_interface. Both of these tables have more columns than just the 7 I'm specifying but everything I found under INSERT ALL here makes me think I'm doing it right. According to the DESCRIBE of mtl_system_items_interface the only non-nullable column is set_process_id and I'm specifying that one...
Why I'm getting the error "ORA-00947: not enough values"?
INSERT ALL INTO mtl_system_items_interface VALUES (process_flag, transaction_type, set_process_id, [code].......
I have one query i have create one table T1 in this table i have this three colm (EMPNO,ENAME,HIREDATE) and i have to insert raw from this two table T2 and T3 in T2 table colms are (EMPNO,SAL) and T3 table colm are (EMPNO,MGRID) so which query i have to run
I wanted to print 'null' when the column value is null. Actually, i am doing something like this
select empno||','||''''||ename||'''''||','||comm||','||sal from emp
It gives the following output for example 7369,'pointers',,200 If I use the above values to form a insert statement it throws an error. As 'comm' value is not there.
I wish to get something like 7369,'pointers','',200 or 7369,'pointers',null,200 from the above select query
note I dint copy paste the query exactly from my sql*plus session as I am away from my oracle machine
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
i'm executing a load test using the below statement:
INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501; COMMIT; EXIT;
I've been using the same for a few days without any error while now wehn i try to use the same i get below error:
SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500; insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500 * ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected.
I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.
I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.
All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?
Given below a block of code, this code compiled successfully on one DB but returns error on other.DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB.
I am creating the following two tables...no issues here: CODECREATE TABLE COURSE_SECTION ( Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key, Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE, Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
[code]...
The issue I am having is actually inserting data into the table: CODEINSERT INTO ENROLLMENT VALUES (100, 1000, 'A' ); INSERT INTO ENROLLMENT VALUES (100, 1003, 'A' );
[code]...
But I get an ORACLE error of
ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?
supplier_name = 'IBM' and supplier_type = 'Hardware' THEN
only 'North office' is being retrieved but how to get 'South office' also along with it.Is there a way to write multiple conditions in THEN clause of case stmt something like THEN 'North office' AND 'South office'.
I am trying to put transactions into 3 different "buckets" for each month of the year.
I am trying to figure out if the syntax for the "else" part of this code is correct... this will basically appear 12 times for each month of the year changing the value for month:
case when floor(sum(total_adjusted_net_amount)/100) < 0 and month in (01) then 0 when floor(sum(total_adjusted_net_amount)/100) > 29 and month in (01) and then 30 else when floor(sum(total_adjusted_net_amount)/100) and month in (01) end Groupin_01
I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc set ecdnv.ContTPRecCount = 1000 where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT') and ecdnv.detail_field_id=tc.default_category_id;
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc * ERROR at line 1: ORA-00971: missing SET keyword
I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..
#select name from v$database; #select log_mode from v$database; #select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID'; #select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID'; #select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID'; #select count(*) "Broken Jobs" from dba_jobs where broken!='Y'; #select count(*) "Block Corruption" from v$database_block_corruption;
i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-
I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
