Using Decode

[getjbb]

How can I use decode where a value can be either yes or no:

DECODE(some_variable, NULL, 'N','N','N','Y') = DECODE('E', 'I', 'Y', 'E', 'N', 'A', 'Y' or 'A', 'N')

In other words if the value if 'A' I want rows where a certain field has 'Y' and 'N'

getjbb

 

[SantaMufasa]

getjbb,

Your use of DECODE in your example, above, besides being syntactically unusable, does not convey what you are trying to accomplish. Could you please restate what you want done with, say, "IF/ELSE" statements in pseudocode terms?

Thanks.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[getjbb]

DECODE(database_field, NULL, 'N','N','N','Y') = DECODE(incoming_param,'I', 'Y', 'E', 'N', 'A', 'Y' or 'A', 'N')

if incoming_param = 'I' then
'Y'
elsif incoming_param = 'E' then
'N'
elsif incoming_param = 'A' then
<I want the be able to capture a row if it is either 'Y' or 'N' when incoming param is 'A'.
In other words 'A' can be 'Y' or 'N'
.

.

.

row1 apple 'Y'
row2 orange 'N'
row3 pear 'Y'
row3 kiwi 'N'

 

[SantaMufasa]

Sorry to draw this out, but I have a couple of additional questions: "If incoming_param = 'A' " then what column name will be the source for the 'Y' or 'N' value? And if that column does not contain a 'Y' or an 'N', then what do you want returned?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[getjbb]

'A' is a parameter coming into a procedure, which is a value set from one of three radio buttons ('I' (Include), 'E' (Exclude), 'A' (All) ).

The values of 'Y' and 'No' are being compared against each field within a cursor select.

If 'A' is not 'N' or 'Y',which mean it is NULL it should be set to 'N'.

getjbb

 

[SantaMufasa]

'A' is a parameter coming into a procedure...

Perhaps I'm worrying about things I don't need to worry about, but procedures in the "Oracle World" are written in PL/SQL, and PL/SQL does not allow independent DECODE statements -- Oracle allows DECODEs only as an expression within a SQL statement.

If your code is, in fact, in PL/SQL, then you can/should use the PL/SQL IF-THEN-ELSE construct:

IF <condition-A> THEN
    <whatever-A>;
ELSE
    <whatever-B>;
    IF <condition-C> THEN
        <whatever-C>;
    END IF;
END IF;
...

Please advise whether you are wanting your DECODE to run as an expression in a SQL statement or whether you want it to run (independently) within a PL/SQL block of code.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[getjbb]

Actually it has to be in the where clause of a select statement. The result ('Y' or 'N') is being compared to
a database field:

WHERE DECODE(database_field, NULL, 'N','N','N','Y') = DECODE(incoming_param,'I', 'Y', 'E', 'N', 'A', 'Y' or 'A', 'N')

getjbb

 

[taupirho]

WHERE
DECODE(database_field, NULL, 'N','N','N','Y') =
DECODE(incoming_param,'I', 'Y', 'E', 'N', 'A', 'Y')
OR
DECODE(database_field, NULL, 'N','N','N','Y')
DECODE(incoming_param,'I', 'Y', 'E', 'N', 'A', 'N')




In order to understand recursion, you must first understand recursion.

 

[getjbb]

taupirho --

Thank you so much, I was able to use your code.

getjbb