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??

Mtek (mtek (AT) mtekusa (DOT) 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;

Mtek (mtek (AT) mtekusa (DOT) 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;

Mtek (mtek (AT) mtekusa (DOT) 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;

Mtek (mtek (AT) mtekusa (DOT) 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;