SQL & PL/SQL :: New Values To Come In A Single Row Of Table
Oct 12, 2010
Below is my trigger:
CREATE OR REPLACE
TRIGGER AR_BANDS_TEST
BEFORE INSERT OR DELETE OR UPDATE ON FEE_BANDS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
[code]...
I want the old and new values to come in a single row of the table while inserting in trigger like below:
OLD_FEE_RU OLD_FEE_BANDS_ID OLD_RATE_NUMBER OLD_RATE_PERCENT NEW_FEE_RU NEW_FEE_BANDS_ID NEW_RATE_NU
---------- ---------------- --------------- ---------------- ---------- ---------------- -----------
TESTALL 1 0 1 TESTALL 1 0
View 7 Replies
ADVERTISEMENT
Sep 10, 2012
CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));
INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad
2, bb
I used:
select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
View 6 Replies
View Related
Oct 5, 2011
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
View 5 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
Sep 1, 2010
I am using forms 10g...
I have 2 blocks,in one i insert student_id and when i click on the button i should get all his courses in the other block...
BUT.. when the student has more than 1 course I get this:
when button pressed trigger raised unhandled exception ORA-01422
Here is the button
begin
select TEST_STUDENT_INFO.stud_name,TEST_STUD_CRSE_DETAIL.crse_no,
TEST_COURSES.crse_name,TEST_STUD_CRSE_DETAIL.crse_type,TEST_STUD_CRSE_DETAIL.crse_type_desc,
TEST_COURSES.crse_time
INTO :stud_name,:crse_no1,:crse_name,:crse_type1,:crse_type_desc1,:crse_time
from TEST_STUDENT_INFO,TEST_COURSES,TEST_STUD_CRSE_DETAIL
where (TEST_STUD_CRSE_DETAIL.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_STUDENT_INFO.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_COURSES.CRSE_NO = TEST_STUD_CRSE_DETAIL.CRSE_NO)
and (TEST_STUD_CRSE_DETAIL.CRSE_TYPE = TEST_COURSES.CRSE_TYPE);
end;
but if the student has ONLY ONE course it WORKS FINE!
View 4 Replies
View Related
Feb 19, 2011
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).
View 1 Replies
View Related
Dec 31, 2012
Version oracle 11g
Is there a way to avoid multiple UNION statements and get statitc values in single SQL?
select 'Week1' as term from dual
UNION
select 'Week2' as term from dual
UNION
....
....Some functionality like selecting from dynamic table column-rows.... like .. "select term from table(term(rowvalues('Week1', 'Week2'...'Week5')) from dual" or something like that
View 4 Replies
View Related
Oct 4, 2012
I have a user requirement where they want to be able to select multiple values for a single parameter that will act as a filter for a report. The possible values for a parameter can come from almost any master data Oracle eBusiness table. I don't want to just present the user with a LOV where they search down a list selecting what they want. That wouldn't be very practical for selecting part numbers from 1000's of rows. I also want the user to easily see what values they have selected. I thought about providing a button that would navigate to a multi-row form where they could record their values and use those values in the where clause of the SQL for the report, but that is a somewhat complex solution I have never tried before. how to do this. I'm working in the latest version 4 of APEX.
View 1 Replies
View Related
Dec 23, 2012
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
View 2 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Oct 28, 2013
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
View 6 Replies
View Related
Jul 19, 2013
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
View 4 Replies
View Related
Jan 24, 2011
Is there any limitations for the number of column defined inside the table?
View 3 Replies
View Related
May 21, 2011
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
View 1 Replies
View Related
Aug 31, 2010
I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
View 4 Replies
View Related
Nov 16, 2012
I am trying to load a single column into a Table. Below is the ctl file:
OPTIONS (ERRORS=10000)
LOAD DATA
TRUNCATE
INTO TABLE "ECOMM"."CC_ECOMM"
TRAILING NULLCOLS
(EMAIL_ID)
Calling loader:
sqlldr silent=ALL userid=username/password@DB control=/home/DEV/control/CC_ECOMM.CTL log=/home/DEV/log/Unsubs.log bad=/home/DEV/log/Unsubs.bad discard=/home/DEV/log/Unsubs.dis data=/home/DEV/files/CC_ECOMM.TXT
Though the count in the Table after load is the same as the input file, they are all NULL.
I tried adding FIELDS TERMINATED BY X'A' for new line and also FIELDS TERMINATED BY X'D' for carriage return. Both times bad file was created and the records that were loaded were again NULL.
The input file has a list of emails:
iatraveler2008@aol.com
iaz65@aol.com
2blue2brown@comcast.net
2c3mwilson@embarqmail.com
abigailolschan@comcast.net
imisskoco@aol.com
I tried FIELDS TERMINATED BY X'10' and FIELDS TERMINATED BY X'13' too for new line and carriage return respectively. This time there were no bad file created, but the Table has Null values.
Log File contents:
Number to load: ALL
Number to skip: 0
Errors allowed: 10000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK, ERRORS and DISCARDS
[code]....
View 4 Replies
View Related
Mar 6, 2010
I have one table called posh, which has about 50,000 ids (millions of rows) so far. It is currently at about 50 gb. So each series of rows for a particular id is about 1 mb.
CREATE CLUSTER posh_cluster (id int)
SIZE 2M
STORAGE (initial 2000K next 2000K)
PARALLEL 4;
CREATE INDEX posh_cluster_index ON CLUSTER posh_cluster;
[code].......
According to the Oracle documentation size is "the average cluster key size", I am not really sure what that means. Is this the 1mb in posh table space I am using for each series of rows for an id, or the space used for the index? Secondly, the storage parameter for 'CREATE CLUSTER' should be what?
View 11 Replies
View Related
May 27, 2010
How can i copy a single column from one table to another. Table 1 has a column with data in it, table 2 has the column but is empty, i want to copy data for a single column from table1 to table 2. By the way these table have multiple columns.
View 8 Replies
View Related
Apr 30, 2010
I am running 3 for loops in continuation. These loops retrieve me 63 values. I need to insert 1 single row in a table and then exit from the same. Moreover when i run the same loop again I need to insert 2nd value fo the second row in the table. The table is having a unique key which does not allow me to load redundant data.
View 2 Replies
View Related
Dec 11, 2011
how to convert a single 'ragged' table like:
MAIN_KEY SUB_KEY DATA
ABC 1 A
ABC 2 B
ABC 3 C
DEF 1 X
where:
MAIN_KEY and DATA are indeterminate, and
SUB_KEY = 1 .. n [always starts with 1 for a given MAIN_KEYD]
into:
MAIN_KEY SUB_KEY DATA
ABC 1 A
ABC 2 B
ABC 3 C
ABC 4 {null}
ABC 5 {null}
DEF 1 X
DEF 2 {null}
DEF 3 {null}
DEF 4 {null}
DEF 5 {null}
in the case where n = 5?
View 6 Replies
View Related
Sep 2, 2011
i need a query for update..the logic is
i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.
View 2 Replies
View Related
Nov 15, 2012
Is it possible to create a table without any single column in it?
View 1 Replies
View Related
Oct 29, 2012
{code{
i want to apply merge stmt on single table.
CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');
[Code]....
My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record
View 3 Replies
View Related
Mar 16, 2013
I created a single table hash cluster like this :
create tablespace mssm datafile 'c:appmssm01.dbf' size 100m
segment space management manual;
create cluster hash_cluster_4k
( id number(2) )
size 8192 single table hash is id hashkeys 4 tablespace mssm;
-- Created a table in cluster with row size such that only one record fits one block and inserted 5 records each with a distinct key value
CREATE TABLE hash_cluster_tab_8k
( id number(2) ,
txt1 char(2000),
txt2 char(2000),
txt3 char(2000)
)
CLUSTER hash_cluster_8k( id );
[code]....
If I issue the same query after creating unique index on hash_cluster_tab(id), the execution plan shows hash access and single I/O (cr = 1).Does it mean that to have single I/o in a single table hash cluster, we have to create unique index? Won't it create additional overhead of maintaining an index?
What is the second I/O needed for in case unique index is absent?
View 25 Replies
View Related
Nov 19, 2010
i WANTED TO SIMPLIFY THIS CODE SO THAT I NEED NOT DO ALL THESE manipulations but still get the result of
UNI_CUG_SITE,
Create Table CUG_SITE compress nologging As
Select C_Key, S_Key From A
group by C_Key, S_Key
/
commit
/
--Creating a table for all Single Sited Customer ---
[code]........
View 5 Replies
View Related
Mar 21, 2012
I have a employee table with the below format
EMP
EMPID DEPID
1A
2A
2B
1B
4A
5B
5A
6C
7A
8D
Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.
I need the output as below (many to many I.e. cross referencing)
EMPID DEPIDGroup
1A A1
2A A1
2B A1
1B A1
4A A1
5B A1
5A A1
6C B1 -- new group as the emp and dept are not mapped previously
7A A1
8D C1
View 1 Replies
View Related
May 25, 2012
How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
View 1 Replies
View Related
Jan 27, 2012
Actully i am updating two table of data.. but below error message came..
update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
View 10 Replies
View Related
Jan 23, 2013
we are using oracle 11.2.0.3 on linux platform and was wanting to know if there is a way to backup just a single table partition using rman? If so does the database need to be running in archivelog mode in order to do this with the database online?
This is a database that has one single huge table that is partitioned by day and we are attempting to export out daily partitions using datapump export so that we can archive them to tape but the disk system we have is not able to keep up and the export of roughly 128gb is taking over 8 hours and is failing because the undo is aging out. We would like to bypass writing the partitions out to disk and just stream them to tape if at all possible?
View 5 Replies
View Related