Re: DECODE QuestionHi,
Date: 22 Aug 2008 15:18:51 -0800
Mtek (mtek_at_mtekusa.com) wrote:
: 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>
: 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;Received on Fri Aug 22 2008 - 18:18:51 CDT