Paramater @{ClaimNum} at runtime yields no errors, but no results.

[Amy_Young-Leith]

This seems simple enough. When I replace a static value in the query with the notation @{ClaimNum} for an inputp parameter and use the same value (or any other value), no results. No errors, and I displayed debugging information in the query entry.

I've perused Community. I reviewed the RepDashboards1700 MRG. This looks to be proper. When Configuring the Data Provider I get no errors. I've confirmed the data type (string) in Config. But... no results.

select
i.itemnum DOCHANDLE, max(i.datestored) DATESTORED, max(F.FILETYPESTR) FILETYPE, count(P.ITEMPAGENUM) PAGES, sum(P.FILESIZE) FILESIZE
from
hsi.keyitem114 k
left join HSI.ITEMDATAPAGE p on K.ITEMNUM = P.ITEMNUM
left join HSI.FILETYPE f on P.FILETYPENUM = F.FILETYPENUM
left join HSI.itemdata i on K.ITEMNUM = i.itemnum
where
K.KEYVALUECHAR = '@{ClaimNum}'
group by I.ITEMNUM

And the original query which in the Data Provider returns data:

select
i.itemnum DOCHANDLE, max(i.datestored) DATESTORED, max(F.FILETYPESTR) FILETYPE, count(P.ITEMPAGENUM) PAGES, sum(P.FILESIZE) FILESIZE
from
hsi.keyitem114 k
left join HSI.ITEMDATAPAGE p on K.ITEMNUM = P.ITEMNUM
left join HSI.FILETYPE f on P.FILETYPENUM = F.FILETYPENUM
left join HSI.itemdata i on K.ITEMNUM = i.itemnum
where
K.KEYVALUECHAR = '8464530'
group by I.ITEMNUM

Can someone point out what I'm missing!? Thanks!

[Saravanan_J]

Did you tried Providing Numeric as the data type on the config and check using the '@{ClaimNum}' on your select list.

Usually I don't see the results in the dataprovider window when using dynamic paratmeters but I get the values on the dashboard.

[Todd_Bailey]

Amy,

When configuring parameters for the Custom SQL data type.  When you select the input parameter to be a String, it will append the single quotes into the SQL statement as Jason pointed out.  One thing to note, is if you are looking for values to be displayed in the Preview Results wizard page, you will need to select "Allow using default values at runtime".  Otherwise, you'll need to put your data provider on a dashboard and select/enter in a value to see results.

Thank you,

Todd Bailey