How execute DO WHILE loop in stand alone execution

Database

How execute DO WHILE loop in stand alone execution

I'm trying to implement loop for one time logic in my project. I cannot use stored procedure, but have to implement loops. Below is the logic, I'm trying to implement. Kindly let me know, how to make this work in teradata.

 

SyntaxEditor Code Snippet

create volatile table tbl_tmp( Col1 int,Col2 int) ;

begin
declare j int;
declare i int;
set j = 0;
set i = (select max(numchats) from sandbox.bc_chatcount);
while( j < i) do
insert into  tbl_tmp ( Col1, Col2) select OperatorId, numchats from sandbox.bc_chatcount where numchats = j;
j=j+1;
end while;
end;
8 REPLIES
Junior Contributor

Re: How execute DO WHILE loop in stand alone execution

You can't run this outside of a Stored Procedure.

 

But why do you want to do this loop instead of a straight Insert/Select?

It's simply inserting all rows.

Teradata Employee

Re: How execute DO WHILE loop in stand alone execution

Teradata SQL does not allow procedural logic outside a stored procedure. If a loop is actually required, the logic would need to be part of the client application or a stored procedure.

Or perhaps the solution can be reformulated to avoid procedural logic / loops and leverage Teradata's parallel architecture. In your example, you could simply remove the WHERE clause from the INSERT.

 

Senior Apprentice

Re: How execute DO WHILE loop in stand alone execution

Hi,

As Dieter said you can do this with a simple piece of SQL, no looping required (although I don't think it is quite 'all rows' as the while loop test is "j<i" - and not "j<=i"). Try:

create volatile table tbl_tmp
as
(select OperatorId, numchats 
 from sandbox.bc_chatcount 
 where numchats < (select max(numchats) from sandbox.bc_chatcount)
)
with data
on commit preserve rows ;

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: How execute DO WHILE loop in stand alone execution

And for your elucidation and future reference, @earunkarthik, I would like to refer you to the methods I have documented for translating looping logic into SQL at http://developer.teradata.com/blog/georgecoleman.  (I started this some time ago because I was not able to find any other good reference on the topic.)

Highlighted

Re: How execute DO WHILE loop in stand alone execution

Thanks for reply guys. But my business requirement is different.

Requirement and Constraint are,

1. The script has to be one time script, to be run on Tableau or Teradata SQL assitance. Just select the query and press F5 in SQL assistanct.

2. There is no access for creating stored procedure in database.

3. I need to pull user session activity. I have OperaterID and User login time data time stamp column.

4. Each Operator login will be inserted as a line in table. I need to divide timestamp for every 15 minutes and check his login status for a day.

5. Same, Operator1 has first login by 13-Oct-2017 09:13AM and last login entry by 13-Oct-2017 03:34PM. Now, I have divide the entry by every 15 minutes and then insert the status & timestamp into a temp table.

 

Is there a solution for this, to create a recursive loop or insert? The loop count is going to be the timestamp column (like, between TMP_STMP >= 13-Oct-2017 09:13AM and TMP_STMP <= 13-Oct-2017 09:23AM , till 3:34PM)

 

Hope you guys understood the requirement. Thanks in advnace for helping.

 

Thnx and Regards,

Arun Karthik

Senior Apprentice

Re: How execute DO WHILE loop in stand alone execution

Hi Arun,

 

I think your solution is going to use the EXPAND ON feature of Teradata. This will generate multiple rows each of (in your case) 15 minute intervals between the operators first and last logon during a day.

You've provided some thoughts about data values, could you expand on that and provide - using your 'operator 1' example a few rows of input data with what your expected results should be.

Also, what should the 'status' column show?

 

As a starter you might like to try the following. Please note that my derived table (dt_rawdata) is simply there for me to have some data to play with.

SELECT logdate,USERNAME,begin(date_range),end(date_range)
      FROM (SELECT USERNAME,CAST(starttime AS DATE) AS logdate
                   ,MIN(starttime) AS first_logon
                   ,MAX(starttime) AS last_logon
            FROM dbc.dbqlogtbl  
            GROUP BY 1,2
	   ) AS dt_rawdata /* <<< this represents your table */
      EXPAND ON PERIOD(first_logon,last_logon ) AS date_range BY INTERVAL '15' MINUTE
ORDER BY 1,2,3;

With my data the first few rows of my output are:

	logdate	operator_id	BEGIN(date_range)	        END(date_range)
1	24/07/2016	Z1_DBA	24/07/2016 13:35:07.580000	24/07/2016 13:50:07.580000
2	24/07/2016	Z1_DBA	24/07/2016 13:50:07.580000	24/07/2016 13:56:08.570000
3	17/11/2016	Z1_DBA	17/11/2016 10:15:56.220000	17/11/2016 10:16:04.270000
4	17/11/2016	Z1_U1	17/11/2016 10:09:11.480000	17/11/2016 10:15:35.080000
5	09/10/2017	DBC	09/10/2017 09:06:41.810000	09/10/2017 09:21:41.810000
6	09/10/2017	DBC	09/10/2017 09:21:41.810000	09/10/2017 09:36:41.810000
7	09/10/2017	DBC	09/10/2017 09:36:41.810000	09/10/2017 09:51:41.810000

Note that on each row the 'end' timestamp value is 15 minutes after the 'begin' timestamp value.

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: How execute DO WHILE loop in stand alone execution

Thanks a lot Dave, I was able to learn about EXPAND ON keyword. But my need is different. Here is the sample data.

 

OperatorID    Login_datetime                         status

--------------      ----------------                              -------

opr1                27/08/2017 03:00                     ONLINE

opr1                27/08/2017 03:03                     ONLINE

opr1                27/08/2017 03:12                     ONLINE

opr2                27/08/2017 03:01                     AWAY

opr1               27/08/2017 06:00                     AWAY

opr2               27/08/2017 06:01                     ONLINE

opr2               27/08/2017 06:02                     ONLINE

opr2               27/08/2017 06:13                     ONLINE

opr1                27/08/2017 10:00                     Busy

opr2                27/08/2017 10:00                     Busy

opr1                27/08/2017 12:00                     offline

opr1                27/08/2017 12:00                     offline

opr1                27/08/2017 14:00                     ONLINE

opr1                27/08/2017 14:00                     ONLINE

opr1                27/08/2017 16:00                     DO NOT DISTURB

opr1                27/08/2017 16:00                     DO NOT DISTURB

 

Expected output, (I cannot use storedprocedure)

 

FIRST_15MINUTES     OPERATOR_ID          COUNT

    3:00 - 3:15                     opr1                            3 

    3:00 - 3:15                     opr2                            1

---other consecutive interval

  6:00 - 6:15                        opr1                          1

  6:00 - 6:15                        opr2                          3

-----other consecutive interval

10:00 - 10:15                             opr1                        1

10:00 - 10:15                             opr2                        1

Junior Contributor

Re: How execute DO WHILE loop in stand alone execution

Seems like you simply want to truncate the timestamp to the quarter hour:

Login_datetime
   - (Extract(SECOND From x) * INTERVAL '1' SECOND)
   - (Extract(MINUTE From x) MOD 15 * INTERVAL '1' MINUTE) AS FIRST_15MINUTES

Now you can GROUP BY this plus OperatorID.