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;
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 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.
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 ;
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.)
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,
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.