capture the values assigned from a cursor to a log table

Database
Enthusiast

capture the values assigned from a cursor to a log table

Hey There,

 

How to capture the values assigned from a cursor to a log table...

I am trying to debug someone else's teradata stored process code...

 

The idea is to look at consecutive records of a hotel checkin/check out and processing some logic...I need to understand the logic for certain scenarios..hence trying to view/output the values...

 

The fetch from cursor bring's in the values...

How to view those variable values assigned...I had tried to insert them into a log table...

Unfortunately no values are logged...

Could you please let me know how to fix this or alternatives to view the assigned values...

 

Any help is highly appreciated...

 

SyntaxEditor Code Snippet

   replace PROCEDURE  GP_sp1()
 

   BEGIN

     DECLARE curr_partyid BIGINT;
     DECLARE curr_CheckOutDate date; 
     DECLARE curr_Checkindate date;

     DECLARE lag1_partyid BIGINT;
     DECLARE lag1_CheckOutDate date; 
     DECLARE lag1_Checkindate date;
    
     
             DECLARE folio CURSOR FOR 
               select partyid,CheckOutDate,Checkindate
               FROM TMP_FA
               order by partyid, checkindate
             FOR READ ONLY;
             
             open folio;
             
SPLABEL: 
            LOOP
            
             FETCH folio INTO curr_partyid,curr_CheckOutDate,curr_Checkindate;
                
                IF (SQLSTATE = '02000') THEN
                  LEAVE SPLABEL;
                END IF;
                
            fetch next from folio INTO lag1_partyid,lag1_CheckOutDate,lag1_Checkindate;
            IF (SQLSTATE = '02000') THEN
              LEAVE SPLABEL;
            END IF;                
                
                 
                 insert into TMP_FA_log (curr_partyid,lag1_partyid,lag2_partyid,curr_Checkoutdate,lag1_CheckOutDate,lag2_CheckOutDate)
                 values(:curr_partyid,:lag1_partyid,:lag2_partyid,:curr_CheckOutDate,:lag1_CheckOutDate,:lag2_CheckOutDate);
            
            if lag1_partyid=curr_partyid and lag1_CheckOutDate > curr_CheckOutDate then 
                
                    ---Some Logic ---------

            elseif lag1_partyid=curr_partyid and lag1_CheckOutDate > curr_CheckOutDate then 
                
             ----Some Logic----------
                
                end if;                

               END LOOP SPLABEL;
     
     CLOSE folio;
   END;
   
   

 


Accepted Solutions
Junior Contributor

Re: capture the values assigned from a cursor to a log table

Cursors are evil as they are processed serially, row by row, and this is worst case in a massive parallel database system like Teradata.

But the good news is: There's almost never a need to use a cursor for processing data :-)

 

You're simply looking for a row where the maximum previous CheckOutDate is less than the current CheckInDate, this indicates the starting row of a new stay:

 

SELECT partyid,Checkindate,CheckOutDate,
   Sum(newStayFlag) -- calculates the stay number
   Over (PARTITION BY partyid
         ORDER BY Checkindate
,newStayFlag DESC -- this is to avoid possibly wrong results due to multiple rows with the same dates ROWS Unbounded Preceding) AS StayNbr FROM ( SELECT partyid,Checkindate,CheckOutDate, CASE WHEN CheckInDate <= Max(CheckOutDate) -- maximum previous CheckOutDate Over (PARTITION BY partyid ORDER BY Checkindate ROWS BETWEEN Unbounded Preceding AND 1 Preceding) THEN 0 -- overlapping stay ELSE 1 -- new stay END AS newStayFlag FROM TMP_FA ) AS dt

 

 

Depending on your hardware this should run in a few seconds or less for a million rows :-)

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: capture the values assigned from a cursor to a log table

I can't imagine this even compiles, since lag2_partyid and lag2_CheckOutDate are neither declared nor assigned any values.

Enthusiast

Re: capture the values assigned from a cursor to a log table

Hey Dan,

I have not posted the exact code I was working due to confidentiality and must have made some changes on the fly to present my issue...

I have figured the issue I was having with the logic and was able to capture the results to the log table...

In SQL server Management Studio with Cursors I could use simple print the variable values to the output window when I want to debug...

Is there a similar way of looking at/output the values of variables in Teradata SQL assistant for debugging...If not SQLA then is there any other tool...

 

I searched a lot and could not find an easy solution...So I capturing the values assigned to a log table...

Please advise...

Thanks in advance.

Junior Contributor

Re: capture the values assigned from a cursor to a log table

A log table is the recommended way (Teradata still doesn't have a debugger for Stored Procedures).

 

But there's should be some documentation for this SP.

IMHO it looks like there's no need for a cursor, a simple query using some Windowed Aggregates (LAG/LEAD) should get you the same result.

Enthusiast

Re: capture the values assigned from a cursor to a log table

Hey Dnoeth,

 

Thanks...

 

I am curious to know about the windowed Aggregate LAG/LEAD solution to my problem...

I have implemented with a cursor looking at least 2 instances back for every row but it takes a lot of time to process...

 

I am looking to see if there is an efficient way to do this in Teradata...

BTW the same logic takes 7 mins to process ~ 1million rows in SAS software...I was hoping to solve this in Teradata stored proc...

 

SO here's my problem...please take a look at a patron and their checkin and checkout dates at a hotel.

ideally, I want to output these records with StayNbr as the output below...the logic is that the patron did multiple room bookings with different reservation id's/dates (green records) but it is the same stay #2 for all those intermingled checkin/checkout dates...

 

Input

-----

patronID   check-in     checkout

1             2/1/2009   2/4/2009

1             2/3/2010   2/6/2010

1             2/4/2010  2/5/2010

1             2/4/2010  2/6/2010

1            2/5/2010  2/7/2010

 

Output

--------

patronID   check-in     checkout  StayNbr

1             2/1/2009   2/4/2009      1

1             2/3/2010   2/6/2010      2

1             2/4/2010  2/5/2010       2

1             2/4/2010  2/6/2010       2

1            2/5/2010   2/7/2010       2

 

 Any help is highly appreciated in this regards...

Junior Contributor

Re: capture the values assigned from a cursor to a log table

Cursors are evil as they are processed serially, row by row, and this is worst case in a massive parallel database system like Teradata.

But the good news is: There's almost never a need to use a cursor for processing data :-)

 

You're simply looking for a row where the maximum previous CheckOutDate is less than the current CheckInDate, this indicates the starting row of a new stay:

 

SELECT partyid,Checkindate,CheckOutDate,
   Sum(newStayFlag) -- calculates the stay number
   Over (PARTITION BY partyid
         ORDER BY Checkindate
,newStayFlag DESC -- this is to avoid possibly wrong results due to multiple rows with the same dates ROWS Unbounded Preceding) AS StayNbr FROM ( SELECT partyid,Checkindate,CheckOutDate, CASE WHEN CheckInDate <= Max(CheckOutDate) -- maximum previous CheckOutDate Over (PARTITION BY partyid ORDER BY Checkindate ROWS BETWEEN Unbounded Preceding AND 1 Preceding) THEN 0 -- overlapping stay ELSE 1 -- new stay END AS newStayFlag FROM TMP_FA ) AS dt

 

 

Depending on your hardware this should run in a few seconds or less for a million rows :-)

Enthusiast

Re: capture the values assigned from a cursor to a log table

Thanks a bunch DNoeth...

That was an awesome solution...I am going to read all your posts for tips...

I believe I have seen this "rows between " before but never looked into the specifics of it...

I couldn't think beyond the cursor world...

Tags (1)