0

Query works and sorts as it should. But query errors when part of ssrs dataset. Errors narrowed down to order by case expressions as shown. Syntax is accepted. First error message during run: "An error has occurred during report processing. (rsProcessingAborted)" The Details>> button for the error popup shows the try n errors below.

select  <columns>
from    <view>
where   <criteria>
order by 

> error condition - try 1
        -- errors "The isnull function requires 2 argument(s)."
        case when isnull(<parm>, '') <> '' 
             then <column to be sorted>
        end, 

> error condition - try 2
        -- errors "An expression of non-boolean type specified in a context where a condition is expected, near ..."
        case when (<parm> is not null  or 
                   <parm> <> '')
             then <column to be sorted>
        end, 

> error condition - try 3
        -- errors "An expression of non-boolean type specified in a context where a condition is expected, near ..."
        case when <parm> is not null 
             then <column to be sorted>
             when <parm> <> ''
             then <column to be sorted>
        end, 

        <more columns to sort>
Gary Thomann
  • 576
  • 6
  • 17

1 Answers1

0

option 1: run you whole query as is and insert data into temp table. Then you can do if else based execution based on your desired column of order by; by first checking in the if statement for null value on specific columns(s) and then running select with desired order by. Option 2: take care of those nulls in an enforced way i.e. check for them before you insert into temp table with a isNull constraint e.g. Col_1 = isnull(Col_1,'N/A') .This way you will have a definitive way to filter your data by 'N/A'. And then do if-else based checks for order by. Option 2 is easy to QA i.m.o.

junketsu
  • 533
  • 5
  • 17