SQL & PL/SQL :: How To Find Rows With Certain Values And Replace Them In The Same Script
Jun 17, 2010
I have a large table with a few billion rows. I need to find ITEM_NAME values in ITEM_MASTER table containing " " (spaces) and replace them with other values. This has to be done within the same script. The replacement values could be any characters or numbers, but not spaces.
The whole reason for this table is testing big databases, so it does not matter what it contains. The reason I need to replace them is that this table will be dumped to a space delimited flat file and spaces within values are not acceptable.
I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.
I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
I have one emp table, in which i have sal, ename,empno etc columns. Now i am trying to replace each digit of salary with '*'. like if salary is 10000(it has five digit) then it will be replace by *****, but unable to do so. I don't how to achieve this, can we use translate or replace function in this.
Well i have achieved this by using case statement but where hardcoding is done...see the case below:-
select substr(ename,1,8),case when length(sal)=2 then '**' when length(sal)=3 then '***' when length(sal) = 4 then'****' when length(sal)=5 then
FUNCTION normalize(str IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN TRANSLATE(LOWER(str), 'äàáâãăāåąæčçðďéèëêěĕėęğģġîĭïīìíłļľŀñńňņöóòôõσøőřśŝšşţüúùûǔųūůŵýÿżźžżαβßγδεζŋηικλμµνξπρσςτυφω', 'aaaaaaaaaaccddeeeeeeeegggiiiiiillllnnnnoooooooorsssstuuuuuuuuwyyzzzzassydeznniklmmnxprsstufo' ); END;
I'm tired to add missing characters in this list...
What I would like is that all characters with an accent or diacritics should be replace by their "base" letter (ë -> e) and Greek letters to be replace by their corresponding values (ω (omega) -> o)
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
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'd like to get all rows for one particular id. I have data stored in two different tables. I'd like the values to be "denormalized" such that for each unique date I want the values(washer_val, widget_val) for each id from each table to be displayed(or null if no data for that date).
I've created two different example views("tables") of my data with a "with" statement(washers, widgets). I tried to denormalize each set of data (washers_val, widgets_val) and then get all the unique dates (unique_dates). I then join the "views" and display all possible rows with their data. This is the type of output I want BUT is there an easier way to achieve it??
with washers as (select 1 id, trunc(sysdate) event_date, 1 val from dual
I want to get the values and put them into html template since i want to configure mime settings. My table's name is rawticket_voip and it has 150.000rows and 20 columns so instead of COL1, COL2 what should i write there? How can i get the spesific row's and column's value and how can integrate loops in this html?
I have table called test script for table is given below
create table TEST ( col1 Number );
[Code]...
For these values Query is not returning values(3 and 4). So i want generic query to get this result. I am working on it but not able to generate proper query.
TABLE_A ------------------------------ ID DEPT CRS ------------------------------ 1 CS CS_100 2 SCIENCE SCI_150 3 MATH MATH_400 4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
-------------------------------------------- NO DEPT CRS -------------------------------------------- 1 MATH MATH_400, MATH_550, MATH_230 --------------------------------------------
instead of., -------------------------- NO DEPT CRS --------------------------- 1 MATH MATH_400 2 MATH MATH_550 3 MATH MATH_230 ---------------------------
I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.
For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.
I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.
Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.
I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0
How do I limit my query to show the information from REQUEST but only where the all of the wips associated between REQUEST and WIPS are not in the SHIPPING table. For example, the SHIPPING table has all of the WIPS that have been shipped, I only want to show the REQUEST rows where all of the WIPS have not shipped.
In literal terms, I'm interested in two fields, I'll call them Field A and Field B. I want to find all situations where a single unique value of Field A has both values Z AND X in Field B (not either or, but both together).
To go into some detail -
I need to make a query that finds ONLY occurrences where one employee id has a certain set of values together (without going into specifics, I'll say PermissionA, PermissionB, PermissionC). I can easily make a query that returns all user id's and all permissions belonging to them, and I can use criteria to filter the results to Permission A B and C only so as to exclude other permissions from getting returned (since there are hundreds),
however my objective is to get ONLY results where the same employee ID has all of those permissions (not just any one or two of the three). However, I don't want to have any criteria that limits the employee ID (I want to search all employee id's, and get a list of those with permissions a and b and c, not just any combination thereof, but all of them). I'm currently able to organize the output using a pivot table by employee id > role,
so that I can easily look at each employee and the roles they have, but I want to undertake a project that will involve searching a much larger number of employees (a # that makes it impractical for me to look through the list, I need to have a query that limits the results to the combinations that I'm looking for, as in this example - permission a b and c together).
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 am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.
with sample_table as ( select 'John' name,'city' ValueType,'Toronto' Value from dual union all select 'John' name,'city' ValueType,'Vancouver' Value from dual union all select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all select 'Susan' name,'age' ValueType,30 Value from dual union all select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all
[Code]...
NAME VALUETYPE VALUE ----------- ------------- ------------ John City Toronto John City Vancouver Susan City Toronto Susan City Seattle Susan Age 30 Susan City Atlanta David City Chicago David age 35 David Status married David City Dallas
The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.
I need the sql syntax that would return this result set.
