SQL & PL/SQL :: How To Get Result Without Minus

Nov 29, 2011

I got a two tables, table the_table_1 consist of customers and the other one the_table_2 consist of channels.

And I need to get list of channels by contract which are an assigned to customers.

The select below give me a only assigned channels but I need to get a contract_key too from the_table_1.

select o.channel_name from the_table_2 o
minus
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY

And the select under (after minus) gives a list of non assigned channels by contract_key:

select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY

how to rewrite a select to get required result?

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Difference Between Using MINUS And NOT IN

Sep 28, 2011

I am looking for an explanation for having the following query:

SELECT PK_SERIAL_NUMBER FROM TABLE1 MINUS (SELECT FK_SERIAL_NUMBER FROM TABLE2);

Which normally returns the values of SERIAL_NUMBER that are not passed to the child table TABLE2

and the query:

SELECT PK_SERIAL_NUMBER FROM TABLE1 WHERE PK_SERIAL_NUMBER
NOT IN
(
SELECT FK_SERIAL_NUMBER FROM TABLE2
);

which returns 0 rows!

View 6 Replies View Related

SQL & PL/SQL :: Minus Operator And NULL

Feb 12, 2013

I have a two tables with same column name , I wanted to find different record in table1 when compared with table2

create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);

insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;

select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);

no rows selected

how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so

NULL-NULL cannot be equal to zero how is it possible

View 3 Replies View Related

Difference Between NOT IN - MINUS - NOT EXISTS / EXCEPT Operators?

Nov 19, 2011

where difference lies between these operators or clause, & whether there is any "except" operator in oracle, i know other three are used bt dont think oracle uses "except" too..as far as i have seen "NOT IN", "MINUS" , "NOT EXISTS" are exactly similar?

View 4 Replies View Related

SQL & PL/SQL :: How To Delete Records Found With MINUS

Dec 1, 2010

I wish to delete records found with function MINUS from file1, which is a result of records not found in file2.

Both codes are taken way too long.

ps. none of the columms are unique, and some records have no value in some columns

DELETE
FROM file1 y
WHERE EXIST (SELECT *
FROM (
SELECT a.col1, a.col2, a.col3, a.col4
FROM file1 a

[Code]....

View 38 Replies View Related

SQL & PL/SQL :: Use Only SQL Joins Not Minus Or Subquery Or Any Function

Aug 2, 2011

i have two tables having some fields .In table table1 there is 2 lakh rows and in table table2 there is 3 lakh rows.In both table 2 lakh rows are common.I have to find those 1 lakh rows which are distinct but the condition for this i have to use only sql joins not minus or subquery or any function.

when i doing this.select t2.id,t2.name,t2.loc from table1 t1,table2 t2 where t1.id <>t2.id;

View 8 Replies View Related

Performance Tuning :: Not In Replace With Minus

Sep 15, 2011

I have 2 questions for this SQL Statement for tuning?

select * from a where id not in (select a_id from b)

1- How do i change this query with A MINUS OPERATOR

2- Shall i use a Hint for this query?

Explain plan is:

Execution Plan

--------------------------------------------------------------------------------
Plan hash value: 31652112322
--------------------------------------------------------------------------------
-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-

| 0 | SELECT STATEMENT | | 299 | 21528 | 9 (12)| 00:00:01
|

|* 1 | HASH JOIN RIGHT ANTI| | 299 | 21528 | 9 (12)| 00:00:01
|

|* 2 | TABLE ACCESS FULL | b | 1 | 23 | 4 (0)| 00:00:01
|

| 3 | TABLE ACCESS FULL | a | 300 | 14700 | 4 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
How do i optimize it best?

View 2 Replies View Related

SQL & PL/SQL :: Difference Between MINUS And LEFT Outer Join?

Jul 27, 2012

What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.

I can achieve same results using either one of them.

View 8 Replies View Related

SQL & PL/SQL :: Using Minus Operator To Get Changed Data Info Between Two Tables

Feb 14, 2013

I am using the below query to show the difference of data between two tables using minus operator.

teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.

select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
[code].......

View 7 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

create a procedure so that I could get minus query of 2 table as a result.

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

View 11 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 :: Number Generation MINUS Number Used

Feb 19, 2011

Here is script of tables

Quote:drop table p;
create table p (qty number(3), beg_no number(5));
insert into p values(5, 110);
insert into p values(8, 786);

drop table s;

create table s (used_no number(5));
insert into s values(111);
insert into s values(113);
insert into s values(791);

Table p: it has ticket quantity and ticket begining number. Thus according to first record ticket number will begin at 110 and will end at 110+5 (Beg_no +qty). According to second record ticket number will begin at 786 and will end at 786+8 (Beg_no +qty). This table can have many records.

Table s: it has ticket numbers which are sold. The ticket will always be any number from table and will lay in any record in this format between beg_no and beg_no+qty

Required: I want "p MINUS s" information. i.e.

Quote:
110
112
114
786
787
788
789
790
792
793

I am not expert in level by command.

Oracle version: 9
OS: Windows XP

View 3 Replies View Related

SQL & PL/SQL :: Getting Result From Three Tables

Sep 24, 2012

CREATE TABLE FLIGHTS(
FLIGHT_NUM NUMBER PRIMARY KEY
)

[Code]...

how to get the name of all passengers who have bookings in all flight_num?

o/p is 555

View 2 Replies View Related

SQL & PL/SQL :: Same Result When Use NOT IN And IN Condition?

Dec 14, 2011

I am using Oracle 8i.

Below is my query...

In the below 2 tables the records are like...

Table_1

IDNameCode
1AII
1AMNET
1AAXIS
1AUAT
2BMNET
2BUAT
2BUTC
3CMNET
3CII
3CUTC

Table_2

IDName
1AAA
2BBB
3CCC
4DDD

When i execute below query with using (NOT IN & IN) i am getting same result..

select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE not in ('MNET', 'II', 'AXIS', 'UAT'))

Result:

IDName
1A
2B
select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE in ('MNET', 'II', 'AXIS', 'UAT'))

