Multi Parameter DB2 query for oledb dataset in SSRS.

• Learner093

  Old Hand

  Points: 359

  More actions

  July 25, 2013 at 2:51 pm

  #296008

  I am trying to execute a DB2 query in a SSRS dataset using the oledb data provider. I have written the query as where parameter values are replaced by :

  SELECT

  Count(*) RecordCount

  FROM HBIDATA.DTAMTH M

  WHERE (M.DXMTSTG = 0 or M.DXMTSTG is NULL) and (M.ABILSYS = 'STG' or M.ABILSYS = 'WHOLESALE') and (M.EFDATE8 <= Replace(Cast((date(?) + 30 days) as Char(10)), '-', ''))

  AND CRDT <= ?

  where '?' are to be replaced by the parameter values.

  I have also tried writing this query as an expression, but it gives some error messages which does not imply the error clearly.

  Th expression I have written is below, but it is somewhere wrong.

  = "SELECT Count(*) RecordCount FROM HBIDATA.DTAMTH M WHERE (M.DXMTSTG = 0 or M.DXMTSTG is NULL) and (M.ABILSYS = 'STG' or M.ABILSYS = 'WHOLESALE') and (M.EFDATE8 <= Replace(Cast((date(" & Parameters!BatchDateDMY.Value ") + 30 days) as Char(10)), '-', '')) AND CRDT <='" & Parameters!BatchDateISO.Value "'"

  Please let me know how you would have written this in SSRS query/expression. Thanks!

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  July 26, 2013 at 1:24 am

  #1636061

  Please do not crosspost. It wastes peoples time and fragments replies. Original post can be found here:

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP