PL/SQL :: Selecting Rows Where All Values Not In Another Table?

Jul 18, 2013

This is my table design: 

REQUEST(R_ID,attr1,attr2)WIPS(R_ID,WIP,attr3,attr4)SHIPPING(WIP,attr5,attr6) 

How do I limit my query to show the information from REQUEST but only where the all of the wips associated between REQUEST and WIPS are not in the SHIPPING table.  For example, the SHIPPING table has all of the WIPS that have been shipped, I only want to show the REQUEST rows where all of the WIPS have not shipped. 

View 2 Replies


ADVERTISEMENT

Inserting Into A Table By Selecting Multiple Rows Into A Single Row

Jul 27, 2012

I have a flat file as source wherein I am getting values like

Comp_id, Comp_name, ISIN, column_name, column_value

The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.

E.g. of Feed File -

Comp_id, Comp_name, column_name, column_value

1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023

Target table structure

Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode

I need to insert the above data to a table by selecting data from above scenario.

View 10 Replies View Related

Precompilers, OCI & OCCI :: Using Pointer To Structure For Selecting Multiple Rows From Table

May 11, 2010

I am working on Pro*C and i have a requirement where i need to select all the rows from a table into a c - structure variable. Since i get to know the no of rows in the table which is getting selected only at run time, i need to create a pointer variable to the structure and i'll allocate the size to it based on the count of rows in the table using malloc or calloc.I tried allocating memory using calloc and it does not show any error. But when i when the exec select statement run it shows an error.

Statements i have used:
struct common *comp;
struct common_ind *comp_i;

comp = (struct common*) calloc(rowcount, sizeof(struct common));
comp_i = (struct common_ind*) calloc(rowcount, sizeof(struct common_ind));

exec sql at db1 select * into :comp indicator :comp_i from tab1;

Error i get :

Stop Error: -2112
Stop Error: -1012
Stop Error: -1012

View 2 Replies View Related

Selecting 100 Number Rows?

Jan 15, 2011

how can I select whole table in parts of 100 rows?

If I have primary key I can:

CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;

How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?

View 1 Replies View Related

Forms :: Selecting And Passing Values For A Query?

Sep 6, 2010

I have a form in which the user selects values above from a LOV and then selects a run query button which then uses the values selected in the LOV to refine the query and output the appropriate values.

What i have done in the pre-query trigger is: (the block i am querying is called cars where the block i am selecting values from the LOV is called selection)

:cars.make := :selection.make; (repeated for other fields)

this worked fine untill i introduced a from and to date in my form in which i passed the data through to the query like this:

declare
v_dates_between varchar2(1000);
begin
v_dates_between := 'reg_date between ''' || :selection.from_date || ''' and ''' || :selection.to_date;
Set_Block_Property( 'cars', DEFAULT_WHERE, v_dates_between ) ;

Now i have added the above bit of code the from and to date work fine, but when a car make is selected it still bring back every car make and not just the one selected.

is it possibly because i am setting the where clause in the block property? perhaps i need to now incoperate my other selections into this where clause? are the two blocks exclusive to eachother? and if i put the whole code into the block property I assume i will get problems. E.G. the user leaves a selection criteria blank (if they want to query all car models they wouldnt select one from the list) i assume the query would only return back values which contain no car model which would be 0 records.

View 6 Replies View Related

Performance Tuning :: Update Million Rows In One Table With Values From Another Tables?

Feb 15, 2011

I am trying to update a million rows in one table with the values from another tables.

Table being updated CI_ADJ_CHAR column CHAR_VAL_FK1
Table from which values will be used CK_ADJ columns (cx_id, ci_id)

The CI_ADJ_CHAR.CHAR_VAL_FK1 values match CK_ADJ.CX_ID and should be updated with the value CK_ADJ.CI_ID.

The CK_ADJ table has 1.3 million rows and both the columns have indexes defined. Table definitiuon mentioned below

The CI_ADJ_CHAR table has 14 million rows and will update 1 million rows and has an index on the ADJ_ID column but not on the CHAR_VAL_FK1 column.

View 1 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

PL/SQL :: Selecting Records From 125 Million Record Table To Insert Into Smaller Table?

Jul 17, 2013

Oracle 11gI have a large table of 125 million records - t3_universe.  This table never gets updated or altered once loaded,  but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads -  that will be updated with regard to when the lead is mailed and for other relevant information.  select records from this 125 million record table to insert into the smaller table. 

I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below.  Then use a second query that inserts into T3_Leads from this View V_Market.  This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause?    My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.   

