In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join
CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS
[code]...
I have a table with:
1 million rows
average row length 200 bytes
50 columns
and this update statement
UPDATE mytable SET varchar2_4000_column = replace(replace(replace...300 times)
It looks at every row in the table (no WHERE clause) and does these 300 replace operations on this column for each row. Each replace replaces with a null so effectively it is removing strings. Much of the time these strings are not in the column.
This update statement takes 25 minutes and it is 98% CPU and 2% USER_IO time.
I figure that is what is taking all the time since it is a CPU bound statement. if rows in this table are persistent over time then tag rows with a flag to show which ones have already been processed and skip these next time around.
I have the following query but it is taking too much time because of the LEFT OUTER JOIN on HST table which is a huge table , is there an alternative to LEFT OUTER JOIN that can be used to optimize the code:
SELECT HST.COMP_CODE,
HST.BRANCH_CODE,
HST.CURRENCY_CODE,
HST.GL_CODE,
HST.CIF_SUB_NO,
HST.SL_NO,
SUM(CV_AMOUNT) CV_AMOUNT,
[code].....
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.
Is there any other way to compare data by sound.
I have a basic requirement to report rows from one table where the second table do not have the same record (basically same key).I understand that we could achieve this using not in or not exists clause. But I think using outer join probably be the simplest one. However, I could not achieve what I actually wanted using outer join
Test Case:
create table tab_1 (a number(1), b varchar2(20));
insert into tab_1 values (1,'one');
insert into tab_1 values (2,'two');
insert into tab_1 values (3,'three');
create table tab_2 (a number(1), b varchar2(20));
insert into tab_2 values (1,'one');
insert into tab_2 values (2,'two');
commit;
Now, I framed the query as
select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+);
/
YA XA XB
---- ---- ----
1 1 one
2 2 two
3 three
In this case, as there is no corresponding record in second table, the value of YA is showing as null as shown above.Hence, I changed the query as
select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+)
and y.a(+) is null;
/
YA XA XB
---- ---- ----
1 one
2 two
3 three
I dont understand why it is behaving in that way. I am rather expecting the output to come as:
YA XA XB
---- ---- ----
3 three
I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:
(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER )
OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))
If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.
the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
I have already done auto increment by making sequence and trigger. but now the problem is when i am trying to retrieve data from that table it returns all data perfectly but the primary key that is my auto increment value shows blank.I am facing this problem with join query, there 4 table left joined in my query. But when I remove join from my query then it shows that value.
But i need that value in my join query.So, what is the problem and what can I do?And other thing is when I apply this query in Oracle SQL Developer, it works perfect.
My Query:
return $this->db->query("select * from TBL_EMPLOYEE_BASIC left join TBL_EMPLOYEE_DETAILS on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_DETAILS.EMPL_ID left join TBL_EMPLOYEE_EDUCATION on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EDUCATION.EMPL_ID left join TBL_EMPLOYEE_EXPERIENCE on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EXPERIENCE.EMPL_ID where
[code]...
novice to SQL (Oracle 10g)
Am trying to write code for sollowing scenario:
Have 3 tables
table1 (campaignid,promoflag)
table2 (campaignid,projectid,campaigndesc)
table3 (projectid,promoflag,projectstart,projectend)
I am to update table 1 promoflag with value from promoflag in table3
Update table1
set promoflag = table3.promoflag
I would like to make sure only appropriate record is updated therefore want to use where clause condition but the primary key for table1 and table3 are different, the only link can be found on table2.
I want to use condition where table1.campaignid=table2.campaignid and table2.projectid=table1.projectid
Have used the following without success:
Scenario 1
Update table1
SET promoflag = table3.promoflag
FROM table1
inner join table2 on table1.campaignid = table2.camapaignid
inner join table3 on table2.projectid = table3.projectID;
Error at line 1 ORA-00933: SQL command not properly ended
Scenario 2 with real table/column names
Update UA_CAMPAIGNEXTATTR
SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID
WHERE
UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID;
Error at line 2
ORA-00933: SQL command not properly ended
It appears to get block with the 'FROM' statement (was underlined in red)
The procedure below give me result of html code into the output :
declare
l_page utl_http.html_pieces;
l_url varchar2(20500) default 'http://htc.katalog-mobilov.sk/mobilny-telefon/htc-s620/';
begin
l_page := utl_http.request_pieces( l_url);
for i in 1 .. l_page.count
loop
dbms_output.put_line( l_page(i) );
-- exit when ( i =6);
end loop;
end;
But I need to get on the output for example from 15th to 50 records from html code.But how to do that?
I am trying this function
instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0
i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2
I have a table called email that contains a field called email
I have a few records in the table that contain example@hotmial.com and want to change the hotmial to hotmail
I also have some that are line example@hotmail.co and I want to change those to hotmail.com
so - I want to do a substring search and replace.
can the replace do this for me in one select statement - how do I structure the sql to do this?
Years ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.
Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.
Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.
I'm thinking something that would work like:
UPDATE table SET column1=REPLACE(column1,oldstr,newstr);
Explain with the best example for difference between translate and replace function.
View 2 Replies View Relatedwhy the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL>
SQL> SELECT str,
2 e9,
3 REPLACE(str, '%E9', e9) replace,
4 regexp_replace(str, '%E9', e9) regexp_replace,
5 utl_url.unescape(str, 'UTF8') utl_url,
6 ascii(e9) ascii
7 FROM (SELECT 'Soir%E9e' str,
8 chr(to_number('E9', 'xx')) e9
9 FROM dual);
STR E9 REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é Soire Soirée Soirée 0
I cant compile & execute this function.
create or replace
FUNCTION get_branding_testing
RETURN r_brand
IS
BEGIN
CURSOR c1
IS
SELECT b.branding_code, c.name_desc
[code]....
I have the next SQL Loader file, and it is giving me some headches because the Replace instruction is not working. It does not replace the ' ' by NULL.
load data
CHARACTERSET UTF8
infile TABLE1.unl
BADFILE 'TABLE1.bad'
DISCARDFILE 'TABLE1.dsc'
insert into table GSCIS.table1
fields terminated by "|"
TRAILING NULLCOLS(
codl1 CHAR "REPLACE(:codl1, ' ', NULL)"
,col2 CHAR "REPLACE(:col2, ' ', NULL)"
,col3 INTEGER EXTERNAL
,col3 INTEGER EXTERNAL "DECODE(:col3, NULL, 0, :col3)"
)
I've got a slowly changing dimension table for products with some duplicate attributes -
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 02-JAN-13 423 MONKEY 5
2 03-JAN-13 04-JAN-13 423 MONKEY 5
3 05-JAN-13 08-JAN-13 423 MONKEY 4
4 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
5 01-JAN-13 08-JAN-13 378 BANANA 2
6 09-JAN-13 10-JAN-13 378 BANANA 3
The natural key should be PRODUCT_ID, ATTRIBUTE_1 and ATTRIBUTE_2. The table should therefore be recreated as follows:
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 04-JAN-13 423 MONKEY 5
2 05-JAN-13 08-JAN-13 423 MONKEY 4
3 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
4 01-JAN-13 08-JAN-13 378 BANANA 2
5 09-JAN-13 10-JAN-13 378 BANANA 3
I need function to pick the record from DB random manner.
For example
, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5
If user inputted value is 10 means, it should display one record between 1 to 10.
The HTML code for the bracket character '[' is & # 91; (without spaces). In my SQL, I would like to convert the word [you into & # 91;you. Is there a way to do that?
I know that replace() will work, but with that you have to supply a list of chars to replace; I
Need a code which prevents the User from entering more than 30 characters in a field. Although the variable can accept more than 30 characters. My requirement is to validate the field by restricting up to 30 characters.
View 6 Replies View RelatedI have two tables employee_master and employee_time. There could be situation that record from employee_master does not exist in employee_time.
create table employee_master(employee_id number, employee_name varchar2(100));
insert into employee_master values(1, 'employee 1');
insert into employee_master values(2, 'employee 2');
insert into employee_master values (3, 'employee 3');
create table employee_time(employee_id number, project_id number, project_type varchar2(20), time_date date, hours number)
insert into employee_time values(1, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(1, 200, 'Billable', '02-Oct-2010', 9);
insert into employee_time values(1, 210, 'Non Billable', '03-Oct-2010', 10);
insert into employee_time values(2, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(2, 200, 'Billable', '02-Oct-2010', 9);
The requirement is to show all the employees from employee_master and with total billable hours and non billable hours, if not exist, show zero.The output will be:
Employee_ID Employee_Name Total_Billable_Hours Total_Non_Billable
1 Employee1 19 10
2 Employee2 19 0
3 Employee3 0 0
The question is to write a Left outer join query or to write a PL/SQL function which can return total rows if Employee_ID is supplied to it as a parameter
Query 1:
Select Employee_ID, Employee_name, sum(Billable), sum(Non_Billable)
From
(
Select a.Employee_ID, a.employee_name,
decode(b.project_type, 'Billable', hours, 0) as Billable,
decode(b.project_type, 'Non Billable', Hours, 0) as Non_Billable
from employee_master a
left outer join employee_time b on a.Employee_ID=b.Employee_ID
)
Group by Employee_ID, Employee_Name
Query 2:
Select Employee_ID, Employee_Name, func_billable(Employee_ID) as Billable, func_non_billable(Employee_ID) as Non_Billable
From Employee_Master
Which query is good from the performance perspective? In real situation the employee_time is a very huge table.
what the following output of the oracle function "dump" means?
query: select dump(:new.messaging_state) from dual
output:
Typ=2 Len=1: 193
I need this debug output because I want to know which value the program wants to write in the columns messaging_state.
The update fails because of a parent Key violation.
Here table - tac has
row1
-----
X
X
X
B
[code]...
I want ouptut like using sql query with out using dense_rank function,
row1 row2
X 1
X 1
X 1
B 2
[code]...
What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc.
View 49 Replies View RelatedI have a report which comes from a table-function. This produces hundreds of lines of output which the user would like to be able to select and then export just the subset of lines.Looking at the Forum's (this article and this one) this seems possible if you are working with a table where you can re-query the original dataset with just a specific rowset required however as the table-function is generating output on the fly it is hard to rerun the query to reselect the same output for redisplay. Also most of the reports are working with interactive reports - whilst this is just static output. Is there a way to redisplay the information which has already been shown in an filtered way with check boxes?Is there then a way to have hidden fields which are not shown when the check boxes are selected but which can be shown in the "filtered" view? I am using Apex 4.0 at the moment on an Oracle 10g instance.
View 0 Replies View RelatedI have a question on Round Function
Round(###.###,1)
Will the round function output depend on 2nd digit after decimal point also or not?
I have 70 materialized views where I need to replace the FROM SCHEMA1 TO FROM SCHEMA2...To quickly to do the fix for all the 70 views..
SELECT REPLACE (QUERY, 'schema1', 'schema2' )FROM USER_MVIEWS ;
But It throws me an error that Number cannot be replace.
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies View RelatedI have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.