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
ADVERTISEMENT
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
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
View Related
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
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
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
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
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
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
Aug 29, 2012
I have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
View 15 Replies
View Related
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
Jun 21, 2010
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL
View 9 Replies
View Related
Mar 16, 2011
I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
View 9 Replies
View Related
Oct 24, 2013
create table test
(
id int ,
dat date
)
/
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
iddat
------------
124/10/2013
123/10/2013
123/10/2013
1
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
View 2 Replies
View Related
Jan 9, 2012
when i follow this steps mention on this website
[URL].........
to modify column from null to not null i got this error and on this website its show successful
my steps are
first i create a table
SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));
Table created.
Then insert some rows into Stu_Table
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');
1 row created.
SQL> select * from Stu_Table;
ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh
Table Structure is like this
SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
View 6 Replies
View Related
Jul 12, 2013
I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
How to write this.
View 5 Replies
View Related
Sep 16, 2010
I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .
create table my_tab
(U_name varchar2(30) ,
constraint ck_check (U_name = upper(m_name))
/
View 6 Replies
View Related
Mar 15, 2013
Any alternate to the following query.
select * from emp where ename like upper(NVL('%mi%',ename));
Basically I want to search based on string or null.
View 11 Replies
View Related
Jun 28, 2010
I have an query i.e.
I want 3 lines input in 1 line using 2 union operator like
Input:-
'i love playing
football and
volleyball'
i want the output like:-
"i love playing football and volleyball"
solve query using 2 union operator?
View 9 Replies
View Related
Jan 19, 2013
i want to know that how can i use like operator with if condition. i m using oracle10g form builder and it's for search purpose .
for example ...
if search string=string2 then
message('Record found');
end if ;
i want to use like '%search string%'.
View 1 Replies
View Related
Feb 28, 2011
1) Can we set a different symbol other than '' for escape operator.
2) If yes, how to see the current escape operator symbol.
3) How to find out the below name with escape operator?
Employee name ----> rama_krishna_raj
View 2 Replies
View Related
Jan 10, 2012
what the difference between IN and EXISTS operator. Why should we use EXISTS operator?
View 1 Replies
View Related
Sep 7, 2011
I have a issue on running the query with quote operator . When I am executing the SQL query I am getting error "Quoted String not properly Ended".
select q'[Oracle's world ]'
from dual
But The following query works.
select q'[It's Oracle's world ]'
from dual
View 4 Replies
View Related
Feb 18, 2013
I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this
select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2
Will the above query solve my purpose?
View 11 Replies
View Related
Jul 18, 2012
Query -
SELECT *
FROM sysadm.ps_tmtl_post_vw a
WHERE a.month_prepared_for = 'JUNE,2012'
AND a.ca_status = 'P5 CUST GO AHEAD'
[code]...
When I try for the SQL-Tuning sets its throws error that
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).
Attached File(s)
exec_plan.txt ( 2.06MB )
Number of downloads: 1
view_def.txt ( 14.12K )
Number of downloads: 2
View 5 Replies
View Related
Jul 30, 2010
The following runs no problem
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
ACCO.AUDIT_DTE < :AuditDate AND
[code]....
However, when I try to add extra conditions to the status' in the sub select i get the error. This is one way I tried:
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
[code]....
I've tried repeating the sub select for each of the extra status parts but everytime i hit the same problem.
View 1 Replies
View Related
Jan 10, 2012
i got the data like
select * from Table1
SNO Name B_MONTH
--------------------
101 A Mar
102 B Jan
103 C Feb
104 D Apr
105 f May
106 G Jun
Select * from Table2
107 H Dec
108 I Aug
109 J Oct
110 L Jul
111 M Sep
112 N Nov
select * from table1 union select * from table2 order by 3
The B_MONTH column is in Varchar2. Expected output should be
Output:
Jan
Feb
Mar
Apr
.
.
.
.
Nov
Dec
View 8 Replies
View Related
Jul 16, 2012
How to replace the like operator for increase the performance. Because it is taking more time and not using the index.
SELECT *
FROM emp
WHERE ename like '%AL';
View 30 Replies
View Related
May 6, 2013
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual;
--Output H S
Select Concat('H ',' S') A From Dual;
--Output H S
Select Length('H '||' S') A From Dual;
--Output 6
Select Length(Concat('H ',' S')) A From Dual;
--Output 6
View 5 Replies
View Related
Jul 12, 2012
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
View 8 Replies
View Related
