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

SQL & PL/SQL :: Get Single Row From Multiple Duplicate Values Of One Field?

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

SQL & PL/SQL :: Select Multiple Values Into Single Column

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

Update Multiple Rows With Different Values In A Single Statement

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

SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?

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

Forms :: Multi Values For One Single Record (student_id)

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

Find Situations Where Single Unique Value Of Field A Has Both Values Z AND X In B?

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

PL/SQL :: Avoid Multiple UNION Statements And Get Static Values In Single SQL?

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

Application Express :: Report With Multiple Values For A Single Parameter

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

SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function

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

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

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

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

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

SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column

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

SQL & PL/SQL :: Max Columns In Single Table

Jan 24, 2011

Is there any limitations for the number of column defined inside the table?

View 3 Replies View Related

Forms :: Retrieving Values From Mater Table In Child Table?

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

SQL & PL/SQL :: Select Data From Test-1 Table Where ID Values In Table Exists In 2?

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

Loading Single Column Into Table?

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

SQL & PL/SQL :: How To Create Single Table Cluster

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

SQL & PL/SQL :: Copy Single Column From One Table To Another?

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

SQL & PL/SQL :: To Insert 1 Single Row In A Table And Then Exit From The Same

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

SQL & PL/SQL :: How To Convert A Single Ragged Table

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

SQL & PL/SQL :: Update Single Column In X Table

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

Create A Table Without Any Single Column In It?

Nov 15, 2012

Is it possible to create a table without any single column in it?

View 1 Replies View Related

PL/SQL :: Merge Statement On Single Table

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

Single Table Hash Clusters

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

SQL & PL/SQL :: Creating Table For All Single Sited Customer

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

SQL & PL/SQL :: Cross Referencing Query From A Single Table

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

SQL & PL/SQL :: Update Two Table Column In Single Query

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

SQL & PL/SQL :: Update Multiple Table In Single Query

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

RMAN :: Backup Just A Single Table Partition?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved