The "HAVING" statement from the query below removes all rows where the "name" field starts with "Port" and it removes all rows where count is less than 11. What I want is to removes all rows where the "name" field starts with "Port" and the count for that row is less than 11.
SELECT deviceEventClassId "Event ID", deviceAddress "Device Address", name "Event Name", deviceHostName "Device Host Name", count( concat(deviceEventClassId, deviceHostName, name)) "Count"
FROM events
WHERE (deviceVendor = "Bilbo" and
is there any function in oracle which removes all HTML tags . our web pages uses rich text editor, when user enters text into editorand saves it, the database will have text with formatting ie all html tags. this works fine as long we display this text in web pages , but some time we have to provide reports to user just by running sql and ouptut in excel, in this case I want to strip out all html tags within the text .there is any function which can strip all HTML tags?
I have written trigger to satisfy the requirement, when i write row level it comes out with mutating error on table which is very obvious, i have change it to statement level which is fine but it doesn't satisfy the requirement.
I want to capture the id column , which rows got inserted or updated .
Is there any way to get the newly inserted or updated rows as in case of row level i can get it form :new.id.
I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select x,y,z from mytable@dblink
returns 788,324 rows
while select x,y,z from mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
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.
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3 (SELECT t1.c1,t2.c2 FROM TAB1 t1, TAB2 t2 WHERE t1.c1 = t2.c1 AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
how to write this query using all three tables in one query statement.
Result:
Input value of "GM" would return "GM@EMAIL.COM"
Input value of "GM" and "GMC" would return "GMC@EMAIL.COM"
If "GM" is supplied, then only return the "GM@EMAIL.COM" email.
If "GM" and "GMC" is supplied, then only return the "GMC@EMAIL.COM" email
Test Case : GM would return "GM@EMAIL.COM" email
Test Case : GM and GMC would return "GMC@EMAIL.COM" email
select email from email a, company b, sub_company c where (company = "GM" but no match found in sub_company) (company = "GM" and sub_company_name = "GMC" match is found in sub_company)
We've got a query which returns one row, but uses an IN statement. The IN statement links to more than one row in the subquery. When we use a combination of DISTINCT and an ANALYTICAL sum, the sum total is multiplied by the number of rows in the sub query. Remove the DISTINCT and we get a single value.
A simplified example of the problem is below.
I can't see how a query which returns a single row then returns multiple values with the addition of a DISTINCT. Removing the analytical sum also provides a single row, but we need this in the actual query we're running. So it seems to be some combination of DISTINCT, ANALYTICAL SUM and IN query is causing multiple values to be returned.
CREATE TABLE go_test_distinct1 (gtd_value NUMBER); -- Three identical values -- To replicate the three identical values returned by
#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've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:
UPDATE tbl1 SET tbl1.EMPID = (SELECT tbl2.EMPIDA FROM tbl2 WHERE LOWER(tbl1.EMAILCOL) = LOWER(tbl2.EMAILCOL2) ) WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156') AND EXISTS (SELECT tbl2.EMPIDA FROM tbl2 WHERE tbl1.EMAILCOL= tbl2.EMAILCOL2 );
But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.
I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.
I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.
select * from (select uin,testing_id,pfa_result from test1) pivot xml (max(pfa_result) as result for (testing_id) in (select distinct testing_id from test1));
[code]....
Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.
I have one issue with the converting columns into rows.
Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .
Orginal data is like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7698
[code]....
I have tried this , using UNION ALL method, But it is taking more time.
SQL> ED Wrote file afiedt.buf 1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno 2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB 3* WHERE addtional_mgr IS NOT NULL SQL> /
I have 2 tables with 3 common columns (Col1, col2,Primary). One common column name is primary (oracle key word).Around 2 lakhs rows to be updated and No indexes are used on these tables. I need to write an update query as shown below.
CREATE TABLE group_device ( group_id NUMBER (8) NOT NULL , member_id NUMBER (4) NOT NULL , device_id NUMBER (10) NOT NULL , install_date DATE NOT NULL , remove_date DATE );
DECLARE @MainTable TABLE (UniqueID INTEGER, Category VARCHAR(200), WeekDate DATETIME, VALUE INTEGER) INSERT INTO @MainTable VALUES(123, 'Shirts', '10/07/2011', 5000) INSERT INTO @MainTable VALUES(123, 'Shirts', '10/14/2011', 8000) INSERT INTO @MainTable VALUES(124, 'Pants', '10/07/2011', 4000) INSERT INTO @MainTable VALUES(125, 'Shorts', '10/14/2011', 8000) INSERT INTO @MainTable VALUES(126, 'Shoes', '10/21/2011', 9000); --select * from @MainTable; [code]...
The query works with all the CTEs up to the last select statement. Oracle does not support the OUTER APPLY statement, how should the last piece be written to make it work in Oracle?
I have seen lots of examples of using PIVOT to return an sql query where the rows have been converted to columns. All the examples I can find require you to have two columns with multiple rows, and also require you to know the data which is in one of columns to make titles for the new columns.
I need something a little different...I have a query using UNION which goes something along the lines of...
SELECT [get data from one place] UNION SELECT [get data from another place] UNION SELECT [get data from another place]
this returns the following... a_val ----- 100 200 300
query to return something like this... a_val1 a_val2 a_val3 ------ ------ ------ 100 200 300
The names of the columns don't really matter. And I dont want to create a new table in the database just for the result.
