Solved

How to use if exists in ORACLE/SQL?

Posted on 2014-10-08
13
7,192 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now