1 Reply Latest reply on Jul 17, 2018 2:26 AM by kumar bharat

    Removing RAWSQL Queries Using Custom SQL Tables


      I am working on a horse racing dataset where for each meet (race day) I have to find the ID of the comparative meet the year before. Currently I'm using this RAWSQL query which is very costly and doesn't allow me to use the data extracts at all.


      CONTAINS(',' +

      RAWSQL_STR("SELECT LISTAGG(Vic_Meet_Id_Weekly_Comparative, ',')

                                FROM (SELECT DISTINCT dM2.Vic_Meet_ID_Weekly_Comparative

                                FROM DWH.D_MEETING AS dM2

                                WHERE DATE_PART(month, dM2.Meet_Date) = %1

                                AND DATE_PART(year, dM2.Meet_Date) = %2)",

                                MONTH([Select Month]), YEAR([Select Month])) + ',', ',' + [Vic Meet Id Weekly Comparative] + ',')


      This function basically returns a string such as "200,201,205" which contains the comparative IDs for all the meetings that occur in the selected month then does a string comparison to see if the comparative ID of the current meet is a substring of any of the IDs in this string.


      I've managed to create a table that for each year and month it attaches the string containing all the comparative IDs for meetings that occurred in that month, but I can't find a way to use THIS string joined into my datasource to filter on the comparative IDs instead of making a dynamic SQL call.


      Whatever combination of filter I try I get an error along the lines of "the formula must be an aggregate calculation or refer only to this field". I'm pretty stumped at this point so any help would be greatly appreciated.