SQL While Statement in Data Virtuality Query Code

For SQL database developers, WHILE loops are important to handle repeating tasks a few times mainly defined by a number of rows or a set of items, etc. In this SQL tutorial for Data Virtuality developers, I want to show a sample SQL query using WHILE control statement.

In English alphabet there are 26 letters. In following SQL script, letters are given in order in a SQL variable named "letters". Using SQL WHILE statement, the string variable containing all the letters is read from last letter to build a new string variable which reverses the letters in the alphabet.

If SQL programmers check, there is a criteria statement used with WHILE command.
It is important to maintain or update the criteria statement within the WHILE block, if the criteria condition never changes the loop runs infinitely, actually till it consumes all resources of your Data Virtuality server.

In following SQL, we have an integer variable which stores the length of concatenated string formed by all alphabet letters.
Within WHILE loop, we read for example 26th letter, then 25th letter, till we reach to first letter and read it as the last iteration item.

Below WHILE loop SQL code block enables database developers to reverse string values.


BEGIN



declare string letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

declare string reversedString = '';



declare integer @i = length(letters);



WHILE (@i > 0)

BEGIN

 reversedString = reversedString || substring(letters,@i,1);

 @i = @i - 1;

END



select reversedString as "Letters in Alphabet in reversed order";



END;;

While Loop SQL Code

SQL While loop sample code for Data Virtuality Logical Datawarehouse developer

For more about control structures like If statements, Loop and While statements, database SQL programmers can check official guides at Data Virtuality portal.

If you want to know more about SQL string functions on Data Virtuality including Substring() please refer to Reference Guide at Data Virtuality portal.