Re: DECODE QuestionHi,

Mtek (mtek_at_mtekusa.com) wrote:
: Hi,

: I have a question about DECODE. I know that I can return a 1 or 0
: using DECODE:

: SELECT (DECODE...,1,0) FROM <table name> WHERE........;

: My question is, what you need to just decode the result of the where?
: Basically if all the criteria of the WHERE statement is true, return a
: 1, and if any of the criteria is false, return a 0.

: SELECT (DECODE...,1,0) FROM <table name>
: WHERE........AND.......AND......;

: Is there a way to do that?? Subqueries? Analytical functions??

I am not clear what you mean. Decode can do much more than you describe, so perhaps your misunderstanding of decode is the issue. Modern Oracle also has the CASE expression which is often easier than decode.

In your original SQL, the select will return the rows for which the where is true, so you might get output such as

        select 1 from table where XXX;

	         1
	----------
	         1
	         1
	         1
	         1
	         1
	         1
	         1

	7 rows selected.

Is that really the sort of result set that you want? I doubt it.

If you mean to list some data and then flag some of it, perhaps you want something like

	select t.view_name,
		case 
		when view_name like '%X%' then	'LOOKS LIKE X'
		else				'not like x'
		end
	from user_views t;