SQLite Select Query , For loop

Help Activities
, ,
#1

Hi, I have a query like below.

SELECT MalzemeBirlesik, MalzemeAd, CAST( sum( Ay1SatisKG) as float) as ay1SatisKG, cast (sum( Ay1IadeKG) as float) as ay1IadeKG,
CAST( sum( Ay2SatisKG) as float) as ay2SatisKG, cast (sum( Ay2IadeKG) as float) as ay2IadeKG,
CAST( sum( Ay3SatisKG) as float) as ay3SatisKG, cast (sum( Ay3IadeKG) as float) as ay3IadeKG,
CAST( sum( Ay4SatisKG) as float) as ay4SatisKG, cast (sum( Ay4IadeKG) as float) as ay4IadeKG,
FROM SKUPerformansSureciAylik GROUP by MalzemeBirlesik order by MalzemeBirlesik ASC

“Ay1SatisKG” is a column name and there are 36 of them and this query will be so long. I want to learn whether there is a way to use for loop with queries? any idea?

The usage (representative) ;

for i=1 to 36
CAST( sum( Ay+i+SatisKG) as float) as ay+i+SatisKG, cast (sum( Ay+i+IadeKG) as float) as ay+i+IadeKG

#2

Hi

May be we can first form the query statement with the loop and then pass that query with Execute query activity

For getting this expression

  1. Use a assign activity like this

str_query = “SELECT MalzemeBirlesik, MalzemeAd”

  1. Again assign activity

counter = 0

Where counter is a variable of type int32

  1. Before going for next activity create a string variable in Variable panel, named str_temp of string type

  2. Now use a WHILE activity where pass the condition like this

counter < 36

  1. Now Inside the loop use a Assign activity like this

Str_temp = “CAST( sum( Ay”+(counter + 1).ToString+”SatisKG) as float) as ay”+(counter + 1).ToString+”SatisKG, cast (sum( Ay”+(counter + 1).ToString+”IadeKG) as float) as ay”+(counter + 1).ToString+”IadeKG”

  1. Another assign activity like this

str_query = str_query + “,” + str_temp

  1. One more assign to increment the counter

counter = counter + 1

  1. Now outside the loop use a assign activity like this

str_finalquery = str_query + “,”+
“FROM SKUPerformansSureciAylik GROUP by MalzemeBirlesik order by MalzemeBirlesik ASC”

Where str_finalquery is a string variable defined in variable panel and it will have the query statement with complete 36 column names

Then we can pass this string variable as input to query activity

Hope this would help you resolve this

Cheers @Arif_Kilic

#3

Thank you for your advice but I wanted to learn that, is there a way to use for loop or while loop inside Queries

#4

In that case we must use a SQL - PROCEDURE or FUNCTION to write that loop

@Arif_Kilic

1 Like