Structured SQL Functions

In Bling, SQL is used to fetch data from the underlying datasource and SQL syntax is dependent on the type of the database. This SQL can be used at the table definition level to fetch data for the table or at column definition level to define computations based on the database columns. These computations happen in the DB namespace i.e., they always refer to the database. In addition to this, we may sometimes need to incorporate conditions or computations based on variables or tables/columns defined in the users namespace that is available for the specific Bling dataset. To allow such constructs in the code, Bling provides different structured SQL functions that can be used within the table or column SQL definitions. Let us now look at these functions and what they can do.

Table Level Functions

These functions can be used within the table SQL, to generate dynamic SQL for the table based on values of parameters defined in the users namespace. Following functions are supported.

  1. bipp_if(): This can be used to create a switch-case like construct in the table SQL that enables you to generate the SQL based on conditions defined on parameter values. The syntax for this function is as follows

     bipp_if(Condition, Then, Else)
    

    Condition allows you to define the condition using other functions. Then is executed if the condition is true and Else if it is false. You can also have other bipp_if conditions nested in the Then or Else part of the function.

  2. bipp_equals(): This can be used to compare two objects in the table SQL and works like the “=” operator that returns true or false . The syntax is as follows.

     bipp_equals(compare1, compare2)
    

    Here compare and compare2 are the two objects to be compared. This would be usually used for checking the value of a parameter that would be provided by the user when visualizing data.

  3. bipp_join(): This is used to concatenate a list of strings and returns the value of the joined string. The syntax for this function is

     bipp_join([list of parameters], join condition)
    

    Here the list of parameters to be joined should be enclosed in square brackets ([]). Join condition is used to define the seperator for joining the values.

Let us now look at the above functions in action.

Consider we have data about tennis matches with the winner and loser for each match. Given a player name we should be able to determine all the matches won or lost by him. For this we have defined a parameter called ResultType in our dataset. This would allow the users to specify whether they are interested in looking at the matches won or lost when visualizing data. We have created two views in our dataset - MatchesWon and MatchesLost which will get the respective data from the Matches table in the database. These are defined as follows.

table MatchesLost
    data_source TennisData
    sql """ SELECT m.tournament, m.Surface, p.fname, p.lname, 'Lost' as Result
        FROM players.atp_matches m join players.atp_players p on m.LoserId = p.Id  """
    //column definitions
	
table MatchesWon
    data_source TennisData
    sql """ SELECT m.tournament, m.Surface, p.fname, p.lname, 'Won' as Result
                FROM players.atp_matches m join players.atp_players p on m.winnerId = p.Id """
    //column definitions

We can now use the table level structured SQL functions to use either of these definitions to fetch data based on the value of the ResultType parameter.

For this we define a third table as follows.

table PlayerMatchResult
    data_source TennisData
    sql """ $bipp_if(
             $bipp_equal(
                $bipp_join(
                        ['atp_players.$ResultType', ''],
                        ''
                ),
             'Won'
                 ),
            '{MatchesWon -}',
            '{MatchesLost -}'
            ) """     

Here bipp_join, concatenates the value for the ResultType parameter to an empty string. bipp_equal compares the joined value to the string ‘Won’. bipp_if checks if the ResultType = 'Won', then it uses the SQL from the MatchesWon table, else from the MatchesLost table.

When we view data for PlayerMatchResult table in sheets with ResultType = ‘Lost’, we get the following output.

Bipp Functions

Reftable keyword

In the above example, you can see that while the tables PlayerMatchResult is available when viewing data, the tables MatchesWon and MatchesLost are not shown on the screen. We don’t want to expose these two tables directly but only through the table PlayerMatchResult. As such we have declared them as reftable in our dataset. This means they would be used only for reference internally in the dataset. This can be achieved with the following code in the dataset definition.

dataset DataAnalysis
    table PlayerMatchResult
    reftable MatchesWon MatchesLost //these tables will not be exposed directly.

Using braces, - and alias

In the definition of PlayerMatchResult we have used '{MatchesWon -}' and {MatchesLost -} to indicate which SQL we wish to use to fetch data. Here {} imply that the tables are defined in the user namespace. The - indicates that the table SQL should be directly used as the SQL for the destination table i.e., PlayerMatchResult.sql = MatchesWon.sql, if ResultType = ‘Won’.

If - is not used, it implies that there should be an outer select x,y,z from wrapped around the table sql with the columns selected in the data viewer and required grouping. In this case you would refer to the table as '{MatchesWon}' in your target SQL. An outer SQL also ensures that the custom columns defined in the MatchesWon table in the user namespace would be included in the selection. This is not possible if the sql is directly copied to the target table i.e., when - is used.

In the above scenario, when you apply filters in the dataviewer the filter would be usually applied on the outer SQL after grouping. If you want the filters to be applied on the inner SQL before the group by clause is applied, you can define an alias for the table. This would ensure that the data would be filtered than grouped. A table can be referred to with an alias as follows.

'{MatchesWon alias=MatchesWon}'

If you wish to ensure that any other incoming filters from other tables on dashboards are also applied in this way before the group by, then you can include the list of these tables in the alias separated by a space as follows.

'{MatchesWon alias=MatchesWon atp_players player_stats}'

Here atp_players and player_stats are two other tables in the user namespace.

You can also restrict the list of columns from the MatchesWon table that will be exposed to the outer SQL by specifying them after the table name. For example in the above scenario if you want only Tournament, Surface and Result to be available with an alias, you would specify the table as follows.

'{MatchesWon Tournament Surface Result alias=MatchesWon}'	

Column Level Functions

For enhanced aggregation functionality, Bling contains a few functions that can be used within the column SQL. These functions are independent of the underlying database being used and may be used with any database or excel. Following are the functions supported.

  1. bipp_total(): This function may be used to generate the total of values for a specified integer column. It uses the SQL Sum() function internally to generate the total of values. Following is the syntax for using this function.

        column VCTotal
            sql """ bipp_total(VictimCount) """
            type int
            label OverallVictimCount
    
  2. bipp_cumulative_total(): This function may be used to generate the cumulative total of values for a specified integer column in the result set. This implies any row in the result set will have the total of values for all the previous rows and the current row. Following is the syntax for using this function.

        column VCCTotal
            sql """ bipp_cumulative_total(VictimCount) """
            type int
            label VictimCountTillNow
    
  3. bipp_percent(): This function may be used to calculate the percentage for the value of a column in the result set with respect to the overall total of the column in the same result set.

        column VCPercent
            sql """ bipp_percent(VictimCount) """
            type float
            format auto%
            label VictimCountPercentage
    
  4. bipp_cumulative_percent(): This function may be used to calculate the percentage for the cumulative value of a column in the result set with respect to the overall total of the column in the same result set.

        column VCCumPercent
            sql """ bipp_cumulative_percent(VictimCount) """
            type float
            format auto%
            label CumulativeVictimCountPercentage
    

The following image shows a result set with values corresponding to the four functions above in different columns. The result shows year-on-year values for the Victim Count, with percentage for particular year, cumulative total and percentage till that year and the overall total. Note that this result is ordered by year. Even if this was not the case, the cumulative values would be generated based on the order in which results are shown.

Bipp Functions

Last updated 24590 months ago.