CREATE VIEW V_Market  asWITH got_pairs    AS     (         SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */  l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no       ,      ROW_NUMBER () OVER ( PARTITION BY  l.address_key                                    ORDER BY      l.hh_verification_date  DESC                    ) AS r_num         FROM   t3_universe  e         JOIN   t3_universe  l  ON                l.address_key  = e.address_key             AND l.zip_code = e.zip_code           AND   l.p1_gender != e.p1_gender      

[code]....

View 2 Replies View Related

PL/SQL :: Selecting Table Name Under A Variable

Mar 21, 2013

correct this one.

declare
v_ename varchar2(10):='emp';
begin
for j in (select ename from v_ename)
loop
dbms_output.put_line(j.ename);
end loop;
end;

getting error v_ename table does not exists. i should use v_ename, as i dont know the table name.

View 4 Replies View Related

Selecting A Set Of Records From One Table

Aug 19, 2010

I'm selecting a set of records from one table, for example: ID, description and date. From this I'm only wanting the latest inserted row. I've used the max function on the date which is fine, however, there are some records that have had their description changed. This then returns two values for one ID, the max for the original description and the max for the changed description.

I'm getting:

ID |Description |Date
1 ABC 01/01/2010
2 XYZ 02/03/2010
2 XYZ1 03/05/2010

When I want:

ID |Description |Date
1 ABC 01/01/2010
2 XYZ1 03/05/2010

As ID 2 with XYZ1 Description is the very latest row for that ID.

This is an audit table so the ID appears on numerous rows as it a composite key with date.

View 1 Replies View Related

Selecting From Column Using RowID In Table?

Apr 24, 2012

I just want to know that "is it safe to select a columns using ROWID in a table?"

View 3 Replies View Related

SQL & PL/SQL :: Selecting Correct Record From The Table

Sep 26, 2013

I have a table with following structure:

CREATE TABLE ID_comments
(
ID CHAR(10 BYTE) NOT NULL,
S_COMMENTS VARCHAR2(255 BYTE),
P_COMMENTS VARCHAR2(255 BYTE),
C_COMMENTS VARCHAR2(255 BYTE)
);

For each Id, I can have multiple records.

Below is the insert script of one of the ID:

Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE','');
Insert into ID_comments values ('0813654254','','JR','');
[code].......

Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.

View 14 Replies View Related

PL/SQL :: Use Asterisk Along With Default Value While Selecting On A Table

Jul 18, 2012

Is there any way to use * along with the default value while selecting on a table...I need something as follows:

select *, 'JUNK' from table_name;

(or)

select 'JUNK', * from table_name;I know it will not work.

View 4 Replies View Related

Selecting Data From Table On Date Criteria

May 27, 2011

I am new to oracle and have an issue with selecting data from a table on date criteria.

select * from table1 t where t.DT BETWEEN TO_DATE(sysdate-1,'dd/mm/yyyy') AND sysdate

my understanding is that this should give me the data for just one day. but it is not.

What I want is, data for last 30days from today.

View 2 Replies View Related

SQL & PL/SQL :: Merge And Add Rows Values Into One?

Jun 4, 2012

CREATE TABLE emp
(
code NUMBER(4),
name VARCHAR2(15),
salary NUMBER(5),
ovt NUMBER(4),
sal_mon DATE,
dept number (3)
)

[code]....

9 rows selected.

Required Output is

CODE NAME SALARY OVT SAL_MON DEPTMerge Codes
---------- --------------- ---------- ---------- --------- ---------- -----------
1068 ALI 18500 2385 31-OCT-11 1021068,1793
1908 ASGHAR 17000 1900 31-OCT-11 1021908,1802
1960 ZAHID 16000 775 31-OCT-11 1021960,1952
1188 RAZA 9500 2910 31-OCT-11 1111188
1275 RAHIL 15300 3480 31-OCT-11 1111275,1176

Merge 02 rows of same department having sum(salary) <=20000. select those rows who have lower salary.

View 10 Replies View Related

SQL & PL/SQL :: All Rows With Denormalized Values

Sep 12, 2013

I'd like to get all rows for one particular id. I have data stored in two different tables. I'd like the values to be "denormalized" such that for each unique date I want the values(washer_val, widget_val) for each id from each table to be displayed(or null if no data for that date).

I'm not stating the exact requirements correctly

EVENT_DATEWASHER_VALWIDGET_VAL
9/12/20131, 2 1, 2
9/13/20132
9/14/2013 1

I've created two different example views("tables") of my data with a "with" statement(washers, widgets). I tried to denormalize each set of data (washers_val, widgets_val) and then get all the unique dates (unique_dates). I then join the "views" and display all possible rows with their data. This is the type of output I want BUT is there an easier way to achieve it??

with washers as
(select 1 id, trunc(sysdate) event_date, 1 val
from dual

[Code]....

View 10 Replies View Related

SQL & PL/SQL :: Getting The Rows And Columns Values

Sep 3, 2010

I want to get the values and put them into html template since i want to configure mime settings. My table's name is rawticket_voip and it has 150.000rows and 20 columns so instead of COL1, COL2 what should i write there? How can i get the spesific row's and column's value and how can integrate loops in this html?

'<html>
<table border="1">
<tr bgcolor="Light Blue">
<td>COL1</td>
<td>COL2</td>

[Code]...

View 4 Replies View Related

SQL & PL/SQL :: How To Get Concatenated Values Separated In Rows

Mar 1, 2012

I have input like below

ID | Name
--------------
1 | ABC, BCA, AAA, BBB (all in one column)
2 | ABC,DBA

and I want to get concatenated values separated

ID | Name
--------------
1 | ABC
1 | BCA
1 | AAA
1 | BBB
2 | ABC
2 | DBA

View 17 Replies View Related

SQL & PL/SQL :: Display Values In A Column Instead Of Rows

Mar 10, 2011

Consider the following (example)table.,

TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110

[Code]...

To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,

--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------

instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------

View 2 Replies View Related

PL/SQL :: Rows Into Comma Separated Values

Oct 30, 2012

I would like get rows into comma separated values expected output

rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:

create table test1 (name1 varchar2(10));

insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;

SQL> select * from test1;

NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO

View 3 Replies View Related

PL/SQL :: How To Compare Two Rows And Retrieve The Values

Aug 15, 2012

I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.

For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.

I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.

Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.

I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0

View 16 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

Listagg Function In 11g For Concatenating Values From Different Rows

Sep 11, 2013

I'm Trying to use Listagg function in oracle 11g for concatenating values from different rows,but i'm getting error as FROM KEYWORD NOT FOUND.

Query is:
select listagg(column_name,'') within group (order by column_name) "column_name"
from table_name;

View 2 Replies View Related

Insert Rows Based On Text Values?

Mar 22, 2007

I am an Oracle beginner and I am having some trouble with the following insert query.

I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.

INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);

The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"

View 1 Replies View Related

Counting Rows - Difference In Values That Are Returned?

Sep 26, 2010

difference in the values that are returned?

select count(*) from aaa;
COUNT(*)
----------
1000001

select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202

View 5 Replies View Related

SQL & PL/SQL :: How To Hold Multiple Rows Values Using Array

Jun 1, 2010

How to hold the multiple rows values using array? And I have to pass this values to some other procedure.

Ex: SQL> select ename from emp;

ENAME
----------------------
Vetrivel
Dr.Venkat
Vinoth
Sudhakar
Sivaganesh
Senthil

View 7 Replies View Related

SQL & PL/SQL :: Select Only Rows Where Certain Column Repeating Values

Mar 6, 2012

I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.

with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all

[Code]...

NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas

The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.

I need the sql syntax that would return this result set.

View 6 Replies View Related

SQL & PL/SQL :: How To Find Rows With Certain Values And Replace Them In The Same Script

Jun 17, 2010

I have a large table with a few billion rows. I need to find ITEM_NAME values in ITEM_MASTER table containing " " (spaces) and replace them with other values. This has to be done within the same script. The replacement values could be any characters or numbers, but not spaces.

The whole reason for this table is testing big databases, so it does not matter what it contains. The reason I need to replace them is that this table will be dumped to a space delimited flat file and spaces within values are not acceptable.

View 7 Replies View Related

SQL & PL/SQL :: Insert All Rows Values Fetched By Cursor?

Aug 12, 2010

I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.

what parameter or code I should used ?

I have marked with color row as below.

DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Column Values Concatenation From Multiple Rows

May 30, 2013

I am currently doing column values concatenation from multiple rows and then removing duplicates as in the following example:

SQL> select pid
2 , regexp_replace(ltrim(sentence), '([A-Za-z0-9]+,)1+', '1')
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
[code]....

but for some reason regexp_replace does not seem to work with clob and I get:

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"

Is it possible to eliminate duplicates in the model before the concatenation?

View 6 Replies View Related







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