How do I retain the value in one Record to others using PL/SQL Oracle

I tried several times to declare,set and hold the value but keep getting errors?

Thanks

SELECT
l.log_id
,l.Surgery_Date
,l.room_id
,l.sched_start_time

From OR_LOG l

Case
  When
    l.room_id = 123
  Then
    Set the TempRoom = l.room_id
  End

Where
l.surgery_date >= trunc(sysdate)-60

ORDER BY l.room_id,l.sched_start_time;                /*Sort by OR and Start Date and Time*/
MIREESEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wasim Akram ShaikCommented:
MIREESE : Recheck the syntax, there is a syntatical error in your query..

can you post the complete code...??

is this an oracle query??

you can't use SET in select statement of oracle..

you have to declare a record or variable and use something like this select a,b,c into rec.a,rec.b,rec.c  from table where....
0
MIREESEAuthor Commented:
That's where I am having trouble.

I have a pretty big Oracle view that does a select of log records that includes a Room_ID. I would like to  and would like to set a temporary flag value into a temp variable until the Room_ID changes.


CREATE OR REPLACE VIEW V_OR_CASE_LOG_DETAILS AS

SELECT
  l.log_id
 ,l.Surgery_Date
 ,l.room_id
 ,l.sched_start_time

/*I would like to a TempVar to 1 until the Room_ID changes*/

From OR_LOG l
Where
l.surgery_date >= trunc(sysdate)-60
ORDER BY l.room_id,l.sched_start_time;                /*Sort by OR and Start Date and Time*/

grant select on V_OR_CASE_LOG_DETAILS to PW
0
flow01Commented:
you can get the value of a previous record using the lag() function

-- an example
select time, case
             when trunc(lag(time) over (order by time)) = trunc(time)  -- the order by should mimic the order by of your main query for this use
                then null
             else
                'day-switch'
             end sw
from
(
select sysdate + 2*level/24 time
from dual connect by level < 24
)
/

in such a case sw is just a delivered sql value : it's just sql

in pl/sql you can retain a value

declare
    v_room_id V_OR_CASE_LOG_DETAILS.room_id%TYPE; -- variable to hold room_id
begin
    for r1 in (select * from V_OR_CASE_LOG_DETAILS) loop
        if  v_room_id is not null and v_room_id <> r1.room_id then
            dbms_output.put_line('room_id changed');
       end if;
       v_room_id := r1.room_id; -- keep last room_id when fetching next record
   end loop;
end;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Geert GOracle dbaCommented:
explain what you want to accomplish
*the idea*, not the code
0
awking00Commented:
I agree with Geert_Gruez. A more detailed explanation of what you're trying to accomplish, perhaps with some examples, would be helpful to us in developing a solution.
0
MIREESEAuthor Commented:
I guess the above solution works but when I tried it, my query ran very slow. Here is what i was trying to do.

I have a table that contains logs with RoomID. My view sorts by room and datetime. If the first record for a paticular room has a time of 7am. I would like to  perform a calculation until that room ID changes?

Sample

CREATE OR REPLACE VIEW V_OR_CASE_LOG_DETAILS AS

SELECT
  l.log_id
 ,l.Surgery_Date
 ,l.room_id
 ,l.sched_start_time

/*I would like to a TempVar to 1 until the Room_ID changes*/

From OR_LOG l
Where
l.surgery_date >= trunc(sysdate)-60
ORDER BY l.room_id,l.sched_start_time;                /*Sort by OR and Start Date and Time*/

grant select on V_OR_CASE_LOG_DETAILS to PW
0
flow01Commented:
What solution (the lag-function or the pl-sql  code)  did you run ?
Where the results correct ?
If yes, share it : we then  know what you want to achieve and find solutions for speeding up.
  Other questions will be asked : how many records are involved, is there an index on surgery_date, is there an index on room_id etc.

If not specify your needs : what should happen if for a particular room the first record has a time of 7.01 am  or 8.00 am. What do you want calculate ?
I also guess you probably want to do the calculation per day :   if the first record for a particular room on a particular day has ..  calculate ... until the room id changes or the surgery_date changes to another day ?
0
MIREESEAuthor Commented:
I used the lag function to get the first record for a roomID that is 7am. That works fine. Once I hit the 3 third record for the same roomID, I cannot determine the time so I need a flag to perform my calculation.

If the time is 7am then Variable SP = 1 until the room_ID changes. When Room ID changes and the SurgeryDateTime is not 7am then SP = 0.
0
flow01Commented:
You just got to answer 2 of the questions I asked and the answer to second is wrong.
You sayd 'that works fine' , but you can't determine what you want so the result is not correct.
I see now you want to get the first record for a roomid : instead of lag you can use the min function over the window
something like
    min(l.sched_start_time)  over (partition by l.room_id)

But maybe you want just the first sched_start_time  for a room on a certain day that after 7 am an the last (sched_end_time) until midnight or until 7 am next day and calculate the time between those 2 times?
Don't keep us guessing give answers to the questions your were asked !   (also those from the other experts)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MIREESEAuthor Commented:
Sorry but I am not trying to keep you guessing. Also, I did not include my original complete query because it is too large.

Here is where I am at as well as my problem in search of an efficient solution:

1.      My query/view is sorted by RoomID, Surgerydatetime.
2.      When a record has a surgerydatetime of 7am, the query will perform a calculation “UtiliHrs”.
3.      If the second record has the same roomID value, my query (using the lag “surgerydatetime”,1 function) will perform the same calculation on that record because the first records date and time is 7a.

Problem
If the third or fourth record has the same roomID, my calculation will not work because it cannot see the first records surgerydatetime.
0
flow01Commented:
instead of the lag-function try
     min(surgerydatetime) over (PARTITION BY roomID,  trunc(surgerydatetime -  (7/24))  -- t to return the first surgerydate time of a roomID  within a 24 hours-window  starting at 7 am
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.