Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-31
11
Medium Priority
?
295 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

618 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