Solved

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

Posted on 2012-03-31
11
284 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
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
 
LVL 36

Expert Comment

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now