Home » SQL & PL/SQL » SQL & PL/SQL » Exists condition
Exists condition [message #405268] Wed, 27 May 2009 04:41 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
I have two tables

complete and incomplete


i want to create a flag with yes and no condition

if the record from complete exists in incomplete table then yes or No

can you please tell me how to create a sql sattaemnt for this

also both the tables are joined as innerjoin
Re: Exists condition [message #405270 is a reply to message #405268] Wed, 27 May 2009 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
If they're inner joined you'll only get records that exist in both.
You'll need to outer join (maybe) or use an exists sub-query (possibly).

Really you need to give more information - table scripts and a sample query.
We don't know what you're trying to select (apart from the flag) or what the relationship is between the tables.
Re: Exists condition [message #405276 is a reply to message #405268] Wed, 27 May 2009 05:07 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Below is the Query suggested by our DBA

select case when tablename.colname exists (select INCOMPLETEtablename.colname from INCOMPLETEtablename) then 'Y' else 'N' end asdf

from completetablename.colname

Can you guide me on how to achive this without full outer join.

Also if possible can you give me query with fullouterjoin so i can use that.
Plase help



Re: Exists condition [message #405277 is a reply to message #405276] Wed, 27 May 2009 05:17 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What is wrong with the query suggested by your DBA?
Re: Exists condition [message #405280 is a reply to message #405268] Wed, 27 May 2009 05:20 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Invalid relational operator is the error i get
Re: Exists condition [message #405281 is a reply to message #405280] Wed, 27 May 2009 05:22 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Post a copy and paste of the seesion in which you execute the code and get the error.
Re: Exists condition [message #405282 is a reply to message #405276] Wed, 27 May 2009 05:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you sure this is the query he sent you?
Do you know the syntax of EXISTS?
Can't you rewrite it to a syntactically correct query?
Re: Exists condition [message #405283 is a reply to message #405268] Wed, 27 May 2009 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your dba suggested an invalid query?

helpful.

Can you please use code tags next time you post code.

I doubt you want a full outer join either.

Details are still way too vague - give us table definitions (create table statements) and details of the relationship between them.
Also tell us what other data you want - I really doubt you want a query that returns a random list of Y/Ns.

With that information we should be able to sort your problem out.

In the meantime read up on outer-joins:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175
Re: Exists condition [message #405286 is a reply to message #405268] Wed, 27 May 2009 05:31 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Spent far too long writing that reply obviously Smile
Re: Exists condition [message #405287 is a reply to message #405268] Wed, 27 May 2009 05:34 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
I need EXISTS query only .

select case when tablename.colname exists (select INCOMPLETEtablename.colname from INCOMPLETEtablename) then 'Y' else 'N' end asdf

from completetablename.colname

Above Query was suggested by DBA

Please help me get this flag
Re: Exists condition [message #405291 is a reply to message #405268] Wed, 27 May 2009 05:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please answer the questions being asked of you and supply the information that we have asked for. Why have you chosen to ignore the replies? This makes no sense. Why come to a forum, ask for advice and then ignore thos that try to help? Bizzarre.
Re: Exists condition [message #405296 is a reply to message #405268] Wed, 27 May 2009 05:53 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
you can use something like this

 select num,NVL2((select num from b where b.num=a.num),'Yes','No')
 from a

Note :Here a and b are tables containing num column
Re: Exists condition [message #405330 is a reply to message #405268] Wed, 27 May 2009 06:57 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
a.num= b.num thats inner join and will give me common records and there are uncommon records also \

i need to use exists condition so i can create some case when exists condition
Re: Exists condition [message #405333 is a reply to message #405330] Wed, 27 May 2009 06:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
./fa/1587/0/
./fa/1620/0/
Re: Exists condition [message #405335 is a reply to message #405268] Wed, 27 May 2009 07:01 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
You might want to actually try ayush_anand's query. It might well work.
Inner join in a sub-query is fine (necessary in fact). Outer join is only usefull if you're not using a sub-query.

And you don't need to use exists for this. You can, but you don't have to.
Re: Exists condition [message #405337 is a reply to message #405333] Wed, 27 May 2009 07:02 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
pablolee wrote on Wed, 27 May 2009 12:59
./fa/1587/0/
./fa/1620/0/



indeed.
Re: Exists condition [message #405338 is a reply to message #405268] Wed, 27 May 2009 07:03 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
yess.

i will try and get back
Re: Exists condition [message #405340 is a reply to message #405335] Wed, 27 May 2009 07:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Wed, 27 May 2009 13:01

And you don't need to use exists for this. You can, but you don't have to.

Unless of course the teacher has set the use of the EXISTS operator as a requirement for the homework...
Re: Exists condition [message #405342 is a reply to message #405268] Wed, 27 May 2009 07:14 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Homework Embarassed Embarassed ..

Actually we are using a different tool here and i am looking for oracle syntax so that i can create what i need in the tool level.

Hence i cant use join here .

I need exists so i can comapare excluding join.

Thats why am stressing much in it Smile
Re: Exists condition [message #405345 is a reply to message #405342] Wed, 27 May 2009 07:18 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
./fa/1659/0/
./fa/3971/0/
Very Happy
Re: Exists condition [message #405348 is a reply to message #405342] Wed, 27 May 2009 07:19 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
avrillavinge wrote on Wed, 27 May 2009 13:14

Actually we are using a different tool here and i am looking for oracle syntax so that i can create what i need in the tool level.

Hence i cant use join here .

I need exists so i can comapare excluding join.




Huh?

You've got a tool that won't do joins?

exists is a join.

What exactly is this tool and what is it stopping you from doing?
Re: Exists condition [message #405360 is a reply to message #405268] Wed, 27 May 2009 07:34 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
AS you wish
 select i,'yes' from a where exists(select i from b where a.i=b.i)
 union
 select i,'no' from a where not exists(select i from b where a.i=b.i)

Re: Exists condition [message #405368 is a reply to message #405360] Wed, 27 May 2009 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
ayush_anand wrote on Wed, 27 May 2009 13:34
AS you wish
 select i,'yes' from a where exists(select i from b where a.i=b.i)
 union
 select i,'no' from a where not exists(select i from b where a.i=b.i)




But that's got joins Smile
Re: Exists condition [message #405370 is a reply to message #405268] Wed, 27 May 2009 07:47 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Solved i replaced exists by IN and its solved


Thanks a Ton for the help frnds
Re: Exists condition [message #405383 is a reply to message #405370] Wed, 27 May 2009 08:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You still did not answer (any of my) questions.
The most important one being
Quote:
Are you sure this is the query he sent you?

Go kick your DBA in his crystal balls.
If it was indeed exactly what he sent you, you'll be fine...
Re: Exists condition [message #405384 is a reply to message #405383] Wed, 27 May 2009 08:42 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Frank, the OP is doing a homework assignment, I'm guessing that she doesn't have a DBA. Not sure where the code came from, I'm guessing pseudo code from Teach.
Re: Exists condition [message #405401 is a reply to message #405268] Wed, 27 May 2009 09:45 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
SCOTT@orcl_11g> 
SCOTT@orcl_11g> BEGIN
  2    INSERT INTO complete VALUES ('001', 'X', '01') ;
  3    INSERT INTO complete VALUES ('002', 'Y', '02') ;
  4    INSERT INTO complete VALUES ('003', 'Z', '02') ;
  5    INSERT INTO complete VALUES ('004', 'A', '03') ;
  6    INSERT INTO complete VALUES ('005', 'B', '04') ;
  7  END;
  8  /

PL/SQL procedure successfully completed.
SCOTT@orcl_11g> CREATE TABLE incomplete
  2  ( EMPID VARCHAR2(3),
  3    EMPNAME VARCHAR2(25),
  4    DEPTID  VARCHAR2(3));
SCOTT@orcl_11g> 
SCOTT@orcl_11g> BEGIN
     INSERT INTO incomplete VALUES ('001', 'X', '01') ;
      INSERT INTO incomplete VALUES ('003', 'Z', '02') ;
     INSERT INTO incomplete VALUES ('004', 'B', '04') ;
   END;
    /

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE TABLE NEW_EMP
  2  ( EMPID VARCHAR2(3),
  3    EMPNAME VARCHAR2(25),
  4    DEPTID  VARCHAR2(3),
  5    STATUS  VARCHAR2 (1));

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE Test
  2    (i_depts IN VARCHAR2)
  3  AS
  4  BEGIN
  5    INSERT INTO new_emp (empid, empname, deptid, status)
  6    SELECT complete.empid, complete.empname, complete.deptid,
  7  	      NVL2 (incomplete.empid, 'T', 'F')
  8    FROM   complete,
  9  	      incomplete
 10    WHERE  complete.empid = incomplete.empid (+);
 11  END Test;
 12  /

Procedure created.



SCOTT@orcl_11g> SELECT * FROM new_emp ORDER BY empid
  2  /

EMP EMPNAME                   DEP S
--- ------------------------- --- -
001 X                         01  T
002 Y                         02  F
003 Z                         02  T
004 A                         03  T
005 B                         04  F


can be another approach

[Updated on: Wed, 27 May 2009 09:49]

Report message to a moderator

Previous Topic: stored procedure
Next Topic: Query to find all the column references..
Goto Forum:
  


Current Time: Mon Dec 05 11:01:20 CST 2016

Total time taken to generate the page: 0.16149 seconds