Home » SQL & PL/SQL » SQL & PL/SQL » Path dependend case-statement (merged 3)
Path dependend case-statement (merged 3) [message #377016] |
Fri, 19 December 2008 08:10  |
steffeli
Messages: 112 Registered: July 2006
|
Senior Member |
|
|
Hello,
I want to create a variable RSI_STRAT [1, 0] which depends on the variable RSI. When RSI is below 30, RSI_STRAT should be 1 and remain 1 until RSI > 50. I tried the code below but it seems that I can' use RSI_STRAT in the case-statement. How can I do that?
Thanks Stefan
select a.*,
case when a.RSI < 30 then 1
when (lag(a.RSI_STRAT,1) over (order by a.ID)) = 1 and (a.RSI < 50) then 1
else 0
end as RSI_STRAT
from X_TEST a
|
|
|
|
|
|
|
|
|
Re: Path dependend case-statement (merged 3) [message #377361 is a reply to message #377016] |
Mon, 22 December 2008 08:55   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can use RSI_STRAT in a case statement. You just can't use it in the case statement that defines what the value of RSI_STRAT is.
Here's a way of calculating the value:drop table test_053;
create table test_053 (ID number,RSI number,rsi_strat number);
insert into test_053 values (1,80,0);
insert into test_053 values (2,70,0);
insert into test_053 values (3,60,0);
insert into test_053 values (4,50,0);
insert into test_053 values (5,40,0);
insert into test_053 values (6,30,0);
insert into test_053 values (7,20,1);
insert into test_053 values (8,30,1);
insert into test_053 values (9,40,1);
insert into test_053 values (10,50,0);
insert into test_053 values (11,60,0);
insert into test_053 values (12,50,0);
insert into test_053 values (13,40,0);
insert into test_053 values (14,30,0);
insert into test_053 values (15,20,1);
insert into test_053 values (16,10,1);
insert into test_053 values (17,10,1);
insert into test_053 values (18,20,1);
insert into test_053 values (19,30,1);
insert into test_053 values (20,40,1);
insert into test_053 values (21,30,1);
insert into test_053 values (22,20,1);
insert into test_053 values (23,30,1);
insert into test_053 values (24,40,1);
insert into test_053 values (25,50,0);
commit;
select id
,rsi
,rsi_strat
,case when max(case when rsi<30 then id else 0 end) over (order by id) > greatest (0,max(case when rsi>=50 then id else 0 end) over (order by id)) then 1
when max(case when rsi>=50 then id else 0 end) over (order by id) > greatest (0,max(case when rsi<30 then id else 0 end) over (order by id)) then 0
else -1
end rsi_strat_calc
from test_053;
|
|
|
Re: Path dependend case-statement (merged 3) [message #377363 is a reply to message #377344] |
Mon, 22 December 2008 09:05   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
One more solution provided you are using 10g is
select id, rsi, last_value(rsi_start ignore nulls) over(order by id) from
(
select id, rsi, case when rsi < 30 then 1
when rsi >= 50 then 0
else null
end rsi_start
from test_053
)
Regards
Raj
P.S : @JRowbottom thanks for the create table.
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 09:31:45 CST 2025
|