HAVING Statement From Query Removes All Rows?
Jun 21, 2012
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
[code]...
View 1 Replies
ADVERTISEMENT
Dec 14, 2010
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?
View 6 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Jun 20, 2012
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.
View 9 Replies
View Related
Apr 6, 2010
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.
View 10 Replies
View Related
Aug 15, 2013
I have a table which stores Employees and their Phone numbers. Each employee can have multiple numbers e.g.
Employee, Number
Adam, 123
Adam, 456
John, 123
John, 456
I am trying to write a select statement that will split the rows into columns and group by each employee e.g.
Employee, Number1, Number 2
Adam, 123, 456
John, 123, 456
View 19 Replies
View Related
Aug 10, 2010
Can I have all the rows in a table updated at once in the merge statement?
MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN
UPDATE ....... I want to update all the other rows at once..
View 24 Replies
View Related
Jul 24, 2009
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.
View 1 Replies
View Related
Feb 16, 2011
The requirement I have is :
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.
View 1 Replies
View Related
Aug 20, 2013
create table temp_tst
(
FILENAME VARCHAR2(200),
EDITED_BY VARCHAR2(50),
EDITED_TO VARCHAR2(50)
)
[code]....
Can I write a single update statement to update filename column replacing "_tst" with "_check"?
View 1 Replies
View Related
Jun 18, 2012
The following query take lot of time when exectued, even after I drop the indexes, is there a better way to write the following query?
DELETE from pwr_part
where ft_src_ref_id in (select ft_src_ref_id
from pwr_purge_ft);
--Table:pwr_part
--UIP10371 foreign key (FT_SRC_REF_ID, FT_DTL_SEQ)
[Code]...
Explain Plan:
Description Object owner Object name Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 224993 5492829 395483688
HASH JOIN RIGHT SEMI 224993 5492829 395483688
INDEX FAST FULL SCAN PWR_OWNER PWR_PURGE_FT_PK 43102 23803770 142822620
PARTITION HASH ALL 60942 27156200 1792309200
TABLE ACCESS FULL PWR_OWNER PWR_PART 60942 27156200 1792309200
View 6 Replies
View Related
Jul 1, 2010
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.
View 3 Replies
View Related
Sep 15, 2013
When I am executing merge statement I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
MERGE INTO TAN_LIST t
USING (SELECT * FROM (SELECT row_number () over (partition by TANNO order by null) rn,
dno,
TANNO,
SOL,
[Code]....
The query is fectching below data.
SELECT dno,TANNO,SOL,DESC,class,ct_dt,tcost
FROM MAT_LIST;
DNOTANNOSOLDESC CLASS CT_DT TCOST
63007565ADclass A A12345 08/28/131
[Code]...
Intially thers is no records in the TAN_LIST table. When I run the merge statemnt I am able to insert all 15 records into that table.
When I run the same merge statemment second time I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
So that I have used partition by cluase in the slect statement and I am able to resolve the error.
But it's inserting only 14 records not all 15. How to process all 15 records without the error..
View 16 Replies
View Related
Aug 25, 2010
Is there any way i can Get how many rows are processing with UPDATE statement while the Update statement is still running.
View 2 Replies
View Related
Aug 12, 2010
I have used a decode statement in my query instead of using select * from <table_name> where value in <>
Instead I have used decode(value,<value1>, <value2>)
Which would be a better performance code?
View 5 Replies
View Related
Aug 31, 2011
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)
[code]....
View 11 Replies
View Related
Nov 18, 2011
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
[code].....
View 12 Replies
View Related
Sep 28, 2011
I'm unable to create a select statement which calculates & brings the below fields :
- Act_Net_Last_Month (LastMonth)
- Act_Net_Same_Month_Last_Year (LM_LastYear)
My Table :
Acc_ID - Period_Name - Period_Year - Act_Net
000044 - Aug-2011 - 2011 - 4493
000044 - Aug-2010 - 2010 - 4300
000044 - Jul-2011 - 2011 - 4389
000044 - Jul-2010 - 2010 - 4266
Example :
Acc_ID - Period_Name - Period_Year - Act_Net - LastMonth - LM_LastYaer
000044 - Aug-2011 - 2011 - 4493 - 4389 - 4266
View 7 Replies
View Related
Nov 19, 2012
I have following queries:-
#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:-
DB_NAME ARCH_MOD INV_OBJ INV_IDX INV_TRG B_JOB BLK_CRP
---------- -------------------------------------- -------------------------------------- ---------- ---------- ---------- ----------
PROD NOARCHIVELOG 0 86 6 3 0
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)
View 7 Replies
View Related
Nov 16, 2012
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.
View 12 Replies
View Related
Oct 6, 2010
I am running one update statement which is running almost one hour still no response.
I would like to know either query is processing or hanging(suppose to finish the update within few minutes).
Is there any way or sql to find either the statement(my update query) is running or hanging.
View 4 Replies
View Related
May 27, 2011
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.
SQL> CREATE TABLE TEST1
2 (
3 UIN NUMBER(8) NOT NULL,
4 TESTING_ID NUMBER(4),
5 PFA_RESULT VARCHAR2(30 BYTE)
6 );
[code]....
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.
View 3 Replies
View Related
Aug 23, 2012
I am new to oracle. I am creating a view, where in the inner query returns below kind data.
column1 column2
-------------------
a y
a y
b y
b n
c y
d n
I want to extract all column1 values which always has "y" in column2.
In this case, output has to be
output
------
a
c
View 10 Replies
View Related
Sep 3, 2013
I have table desc xx_testName
Null Type
-------- -------- --------------
COL1 NOT NULL NVARCHAR2(100) COL2 NOT NULL NVARCHAR2(100) COL3 NOT NULL NVARCHAR2(100)
i am able to query select * from xx_test however if i query as select col1 from xx_test then it is giving error.
ORA-00904: "COL1": invalid identifier00904. 00000 - "%s: invalid identifier"*
Cause: *Action:Error at Line: 3,131 Column: 13
Let me know how to query NVARCHAR2 column and how can we put in WHERE condition ?
View 5 Replies
View Related
Aug 10, 2010
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> /
View 15 Replies
View Related
Jun 12, 2013
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.
Emp table
Col1col2primay
100101y
103104n
201105y
100101y
Dept table
Col1col2primay
100101null
103104null
000656null
Update query Result
Col1col2primary
100101y
103104n
View 16 Replies
View Related
Jul 27, 2012
I have following table -
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
);
[Code]...
Current output does looks like below
10 1 123 23-MAY-12 28-MAY-12
10 1 456 28-MAY-12 01-JUN-12
10 1 789 01-JUN-12
10 2 999 04-MAY-12 17-MAY-12
10 4 1123 22-JAN-12 27-JAN-12
10 4 1456 27-JAN-12 28-JAN-12
Device_id - Replaced_device_id - remove_date :: For group_id=10
e.g. for member_id = 1, device_id=123 got replaced by device_id=456 on 28-May-12
device_id=456 got replaced by device_id=789 on 01-Jun-12
So output should look like this
10 123 456 28-May-12 -- Replaced
10 456 789 01-Jun-12 -- Replaced
10 789 -- Active
Similarly there will be many groups, so it should list based on group_id
View 8 Replies
View Related
Feb 1, 2012
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?
View 1 Replies
View Related
Oct 25, 2010
I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.
UPDATE transaction SET loannum = lpad(loannum,12,'0')
View 15 Replies
View Related
Sep 14, 2008
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.
View 1 Replies
View Related
