SQLPLUS if then

using Oracle 9i
I am trying to create a report that will look at a user variable
and append sql criteria onto the end of the sql statement based
on the variable.
Example:

define x = '&1'

If &x = 'A' Then I would like to append 'WHERE test_column > 5' to the end of the statement.
If &x = 'B' Then append 'WHERE test_column > 500' to the end.
and so forth.

I am able to get the results through PL/SQL below but I am not sure how to pass that variable to sqlplus.
DECLARE x varchar(25) := 'A';
y varchar(30);
BEGIN
SELECT DECODE(x,
'A', 'WHERE test_column > 5',
'B', 'WHERE test_column > 500',
'C', 'WHERE test_column < 5')
INTO y
FROM DUAL;
select disc_Cd from disc y;
END;
/
select disc_Cd from disc y

Hi Mnymoen,

Here you go!
-----------------------------------------
var x varchar2(30);

DECLARE x varchar(25) := 'A';
y varchar(30);
BEGIN
SELECT DECODE(x,
'A', 'WHERE test_column > 5',
'B', 'WHERE test_column > 500',
'C', 'WHERE test_column < 5')
INTO y
FROM DUAL;
:x:=y;
END;
/

print x

X
--------------------------------
WHERE test_column > 5
-----------------------------------------
Daniel

Quote:

---

Originally Posted by mnymoen

I am able to get the results through PL/SQL below but I am not sure how to pass that variable to sqlplus.
DECLARE x varchar(25) := 'A';
y varchar(30);
BEGIN
SELECT DECODE(x,
'A', 'WHERE test_column > 5',
'B', 'WHERE test_column > 500',
'C', 'WHERE test_column < 5')
INTO y
FROM DUAL;
select disc_Cd from disc y;
END;
/
select disc_Cd from disc y

---

No. You are not.

Maybe this will work:

Code:

---

Def X = '&1'
Col W New_Value Test
Select Decode('&X','A',' > 5','B',' > 500','C',' < 5') W
  From Dual;

Select Disc_Cd
  From Disc
 Where Test_Column &&Test;

---

:p