I have the following table and data , So i want to update this table(word column) with words that has same soundex with one word of them, the word must be that has the long length if the two words has same length So, update by any of them.
drop table t1;
create table t1 (id number(10) ,word varchar2(100));
insert into t1 values(1,'Londn');
insert into t1 values(2,'Egypt');
insert into t1 values(3,'London');
insert into t1 values(4,'Gorgey');
insert into t1 values(5,'Michal');
insert into t1 values(6,'Michel');
insert into t1 values(7,'London');
insert into t1 values(8,'Egpt');
insert into t1 values(9,'Londan');
insert into t1 values(10,'Gorgy');
insert into t1 values(11,'Gorge');the results must be as following
id word
1 London
2 Egypt
3 London
4 Gorey
5 Michal
6 Michal
7 London
8 Egypt
9 London
10 Gorey
11 GoreyNote: this is the results of soundex
In the table the names are having diffrent spaces for one name only one space between the words,for another name two spaces between words,for other names three spaces between the words.
I want to update with single space how can we that.
I am giving the data as follows.
CREATE TABLE student(sname VARCHAR2(30));
INSERT INTO student VALUES('PRAKASH BABU'); INSERT INTO student VALUES('RAJESH KUMAR'); INSERT INTO student VALUES('POORNA CHANDAR'); INSERT INTO student VALUES('ANIL RAJ'); INSERT INTO student VALUES('ABHI RAM KONA'); INSERT INTO student VALUES('SRI TEJA MEDI');
Like this the table contains millions of rows.
I want to update the names like this.
PRAKASH BABU RAJESH KUMAR POORNA CHANDAR ANIL RAJ ABHI RAM KONA SRI TEJA MEDI
Is there any alternative of Soundex function. I have two table of city. I want to compare city of both table. Now problem is that one city name is 'HANGO'. In table A City is feed with spelling 'HANGO' and other it is spelled 'HANGU' OR 'HUNGU'. Similarly there are many cases. I used Soundex function but it is not working perfectly.
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
I have a table which has columns like First_Name,Last_Name and Display_Name. Now the every entry in the table have first name and last name populated .
I would like to populate the display name based on the format Display Name = First Name+ " " + Last Name.
For eg. If the first name John and last name is Doe , then the display name should be John Doe.write such a queries which dynamically picks up all the rows and populate the display name.
ITEMNUM STORELOC lastyear currentyear AM1324 AM1 need sum(quantity) here need sum(quantity) AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values: GLOBETROTTER: agent has visited five or more different locations in the course of his missions ROVER: agent has visited between one and four locations SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are agents table: agent_id, location_id targets table: target_id, location_id missions_agents table: agent_id, mission_id missions_targets: target_id, mission_id missions table: mission_id, location_id
My code is
DECLARE status AGENTS.TRAVEL_STATUS%TYPE; contact AGENTS.CONTACTS%TYPE; CURSOR loc_cur IS SELECT a.AGENT_ID id,
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
Table LEAVE Column Type Null Description APP_NO Number(6,0) Not Null PK Leave Application Number ECN Number(6,0) Not Null FK Employee Code Number APP_Date Date Not Null Date of Application From_Date Date Not Null Date from which the leave starts TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved Remark VARCHAR2(100) Nullable Reason to be put if status is rejected [code]....
What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.
For example if 3 rows are inserted in the LEAVES table as INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');
Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'
I tried with the following trigger, but is compiling with a warning (not showing what the warning is)
CREATE OR REPLACE TRIGGER leaveentitle AFTER INSERT ON LEAVES FOR EACH ROW BEGIN UPDATE LEAVEENTITLE LVE SET LVE.SL_USED = SL_USED+(CASE WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED' THEN :NEW.NO_OF_DAYS SL_UNUSED=SL_ENTITLED - SL_USED ELSE 0 END), [code]....
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].....
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST ( C VARCHAR2(2000 BYTE), B NUMBER, A NUMBER );
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP :M_END_YN := :M_END_ALL; [code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN :M_END_ALL := 'N'; END IF;
--code written on button to update those values which are checked.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP IF :M_END_YN = 'Y' THEN [code]......
I tried to convert numbers to words, it shows the below error.
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
TO_CHAR(TO_DATE(2447834,'J'),'JSP') --------------------------------------------------------------------------- two million four hundred forty-seven thousand eight hundred thirty-four
SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL; SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string
I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :
I have a table with the following column and data is like this.
SQL>CREATE TABLE test ( column1 varchar2(50)); SQL>INSERT INTO test VALUES('ABC XYZ'); SQL>INSERT INTO test VALUES('MNO PQR'); SQL>INSERT INTO test VALUES('ABCD ABC'); SQL>INSERT INTO test VALUES('PQR MNOP');
When integrating your product with third-party APIs I've repeatedly come into conflict with PLSQL and Oracle's reserved words list. For instance while integrating the clickatell SMS Message Transmission notification API there are two variables they send.
[URL]......
How do you propose PLSQL handles these requests without using Apache to rewrite the variable names, or using the three and four variable pslql variable collection methods.
I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.
select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');
Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.
search words : one two
one is less than two -> match two is greater than one -> match onetwo is union of two numbers -> match onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word one is less than three -> not a match as two is not present.
I used to have two tables to store details of PROPERTIES e.g UNIT_COMMERCIAL , UNIT_RESIDENTIAL. I need to combine both of them into one table called UNIT. I moved the data to the new table, but now I am stuck how to update the values of the child table which called MARKETING.
---
Now, I have two tables:
MARKETING (ID NUMBER PK , OLD_UNIT_ID NUMBER FK ...) UNIT (NEW_ID NUMBER PK , OLD_UNIT_ID NUMBER ... )
I need to update the value of OLD_UNIT_ID in Marketing table to be equal to NEW_ID in the table of UNIT. As you can see the values of OLD_UNIT_ID in both tables are the same.
I used this statement
update ( SELECT distinct M.UNIT_ID , U.OLD_ID , U.ID FROM MARKETING M INNER JOIN UNIT U ON (M.UNIT_ID = U.OLD_ID)) set unit_id = idBut I got this error:
ORA-01732: data manipulation operation not legal on this view??
I am trying to delete duplicate or repeating words in a string using regular expressions (regex) and an Oracle database.I have Googled quite a bit on this topic and was unable to track down something that fit the bill or remotely came close to paying the tip.
A pattern would be: word word start with newThe final string should look like this: word start with new Basically all the strings begin with the two words repeating.
