SQL & PL/SQL :: Updating A Table With A Select Statement?
Nov 8, 2011
this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.
My ultimate goal is to populate the "children" field with a count of children for each household id.
create table NON_GBC_Members nologging as
select distinct hcp.household_master_ID
from mrtcustomer.household_child_profile hcp
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
group by hcp.household_master_id
minus
[code]....
View 5 Replies
ADVERTISEMENT
May 26, 2011
i'm trying to use a collection in a select statement as a table, but i've got an error which i don't understand.
This exemple is very simple. I'm trying to validate the solution. The object will contain the result of a query from a big table, and i have 4 request to make on it. I don't want to select 4 times the big table to get the result, but i need the whole result to make my requests (intersect, minus and union)
CREATE OR REPLACE
TYPE zy_w AS OBJECT(
CODE_INFORMATIONVARCHAR2(4),
[Code]....
****************
Rapport d'erreur :
ORA-06550: Ligne 12, colonne 3 :
PL/SQL: ORA-00947: nombre de valeurs insuffisant
ORA-06550: Ligne 5, colonne 3 :
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
View 9 Replies
View Related
Sep 1, 2011
there is a possible to use a select statement on collection table?
View 1 Replies
View Related
Jun 6, 2010
In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.
But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------
IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------
In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .
SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID
how can I rewrite my query to the above value from the select into my insert statement..?
View 24 Replies
View Related
Apr 27, 2010
Is it possible to hold the data from select statement without temp table or materialized view or view in oracle?
because my DBA does not give access to create temp table.but we are selecting the records from 3 different sql statement.
Example: inserting in temp table
a) insert into temp select empno,ename,sal from emp where sal>4000
b) insert into temp select empno,ename,sal from emp where dept=40
c) insert into temp select empno,ename,sal from emp where comm is null
View 6 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Jun 15, 2010
I have two tables,
CREATE TABLE repos
(
rep_key VARCHAR(10) NOT NULL,
base_term VARCHAR(100) NOT NULL,
blt_key INTEGER NOT NULL
[code]...
gloss table has the unique set of base_term as in repos. BLT_KEY will be primary_key in gloss and foreign key in repos.
Data in gloss table
BLT_KEY BASE_TERM
1 base1
2 base2
3 base3
Now, I need to update the BLT_KEY in gloss to matching entries in repos. Can I do that in a update on select statement? like,
UPDATE repos
SET blt_key = (SELECT gloss.blt_key
FROM repos,
gloss
WHERE repos.base_term = gloss.abase_term) This throws subquery returns more than one row.
And the end of update the repos table should look like,
REP_KEY BASE_TERM BLT_KEY
M001 base1 1
M002 base1 1
M003 base2 2
M004 base1 1
[code]....
Also I need a single query which can update on select as the no of records to be updated are more than 90000 in repos. So two step process would slow down the process
View 13 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
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 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
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 Replies
View Related
Mar 22, 2013
simply select and works great:
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%'
Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2
if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?
View 4 Replies
View Related
May 5, 2010
I have a question about select statements, as I am new to them and don't know how to work all the commands yet.
I'm making a select statement that is about half right... it is shown below:
select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);
I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.
View 2 Replies
View Related
Jun 15, 2007
decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,
After executing this statement. I get negative values if cancel time is null
I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time
View 1 Replies
View Related
Aug 23, 2008
how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.
View 6 Replies
View Related
Sep 1, 2011
I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).
Ex:
Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;
Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?
View 12 Replies
View Related
Jul 5, 2011
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT
Location,
LEVEL,
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
View 7 Replies
View Related
Feb 11, 2011
I have the following tables:
create table lookups (code varchar2(20), amount number);
insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;
This returns
ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
View 4 Replies
View Related
May 6, 2011
Below is the query that I have:
Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
View 5 Replies
View Related
Apr 28, 2010
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
View 1 Replies
View Related
Jun 24, 2011
select GAM.FORACID,
SOL.SOL_DESC,
GAM.ACCT_NAME,
LHT.LIM_SANCT_DATE,
LHT.SANCT_LIM,
to_char( GAM.CLR_BAL_AMT,'9,99,999.99'),
[code]...
i am getting error like this
ERROR at line 9:
ORA-00911: invalid character
View 4 Replies
View Related
May 3, 2013
Execute Immediate on SELECT Statement
declare l_stmt VARCHAR2(1000);
begin
l_stmt := 'Select * from mytable' ;
EXECUTE IMMEDIATE l_stmt;
--- dbms_output.put_line( l_stmt);
end;
it is not returning any output. I have hardcoded the table name here where as in real time Mytable name will differ.
View 14 Replies
View Related
Feb 19, 2013
I was given a SQL query to develop another query that will need to do something similar. The query I was given I believe is a stored procedure. It is PL/SQL and has a SELECT statement that looks like
SELECT :var1 AS var1
FROM t1
WHERE
:var1 = 1
In the past I have seen where variables can be used in the WHERE clause like
SELECT c1
FROM t1
WHERE
c1 = :my_column_valueand this makes sense to me.
Usually :my_column_value is declared in the DECLARE block of the PL/SQL. In the query I was given, :my_column_value is not declared in any DECLARE blocks (maybe I wasn't given the entire code??). But even if it was, that would be even more confusing because in the DECLARE block it could be assigned one value and then set to another value in the WHERE clause???
The first query doesn't make sense to me and when I run it as plain SQL I get an error like "SP2-0552: Bind variable "var1" not declared. I am guessing that such a syntax is not valid for regular SQL?
The only thing I can gather that the first query does is something along the lines of
SELECT 1 as var1
so it will output a 1 for every row. Not always that interesting of a query, but it is needed sometimes, I know. But I still don't understand this type of assignment.
So what's going on here? I'd be happy to look it up and read about it myself, but I don't know what to search for.
View 5 Replies
View Related
Nov 12, 2013
i want a select statement to retrieve the last name added into the "ename" column .i mean :if i insert "jack" into "ename" column , i want to write a select statement to retrieve that last name while i do not know it is jack or any other name ?
View 2 Replies
View Related
Sep 14, 2013
How to use a function with DML operations in it in a select statement.
View 11 Replies
View Related
Apr 22, 2013
I am trying to find the best solution for the following problem
I have a table called HIERARCHY with 3 columns.
col1 col2 col3
------------------------
1
1 2
1 2 3
4 5
6 7 8
I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.
For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
However 4,5 and 6,7,8 are not valid as higher levels are not completed.
How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions?!
View 5 Replies
View Related
Apr 5, 2011
here is what i am trying to do: im as using oracle 8 with sqltool
i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;
but could not get it to work.
View 2 Replies
View Related