Result:

IDName
1A
2B

When we execute the 1 query above it should not show 1 and 2 records...as i am using NOT IN condition.

View 5 Replies View Related

PL/SQL :: Query To Get Result Set

Feb 12, 2013

I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records

SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
     " ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
    LKP_Item.Group, LKP_Item.Modality Source
    FROM ((LKP_Product
[code]....

T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp

Just want to know if there is any possibility of running the query with all the Groups in one go

Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.

View 1 Replies View Related

PL/SQL :: How To Do The Partition On Result Using Sql Alone

Aug 27, 2012

How to do the partition on sql result result using query.

sample

BPREF_NO     BILL_MONTt AVG_IND     partition
Q12345     1/31/2009     2     part1
Q12345     2/28/2009     2     part1
Q12345     3/31/2009     2     part1
Q12345     4/30/2009     2     part1
Q12345     5/31/2009     2     part1
Q12345     6/30/2009     1     part1
Q12345     7/31/2009     2     part1
Q12345     9/30/2009     1     part2
Q12345     10/31/2009     2     part2
Q12345     11/30/2009     2     part2
Q12345     1/31/2010     1     part3
Q12345     2/28/2010     2     part3
Q12345     3/31/2010     2     part3
Q12345     11/30/2011     2     part4
Q12345     2/29/2012     2     part5
Q12345     3/31/2012     2     part5
Q12345     4/30/2012     2     part5
Q12345     5/31/2012     2     part5
Q12345     7/31/2012     2     part6

i want to create the partition column using query based on below logic

if bill_month is sequence then we have to create a partition and if any breaks comes we have to introduce the new partition.

just for example....
from 31-jan-2009 to 31-jul-2009 is called part1
from 30-aug-2009 to 30-nov-2009 is called part2
like wise...

is it possible to make the partition by query itself.

View 8 Replies View Related

XML DB :: Pivoting A Result Set

Oct 26, 2012

BANNER
--------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
[code]....

Middle EastThe table contains more than a million records, pivoting using a group by clause will have performance issues. Would it be possible to do this with Xquery or any other method?

View 11 Replies View Related

SQL & PL/SQL :: How To Display Result

Aug 7, 2012

I need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
[code].....

View 1 Replies View Related

Concatenating Result Of Query

Aug 30, 2007

My scenario is something like this:

MyTable

Rownum Colval
1 23
2 34
3 45

I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?

View 2 Replies View Related

SQL & PL/SQL :: How To Display Result Horizontally

Feb 11, 2013

1.Requirement....

deptno ename
++++++++++++++++++++++++++++++
10 a,d,e,g
20 b,c,j
30 k,l,o

2.How to swap the column values ? ex. col2 into col1 and col1 into col2

3.What is the major Diff. b/w Right outer join & Left outer join ?

View 3 Replies View Related

SQL & PL/SQL :: Code For RESULT Column?

Aug 4, 2011

I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:

create table zTEST
( IDx number,
STARTx number,
DURATION number,
RESULT number );
insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );

[code]...

The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.

What is the sql code for the RESULT-column?

View 3 Replies View Related

SQL & PL/SQL :: Join Two Queries Together To Get Result

Dec 12, 2011

How can i join two quires together to get result.

My requirement is:

First i want to select Table as we do

Select * from tab;

Then i want to describe the table as we do

Desc WO;

How can we join these two queries to have result.

View 21 Replies View Related

SQL & PL/SQL :: How To Use Substr To Get Required Result

Jan 7, 2013

i have following table

CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)

