Home » SQL & PL/SQL » SQL & PL/SQL » Return boolean value for match & non matched records (was:Problem) (Oracle 9i)
Return boolean value for match & non matched records (was:Problem) [message #332056] Mon, 07 July 2008 06:50 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
CASE1:
(SELECT A.ROLE_ID,A.ROLE_DESC,B.DEPT_ID FROM USR_ROLE_TBL A, DEPT_ROLE B WHERE A.ROLE_ID=B.ROLE_ID AND
B.DEPT_ID=(SELECT DEPT_ID FROM DEPT_ROLE WHERE ROLE_ID=90))

ROLE_ID_PRIV 		ROLE
------------- --------------------------------------------------------------------------------------
           92 		Gen User
           97 		CGWS
          104 		PRS Staff
		  

		  
		  


CASE2:

(SELECT A.ROLE_ID_PRV,B.ROLE_DESC FROM USR_ROLE_PRIV_GIVEN A,ROLE_TBL B WHERE A.ROLE_ID_PRIV=B.ROLE_ID AND A.ROLE_ID=90)


ROLE_ID 		ROLE
-------- -----------------------------------------------------------------------------------------
      
      90 		Help Desk
      91 		Web User
      92 		Gen User
      93 		PFS
      95 		SV PTS
	  
	  


Merging the above two statement Need to check if rows of 2nd resulset matches with the rows of 1st recordset.
If matches then return TRUE else false

Below is the required output:

	  
	  
	  ROLE_ID 		ROLE                     IND
-------- -----------------------------------------------------------------------------------------
      
      90 		Help Desk					FALSE
      91 		Web User					FALSE
      92 		Gen User					TRUE
      93 		PFS							FALSE
      95 		SV PTS						FALSE



Regards,
Oli


[Mod-Edit: Frank changed topic title]

[Updated on: Mon, 07 July 2008 08:41] by Moderator

Report message to a moderator

Re: Problem [message #332059 is a reply to message #332056] Mon, 07 July 2008 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After all the questions you asked these last weeks you still don't how to post and find a meaningful title.

Regards
Michel
Re: Problem [message #332062 is a reply to message #332059] Mon, 07 July 2008 07:09 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sorry for this Michel! Perhaps the question should be like this "Return boolean value for match & non matched records"

Check if the rows of 1st resultset are in 2nd resultset.If matched then return role_id,role_desc with indicator 'YES'
otherwise return role_id,role_desc with indicator 'False'.



Regards,
Oli
Re: Problem [message #332064 is a reply to message #332059] Mon, 07 July 2008 07:17 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Can you post your test case.

1) Table and Insert Statements.

Regards,
Rajat Ratewal
Re: Problem [message #332069 is a reply to message #332064] Mon, 07 July 2008 07:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I need the sql that returns matching and non matching rows with an indicator TRUE or False.If matches then True else False.

Sorry,I am unable to provide you the CREATE TABLE and INSERT sql now..
Re: Problem [message #332071 is a reply to message #332069] Mon, 07 July 2008 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sorry,I am unable to provide you the CREATE TABLE and INSERT sql now

It is just a matter of writing them.
If you have a keyboard, you are able to provide them.

Regards
Michel
Re: Problem [message #332072 is a reply to message #332069] Mon, 07 July 2008 07:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why are you unable to provide them?

You've posted up some test output for the two queries - why can't you create a couple of tables and some data for them that would produce the output you've posted, and send them to us.

The alternative is that we have to create your test data, and you're far less likely to get a decent answer if you want us to do all the work.
Re: Problem [message #332075 is a reply to message #332071] Mon, 07 July 2008 07:44 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Thanks Michel!
Does not the requirement make sense? Is it too tedious too understand what is required?


I have two sql queries which returns the resulset. Some rows matches while other rows doesnt matches ( based on role id).
If matched thene in the output I am expecting another column(pseudo) which returns TRUE (for matching rows)else return False.


Re: Problem [message #332076 is a reply to message #332075] Mon, 07 July 2008 07:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Join the two queries on the ID then, and use DECODE in the query to return TRUE/FALSE.

Re: Problem [message #332077 is a reply to message #332075] Mon, 07 July 2008 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So do it!
Or do you want us to show you how to do it?
In this case, post a test case, do you expect us to write it?

Regards
Michel
Re: Problem [message #332078 is a reply to message #332076] Mon, 07 July 2008 07:54 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks ThomasG for the hint.
I am using LEFT OUTER join. The outer join gives matching and non matching rows from 2 query result.

But not being able to track match and non match row and set TRUE or FALSE..


Output I expected:

ROLE_ID
ROLDE_DESC
INDICATOR( TRUE OR FALSE FOR THE ROWS)(pseudocolumn?)

Re: Problem [message #332082 is a reply to message #332078] Mon, 07 July 2008 08:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
use decode for that :


....
decode (col_01, col_02, 'TRUE', 'FALSE')
....

Re: Problem [message #332094 is a reply to message #332082] Mon, 07 July 2008 08:53 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thank you very much Thomas.

Here is the query.


SELECT P.ROLE_ID,P.ROLE_DESC, decode(P.ROLE_ID , Q.ROLE_ID, 'TRUE' ,'FALSE' ) INDICATOR
FROM
(SELECT A.ROLE_ID ROLE_ID,A.ROLE_DESC ROLE_DESC,B.DEPT_ID 
FROM USR_ROLE_TBL A, DEPT_ROLE B WHERE A.ROLE_ID=B.ROLE_ID AND 
	 B.DEPT_ID=(SELECT DEPT_ID FROM DEPT_ROLE WHERE ROLE_ID=90))P
LEFT OUTER JOIN 
(SELECT A.ROLE_ID_PRV ROLE_ID,B.ROLE_DESC ROLE_DESC 
FROM USR_ROLE_PRIV_GIVEN A,ROLE_TBL B WHERE A.ROLE_ID_PRIV=B.ROLE_ID AND A.ROLE_ID=90)Q
ON P.ROLE_ID=Q.ROLE_ID;




@Michel

I can understand that providing the script helps you to get descent answer.But instead of writing insert scripts for the existing tables I prefered to do it by myself.



Anyway, thanks to all for giving your time...


Regards,
Oli
Re: Problem [message #332097 is a reply to message #332094] Mon, 07 July 2008 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Our point of view is, generally speaking, that we'd like you to do as much of the work as possible, given that it's your problem that's being solved.

It's just politeness really.

There is a marked correlation between the number of solutions that get posted to a question and whether or not a set if Create/ insert statements are posted.

I've seen several threads hang around for a few days with nothing much happening, and then when someone other than the OP posts a set of scripts, the thread bursts into life.
Re: Problem [message #332106 is a reply to message #332097] Mon, 07 July 2008 09:20 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks JRowbottom...

So nice of you! Yah, I can really do understand that it helps you
to provide answers as per the requirement.

And you people are really very helpful. No doubt about that.

Its the place where one can learn a lot through sharing thoughts/ideas.And I love to be here.

But, I preferred to do by myself instead of writing INSERT/TABLE script. After posting the requirement,I was working by myself and trying to get the answer.


It was not that I posted the query and sit remain idle thinking that SOMEONE will do that for me.It never helps!


Hope you understand!


Thanks,
Oli


[Updated on: Mon, 07 July 2008 09:21]

Report message to a moderator

Re: Problem [message #332108 is a reply to message #332106] Mon, 07 July 2008 09:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The way I see it, as long as answers like "Try to use [technique]" or "Try to use [statement]" are enough for the person who asked the question to figure out a solution, then I can live without having a test case. Wink

It's those "Can you post some detailed solution/query?" questions that are impossible to answer without a test case.
Re: Problem [message #332127 is a reply to message #332108] Mon, 07 July 2008 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"impossible" is a very relative word.

Regards
Michel
Re: Return boolean value for match & non matched records (was:Problem) [message #332129 is a reply to message #332056] Mon, 07 July 2008 10:20 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Nothing is impossible for the person who does not actually have to do it.
Previous Topic: Outer Join
Next Topic: Case Criteria
Goto Forum:
  


Current Time: Sat Dec 10 14:53:21 CST 2016

Total time taken to generate the page: 0.09117 seconds