How can i query in a específic column using input parameter?

I need to consult the specific column and who will specify which column is the variable, can anyone there help me? I tried to do it that way and in test inputs I put the specific column inside "" but it didn't work.

Hi Mancciny,

Seems like you are struggling with SQL. :)

Try SELECT {PrizePool}.[Rank] FROM {PrizePool} WHERE {PrizePool}.[RangeColumn] = @RangeColumn

Remeber thatr the output structure must match the result structure.

Cheers





Mancciny wrote:

I need to consult the specific column and who will specify which column is the variable, can anyone there help me? I tried to do it that way and in test inputs I put the specific column inside "" but it didn't work.

Hello Mancciny,


Within the output structure, do you have both select fields? I advise you to put the second select whole field within the variable, for example the value of the bring variable: ", {PrizePool}. [Column name]".


Jorge Rodrigues


Hi! 

If your  @RangeColumn content  looks like "{PrizePool}.[ColumnA] = 'xxxx'" you can define @RangeColumn parameter Expand In Line as Yes


and the select would be 

   SELECT {PrizePool}.[Rank] FROM {PrizePool} WHERE  @RangeColumn

It should work, but Expand In Line =  Yes rise some security problems you must take in consideration.

hope this Help 

Graça

Hey Guys, thanks for supporting me but what is happening here is as follows: Depending on the amount of registration in aggregate I will make a query in the specific column, for example, if you have up to 5 players registered, the system will query the percentage of payouts from column "P1to5", if the number of players exceeds 5, he will make a query only on column "P6to7" and so on.

The prizepool table only needs to be consulted within my "Game" web screen
How many players will determine which column I will refer to.


Hi,

You are overcomplicating wanting to do that with advanced SQL. Just use an aggregate get the whole record and do a switch after to decide which column you want to use and save it in other variable. You can also use the ifs in a calculated attribute if you prefer.

Regards,

Marcelo

Solution

Hi Manccini,

as Maria was saying, for this to work, you set Expand Inline to Yes.

But, another design of your PricePool datamodel could avoid a lot of hassle, not only with retrieving the right payout for a given number of players, since, as it looks now, at some point you still need to translate the number of players in a game to the corresponding column name in the table, but also this design is very inflexible for changing the ranges of player numbers you want to use for defining different payout structures.


Compare that to the following datamodel : PrizePoolExample2

This allows you to retrieve the payout structure for a given number of players with a simple aggregate :



And allows you to set up different structures for different types of games, having other bounderies of the ranges, as they are not set in stone in the column names.

I would even take it a step further and design it as follows :

Good luck,

Dorine

Solution

Hey Guys, thank you all for helping here, I will back to tell you what was the best solution for me.