Forum Discussion

lukaszbrdon's avatar
lukaszbrdon
Frequent Visitor
3 years ago

Multi value in one dynamic M query parameter

Hi all,   I need to pass multiple values to single parameter which is added as 'where' clause to query. There is no static list of these values. It's dynamically changing. I'm receiving values to p...
Need Help
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Hi all,


I have sth like that for now.

let

   

    PARAMETER_LIST  =

 

if

    //check to see if the parameter is a list

      Type.Is(

        Value.Type(PARAMETER),

        List.Type

      ) then

        //if it is a list

        let

          //add single quotes around each value in the list

          AddSingleQuotes = List.Transform(

              PARAMETER,

              each "'" & _ & "'"

            ),

          //then turn it into a comma-delimited list

          DelimitedList = Text.Combine(

              AddSingleQuotes,

              ","

            )

        in

          DelimitedList

    else

 

    "'"& PARAMETER_LIST  &"'",

  

    Source = Oracle.Database("XXX [HierarchicalNavigation=true, Query="
Select * from xxx join xxx on bbb=aaa

       where column ='"& PARAMETER_LIST &"'"])

in

    Source

but I'm receiving the error like below:

DataSource.Error: Oracle: ORA-00933: SQL command not properly ended
Details:
DataSourceKind=Oracle
DataSourcePath=xxx
Message=ORA-00933: SQL command not properly ended
ErrorCode=-2147467259

 

Without "where" clause Idon't get error.

 

Can anybody advice?

DouglasABailey's avatar
DouglasABailey
Advocate I
3 years ago

This might be too late for you but it could help someone else.

 

I think in the Where clause you should use IN not '='. And add brackets around the list: 

Use:  Where column IN ("& PARAMETER_LIST &")

So it would give you: Where column IN ('a','b')

Instead of giving you: Where colum = 'a','b'