none
Parameters with ODBC IBM db2 connection RRS feed

  • Question

  • Dear users

    I am seraching for help on following topic

    I set up in excel  (2013) an ODBC connection to IB DB2 database. Based on this I am running in Power Query an SQL statement like:

         Select * from dbo.MyTable

    which gives me following results:

         ID, VALUE, Date

         1,ABCD,2019-08-08

    I need to set up a parameter on VALUE so I enhanced my SQL with parameter

         Select * from dbo.MyTable where VALUE = :param1

    and set up in Power Query

         :param1 = ABCD

    When I'm trying to run Power Querry i am getting such error 

    DataSource.Error: ODBC: ERROR [07001] [IBM][CLI Driver][DB2/LINUXX8664] SQL0313N  The number of variables in the EXECUTE statement, the number of variables in the OPEN statement, or the number of arguments in an OPEN statement for a parameterized cursor is not equal to the number of values required.  SQLSTATE=07004

    Best Regards

    PLKolakoLU

    Thursday, August 8, 2019 3:58 PM

Answers

All replies

  • Hi there. Can you share the M formula text for your parameter, as well as the query where you attempt to generate the SQL statement?

    Ehren

    Thursday, August 8, 2019 7:15 PM
    Owner
  • Hi,

    Below you can see the querries

    main:

    = Odbc.Query("dsn=DPMHA", "SELECT#(lf)p.KUNDENNR as ID#(lf),LISTAGG (CAST(CONCAT(TRIM(pm.KUNDENNR), CONCAT (' ',jm.FIRMENNAME)|| ' - ' ||'DAUGHTER')as clob),CHR(10)) as OUTPUTSTRING#(lf)--,'DAUGHTER' as TYPE#(lf)FROM abs.TPERSON p#(lf)join abs.TJURPERS j on p.PERS# = j.PERS##(lf)join abs.TKONZMITGL km2 on j.JP# = km2.JP#_MUTTER#(lf)left join abs.TJURPERS jm on km2.JP#_TOCHTER = jm.JP##(lf)left join abs.TPERSON pm on jm.PERS# = pm.PERS##(lf)where p.KUNDENNR in :param1#(lf)GROUP BY p.KUNDENNR")

    parameter:

    "1231261" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

    Please Keep in mind I am connected to IBM DB2 not to MS SQL but I assume it is not a db issue cos I can run the main querry without any erros when I put p.KUNDENNR in ('1231261','1226354')stead of :param1

    Thanks a lot

    PLKolakoLU

    Friday, August 9, 2019 8:47 AM
  • Try this:

    "SELECT
    p.KUNDENNR as ID
    ,LISTAGG (CAST(CONCAT(TRIM(pm.KUNDENNR), CONCAT (' ',jm.FIRMENNAME)|| ' - ' ||'DAUGHTER')as clob),CHR(10)) as OUTPUTSTRING
    --,'DAUGHTER' as TYPE
    FROM abs.TPERSON p
    join abs.TJURPERS j on p.PERS# = j.PERS#
    join abs.TKONZMITGL km2 on j.JP# = km2.JP#_MUTTER
    left join abs.TJURPERS jm on km2.JP#_TOCHTER = jm.JP#
    left join abs.TPERSON pm on jm.PERS# = pm.PERS#
    where p.KUNDENNR in "&:param1&"
    GROUP BY p.KUNDENNR"

    The problem being, the query is a string but :param is an object/parameter that belongs to PQ. You have to end the string and call the parameter, and then resume the query string

    And in my experience, comments don't work well so you might wanna remove "--,'DAUGHTER' as TYPE" or place the comment outside of the query string.

    Friday, August 9, 2019 12:20 PM
  • Hi

    Thank you very much for help

    Now my query looks like:

    = Odbc.Query("dsn=DPMHA", "SELECT#(lf)p.KUNDENNR as ID#(lf),LISTAGG (CAST(CONCAT(TRIM(pm.KUNDENNR), CONCAT (' ',jm.FIRMENNAME)|| ' - ' ||'DAUGHTER')as clob),CHR(10)) as OUTPUTSTRING#(lf)FROM abs.TPERSON p#(lf)join abs.TJURPERS j on p.PERS# = j.PERS##(lf)join abs.TKONZMITGL km2 on j.JP# = km2.JP#_MUTTER#(lf)left join abs.TJURPERS jm on km2.JP#_TOCHTER = jm.JP##(lf)left join abs.TPERSON pm on jm.PERS# = pm.PERS##(lf)where p.KUNDENNR in "&:param1&" #(lf)GROUP BY p.KUNDENNR")

    I got the error

     Expression.SyntaxError: Token Literal expected

    :(

    Monday, August 12, 2019 2:23 PM
  • Does ":param1" really have a colon in front of it? If so, then the M should be:

    "...in "& #":param1" & " #(lf)GROUP BY..."

    If not, then the M should be:

    "...in "& param1 & " #(lf)GROUP BY..."

    Ehren

    Tuesday, September 3, 2019 7:32 PM
    Owner