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??
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 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!
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).
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
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.
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)
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)
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?
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.
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)
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
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?
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.
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.
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.
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
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?
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]........
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
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?
