?
Solved

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

Posted on 2012-03-31
11
Medium Priority
?
294 Views
Last Modified: 2012-04-17
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*/
0
Comment
Question by:MIREESE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37792341
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
 

Author Comment

by:MIREESE
ID: 37792544
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
 
LVL 20

Expert Comment

by:flow01
ID: 37792952
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 37794718
explain what you want to accomplish
*the idea*, not the code
0
 
LVL 32

Expert Comment

by:awking00
ID: 37796220
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
 

Author Comment

by:MIREESE
ID: 37817475
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
 
LVL 20

Expert Comment

by:flow01
ID: 37817595
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
 

Author Comment

by:MIREESE
ID: 37817739
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
 
LVL 20

Accepted Solution

by:
flow01 earned 2000 total points
ID: 37820112
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
 

Author Comment

by:MIREESE
ID: 37820474
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
 
LVL 20

Expert Comment

by:flow01
ID: 37821413
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question