SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for AWS Amazon Web Services, Redshift, AWS Lambda Functions, S3 Buckets, VPC, EC2, IAM

SQL CASE Statement in Data Virtuality Query


For SQL developers building applications using Data Virtuality, SQL CASE statement is required frequently in View source query or in Procedure SQL codes.

When SQL programmers checks below SQL query, they can see that first of all an integer variable is declared.
Then using numeric SQL function RAND(), a random number is generated. Since Rand() random SQL function generates a double value between 0 and 1, I multiply it by 10 to generate a number between 0 and 10.
Of course a CAST conversion function is applied to convert double type data into integer type data.

Then using string concatenation expression " || ", I generate a new string value and display it as an output of the following SQL query execution.

begin

declare integer @i;
@i = cast( (RAND() * 10) as integer);

select
 'My random number ' || cast(@i as varchar(3)) || ' is ' ||
 case when @i < 5 then 'less then 5' else 'between 5 and 10' end;

end;;
Code

Please pay attention to CASE statement usage.
The syntax is as follows:

CASE WHEN (criteria) THEN (block) ELSE (block) END
Code

SQL Case statement sample for Data Virtuality developer

It is possible to extend the same SQL query using CASE statement with additional WHEN clauses as follows

begin

declare integer @i;
@i = cast( (RAND() * 10) as integer);
select
 'My random number ' || cast(@i as varchar(3)) || ' is ' ||
 case when (@i < 5) then ('less then 5') else ('between 5 and 10') end,
 case @i
  when (0) then ('Zero')
  when (1) then ('One')
  when (2) then ('Two')
  when (3) then ('Three')
  when (4) then ('Four')
  else ('between 5 and 10')
 end;

end;;
Code

Data Virtuality SQL Case code with multiple WHEN clause

SQL developers can refer to Reference Guide for numeric functions including Rand() random number function.

For SQL expressions including CASE statement again the same Reference Guide from Data Virtuality can help more to database developers.



AWS


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.