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 Go to next message
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 [message #377021 is a reply to message #377016] Fri, 19 December 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Fri, 19 December 2008 08:28]

Report message to a moderator

Re: Path dependend case-statement (merged 3) [message #377022 is a reply to message #377016] Fri, 19 December 2008 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And DON'T multipost your question.
Duplicates deleted.

Regards
Michel

[Updated on: Fri, 19 December 2008 08:33]

Report message to a moderator

Re: Path dependend case-statement (merged 3) [message #377038 is a reply to message #377022] Fri, 19 December 2008 09:38 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
I'm sorry for the multipost, it was a mistake. Here is an example file: I have the ID and the RSI and want to evaluate the RSI_STRAT.
  • Attachment: testcase.csv
    (Size: 0.23KB, Downloaded 591 times)
Re: Path dependend case-statement (merged 3) [message #377044 is a reply to message #377038] Fri, 19 December 2008 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read my post and the link. I don't care of a CSV file.

Regards
Michel
Re: Path dependend case-statement (merged 3) [message #377344 is a reply to message #377044] Mon, 22 December 2008 08:08 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Can anybody help me with my problem? I'd appreciate very much.

Thanks, Stefan
Re: Path dependend case-statement (merged 3) [message #377354 is a reply to message #377344] Mon, 22 December 2008 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 19 December 2008 15:27
Post a Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version (4 decimals).

If you don't want to help us help you why should we take time to help you.

First post what we asked then you will have answers.

Regards
Michel
Re: Path dependend case-statement (merged 3) [message #377361 is a reply to message #377016] Mon, 22 December 2008 08:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Path dependend case-statement (merged 3) [message #377364 is a reply to message #377363] Mon, 22 December 2008 09:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very nice - I'd completely forgotten about IGNORE NULLS.
Previous Topic: error message : Warning: compiled but with compilation errors
Next Topic: Insufficient Previleges
Goto Forum:
  


Current Time: Tue Feb 11 09:31:45 CST 2025