CODEWITHSUNDEEP
oracle-databasereporting-services
Zolt
Zolt

Reputation: 2811

Why do queries with parameters involving calculations not work in SSRS?

Why does this code work in SSRS without issues:

SELECT DISTINCT DEPARTMENT FROM MY_TABLE
WHERE MONTH > :O_MONTH

But this does not: 

SELECT DISTINCT DEPARTMENT FROM MY_TABLE
WHERE MONTH > trunc(:O_MONTH-30)

I'm just curious, what is the reason for this? Is there a way around this so I can embed the query for a dataset? When I add the second version of the query that "Define Query Parameter" window pops up, telling me something is wrong.

Please help. Thanks

UPDATE:

Both queries work in TOAD for example, but the second, the one I need, does not work in Visual Studio. Something about doing the deduction from the input parameter is causing issues. Please help find a way around this!

Upvotes: 0

Views: 210

Answers (2)

phonetic_man
phonetic_man

Reputation: 1088

Can you try the following options.

SELECT DISTINCT DEPARTMENT FROM MY_TABLE
WHERE MONTH > trunc(:O_MONTH) - 30;

OR 

SELECT DISTINCT DEPARTMENT FROM MY_TABLE
    WHERE MONTH > trunc(CAST (:O_MONTH AS DATE)) - 30;

OR 

SELECT DISTINCT DEPARTMENT FROM MY_TABLE
    WHERE MONTH > (CAST :O_MONTH AS DATE) - 30;

Upvotes: 1

Art Trifonov
Art Trifonov

Reputation: 220

If you are passing a string, use TO_DATE conversion. For example: trunc(to_date(:o_month,'mm-dd-yyyy'))-30.

Upvotes: 0

Related Questions