[Code]...

Result is

ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS

I want a query which should return only Sahde # from above data result must be as

7921
C-8762
C-8762
C8676

View 9 Replies View Related

SQL & PL/SQL :: How To Join 2 Tables To Get Result

Sep 7, 2010

how to join 2 tables to get the result I need.I have following 2 tables (all dummy data)

Table 1

org_iddd_1dd_2dd_3
K87973789865879876432465
J87879454321981234723454

Table 2
acc_numacc_nameacc_typevol_sales
789865 Abcn185765
879876 defd494854
432465 efgg5948545
789865 hijh685765
879876 klmj794854
432465 nopl9948545

I want to join the above tables to get the following result

org_idacc_numvol_sales
K8797378986585765
K8797487987694854
K87975432465948545
J8787978986585765
J8788087987694854
J87881432465948545

I have tried writing union queries and exists clause in where and seem to get nowhere with this.

View -1 Replies View Related

SQL & PL/SQL :: OUTER Join That Gives No Result

Aug 17, 2012

i have the following problem

select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))

I should have a result with this request but there's nothing and the problem come from the fact that when the following condition is not met, there are no resulty even if there's an outer join

and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))

This condition makes Oracle ignores the outer join..I have tried to add this:

select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and (memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
or memo.dateCreation is null)

but there are too much records and the result is not consistent

View 7 Replies View Related

SQL & PL/SQL :: How To Get Rows N Times In A Result Set

Mar 9, 2010

How to get rows N times in a result set?

For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.

But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".

View 2 Replies View Related

SQL & PL/SQL :: Result From Rows To Columns

Oct 29, 2013

How can i retrieve result of multiple rows into single row. Ex

select ename from emp;

Ename:
--------
ALLEN
WARD
JONES
MARTIN
BLAKE

[Code]...

Result should be like

ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

View 8 Replies View Related

SQL & PL/SQL :: Query Result Into Excel Or CSV

Jun 15, 2011

I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.

View 4 Replies View Related

SQL & PL/SQL :: Query Result In One Line

Aug 29, 2011

I have a select that return the query like this:

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br

so,I need the query result in just one line...

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br

I saw some exemples of "pivot query" but I still looking for a solution.

View 10 Replies View Related

SQL & PL/SQL :: Split Result Set Into Column

Aug 17, 2012

i have a table emp with three column

columns are (empid varchar,empnomini varchar,nominitype varchar), data in table like

empid empnomini nominitype
1 x B
1 y c
2 xx B
2 yyyy c

and i want data comes like

empid nominitype b nominitype c
1 x y
2 xx yyyy.

View 6 Replies View Related







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