Solved

How to use if exists in ORACLE/SQL?

Posted on 2014-10-08
13
8,012 Views
Last Modified: 2014-10-24
I am trying to execute a query with if exist and it returns an error. Error Report

and the query is
IF Exists(select * from page  WHERE form_id = '255626');

Open in new window


How can I resolve this?  I am just trying to check whether the records exists or not?

Thanks.
0
Comment
Question by:developer2012
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40368986
Where exists in (select * from page  WHERE form_id = '255626')
0
 

Author Comment

by:developer2012
ID: 40369002
Can you please elobrate it?  I mean where should I put where exists in?

select id from pages
where exists in(select * from pages WHERE form_id = '255626')
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40369003
If you're doing this as a decision structure, then it's IF EXISTS(...).  To demonstrate..
CREATE TABLE page (form_id int) 

INSERT INTO page (form_id) VALUES (1) 

IF EXISTS (SELECT * FROM page WHERE form_id=255626) 
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'
	
INSERT INTO page (form_id) VALUES (255626) 

IF EXISTS (SELECT * FROM page WHERE form_id=255626) 
   PRINT 'TRUE'
ELSE
   PRINT 'FALSE'

Open in new window


If you're doing it as a query with only one table, then it's WHERE form_id=255626

If you're doing it as a query with multiple tables, spell out all the tables we're talking about.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40369079
What product/software are you using?

You mention Oracle/SQL in the title but there is no 'if exists' in Oracle.

From the sample you posted above, Oracle syntax is:
 select id from pages
 where exists (select 1 from pages WHERE form_id = '255626') 

Open in new window


I hope that is just an example because that query really doesn't make sense.
0
 

Author Comment

by:developer2012
ID: 40369215
I want to check whether the record exists or not of certain value.

For example there are some values which does not exist in the table then how can I check it?

Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40369237
Connect to the database using sqlplus:
select id from pages WHERE form_id = '255626'

You either get rows or you don't.  If you don't then 255626 doesn't exist.

Other than that, I don't understand the question.

If you want to check in code, what programming language?

We need more information to provide a more exact answer.
0
 

Author Comment

by:developer2012
ID: 40369332
Well, I am using c# and I would like to check whether the ID '255626' exist or not in the column form ID.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40369527
I wish you hadn't accepted this.

First the SQL in the first post, http:#a40368986 isn't even valid Oracle syntax.
The second accepted answer won't work for C#.

Here is how it should be done in C# (ODP.Net example):
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select count(*) from page  WHERE form_id = :myid and rownum=1";
cmd.CommandType = CommandType.Text;

OracleParameter parm = new OracleParameter();
parm.OracleDbType = OracleDbType.Decimal;
parm.Value = "2556256";

Then to get the value:
cmd.ExecuteScalar()

This will return either a 0 or 1.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40369553
In my above comment I neglected to add a disclaimer 'SQL Server, no clue about Oracle...'.

>First the SQL in the first post, http:#a40368986 isn't even valid Oracle syntax.
Not SQL either, as WHERE EXISTS IN isn't valid, it's either WHERE EXISTS(...) or WHERE column_name IN (...)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40369623
developer2012 - Can you tell us how the first answer solved your question?
Just curious, as it appears that it doesn't work in either Oracle or SQL.
0
 

Author Comment

by:developer2012
ID: 40400259
Yes, Please close this queston.  Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40400265
>>Yes, Please close this queston.  Thanks.

It is up to you to properly close your questions by accepting the post or posts that provided the answer.

The reason this was re-opened is that one of the posts you selected as an answer wasn't even valid syntax so there was no way it could have helped answer the question.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question