none
How to use parameter query or list to build dynamic SQL source statement RRS feed

  • Question

  • Hi,

    My source is a SQL Database.

    In my SQL statement I need to have a WHERE ... IN clause.

    The value in the IN is dynamic and must come from a query.

    As a workaround I thought about using a parameter, from query :

    and then using it for filtering rows :

    The problem is that it's not multi-value...

    How could i achieve this ?

    Thanks for any help,

    Regards


    Bertrandr

    Tuesday, February 14, 2017 10:15 AM

Answers

  • Hi Bertrand,

    The solution I provided doesn't use a parameter query. In this case, we would just use the Territory Table for the "IN" values. So the step should read:

    FilteredTable = Table.SelectRows(
    Source,
    each List.Contains(
    Territory[SalesTerritoryCountry],
    [EnglishCountryRegionName]
    )
    )


    • Marked as answer by Bertrandr Wednesday, February 15, 2017 2:56 PM
    Wednesday, February 15, 2017 2:51 PM

All replies

  • Let's say that your "parameter" query is a one column table with a table name of Country and a column name of ListCountry.

    In your main query, lets' assume that you have a column called CountryName. You can add a step like:

    FilteredTable = Table.SelectRows(<previousStepName>, each List.Contains(Country[ListCountry], [CountryName]))

    Tuesday, February 14, 2017 5:04 PM
  • Hi Colin,

    Thanks for your answer.

    I still have an issue.

    Some details about my file.

    All my queries :

    Territory table contains list of countries :

    Based on this table i created a list "SalesTerritoryCountry" :

    This list is the source query of my parameter "ListCountry" :

    I tried your code :

    but i guess i'm still doing a mistake as I'm getting the following error :


    Bertrandr

    Wednesday, February 15, 2017 11:03 AM
  • Hi Bertrand,

    The solution I provided doesn't use a parameter query. In this case, we would just use the Territory Table for the "IN" values. So the step should read:

    FilteredTable = Table.SelectRows(
    Source,
    each List.Contains(
    Territory[SalesTerritoryCountry],
    [EnglishCountryRegionName]
    )
    )


    • Marked as answer by Bertrandr Wednesday, February 15, 2017 2:56 PM
    Wednesday, February 15, 2017 2:51 PM
  • Thank you so much Colin !

    It's working perfect now :)


    Bertrandr

    Wednesday, February 15, 2017 2:56 PM