SQL & PL/SQL :: Difference Between IN And EXISTS Operator

Jan 10, 2012

what the difference between IN and EXISTS operator. Why should we use EXISTS operator?

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Difference Between Concat Function And (||) Operator?

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

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 :: Decode With Like Operator

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

SQL & PL/SQL :: Uses Of Check Operator

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

SQL & PL/SQL :: Alternate To Like Operator

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

SQL & PL/SQL :: Using 2 Union Operator

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

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

Forms :: Use Like Operator With If Condition?

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

SQL & PL/SQL :: Escape Operator Symbol?

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

SQL & PL/SQL :: Oracle 10g Quote Operator

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

PL/SQL :: Rownum In UNION Operator

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

Optimizer Cannot Merge A View That Contains Set Operator

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

Error ORA 920 / Invalid Relational Operator

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

SQL & PL/SQL :: Using Union Operator And Sort By Month?

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

SQL & PL/SQL :: Replace The Like Operator To Increase The Performance

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

Forms :: How To Compare Two Text Boxes With IN Operator

Jun 12, 2012

How we can compare IN operator between two text boxes in form

I have two text boxes on form
control.txt1 - 003

control.txt2 - 001,002,003

On button pressed

if :control.txt1 in (:CONTROL.TXT2) THEN
MESSAGE('1');
MESSAGE('1');

else

MESSAGE('0');
MESSAGE('0');
END IF;

All the time Message is 0 .

View 4 Replies View Related

SQL & PL/SQL :: Select From Dual Versus Equals Operator?

Mar 14, 2011

I have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)

But it does not raise any exception and the query works absolutely fine generating desired results when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)

View 6 Replies View Related

Forms :: Details On Mouse Click Using Like Operator?

Jun 9, 2013

I want to link to blocks using description as there is no relation , for example i have two tables with one field in common called description, and i want to link this field in two tables using like operator.

create table item ( item_code varchar2(12),item_name varchar2(30));
insert into item VALUES('A','HEA160');
insert into item VALUES('B','HEA180');
create table stk (sl_item varchar2(12),sl_desc varchar2(30),sl_qty number);
insert into stk VALUES ('X','HEA160X1000',12);
insert into stk VALUES ('X','HEA160X2000',4);
insert into stk VALUES ('Y','HEA180X3000',10);

Suppose i click on item block item_desc with value on HEA160 all the items similar to that should appear in stk block like 'HEA160X1000' ,'HEA160X2000' , if i click on 'HEA180' on item then 'HEA180X3000' it should come.

View 1 Replies View Related

SQL & PL/SQL :: Select Query Like Operator Getting Wrong Data

Dec 25, 2012

I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.

the first table name has not started with 'T_', why is it appearing.

*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE

View 4 Replies View Related

PL/SQL :: REGEXP_LIKE ORA-00920 Invalid Relational Operator

Apr 18, 2013

I am trying to use the REGEXP_LIKE and I am getting ORA-00920: invalid relational operator.

I have run very simplified code to try to sort it out but no success:

SELECT id, address
FROM test_lob_tab
WHERE REGEXP_LIKE(address,'^A');

select 1 from dual where regexp_like('123', '^[0-9]$');

same result from both.

I am using Oracle 11 and freetoad 11.

View 5 Replies View Related

SQL & PL/SQL :: How Does Operator Works For Varchar2 Datatype Columns

Nov 22, 2011

I have a table called Student and a column as name now if i write a Query

select * from student where name < 'BRIAN D'

How does the comparison will be done.

View 3 Replies View Related

PL/SQL :: Using Regexp_instr In A Where Clause - Invalid Relational Operator

Oct 23, 2013

Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted. 

select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where  regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy')  AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type

View 3 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 :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

Application Express :: How Does Filter Operator Contains Work On Interactive Reports

Jul 30, 2012

I'm creating Tool Tip definitions for the Operators in the Filter on the Interactive Reports. I was looking for a definition for the 'Contains' operator and from what I've found this operator is used to do a text search and it returns a relevance score for every row selected.

I've also read that in order for that score to be determined that the column(s) need to be indexed with a CONTEXT index. Non of the columns in the tables are indexed with a CONTEXT index, however, when I put a value in the Expression box for a column I get a record returned.

If I run the same query in PL/SQL Developer like:
SELECT <column>
FROM <table>
WHERE contains(<column>,<search text>,1) > 0;

I get an error that the column is not indexed, so how does it work in APEX?

View 2 Replies View Related

SQL & PL/SQL :: Not In Not Exists?

Aug 29, 2012

I have a question i wanted to know that " Is it possible to write the Following Query Using NOT EXISTS

SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT UNIQUE DEPTNO FROM EMP);

And one more doubt is there, can we use join to get same result.

View 9 Replies View Related

Application Express :: Version 4.1 Interactive Report Filter On Date Column Has No (=) Operator

Aug 17, 2012

if on an interactive report, you try and filter on a date column, then the "=" operator disappears.You get other (not so useful) operators like "in the next", "not in the next" ">", ">=" etc. - but no "=" to be found.

View 6 Replies View Related

Datafile 11 - Not Exists

Aug 3, 2009

i see in my alert.log this message:

Errors in file /oracle/BWP/saptrace/usertrace/bwp_ora_2728058.trc:
ORA-01114: IO error writing block to file 1030 (block # 602122)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
Additional information: 180224

But this file_id i don't have in my database, i am making these queries:

SQL> select FILE_ID from dba_temp_files order by FILE_ID;

FILE_ID
----------
1
2
3
4
5
6
7
8
9
[code]....

I don't have this file_id, why alert.log is showing me it? Of course, nobody has created this datafile and nobody has removed it too.

View 11 Replies View Related

PL/SQL :: Check Given Value Exists Or Not

Jun 18, 2013

i have a procedure like create or replace procedure studrec( a in sid%rowtype)asi sid%rowtype;beginselect sid into ifrom students; i need to check whether sid is exist in the variable i or not

View 5 Replies View Related







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