SSRS 2005 - Simplify a Where Statement in Query 1

[dianemarie]

Hello, my report allows the user to choose whether or not they want to show canceled orders on the report via a parameter @CNX (Prompt: Show Canceled Orders?). The database field oh.cnx is a char(1) field. If it is blank, the order is not canceled. If it has a letter character (we have almost a full alphabet of possibilities, A-Z) it is a canceled order. After some help from my good friends at Tek-Tips and trial and error, I have gotten this to work with the WHERE query statement shown below (currently with only the first 3 letters). Before I write this out with all the letters, is there a way to simplify it? Also, if I don't add the very last *else ' '* then it doesn't work and I'm not sure why (got that part from a Tek-Tips contributor). Thank you for any help. Diane

WHERE
(CASE @CNX
WHEN 'Y' THEN
CASE oh.cnx
WHEN 'A' THEN 1
WHEN 'B' THEN 1
WHEN 'C' THEN 1
WHEN '' THEN 1
ELSE 0
END
WHEN 'N' THEN
CASE oh.cnx
WHEN 'A' THEN 0
WHEN 'B' THEN 0
WHEN 'C' THEN 0
WHEN '' THEN 1
ELSE 0
end
else ' '
END) = 1

 

[RiverGuy]

Sites like

http://www.asciitable

are your friend. T-SQL has an ASCII function to convert a character into it's ASCII code number. So care of letters, you could do the following:

WHEN ASCII(oh.cnx) BETWEEN 65 AND 90 OR ASCII(oh.cnx) BETWEEN 97 AND 122 THEN 1

 

[dianemarie]

Well this sounds promising, and as the field is forced uppercase I don't have to worry about the lower cases. I am working with this query directly on the datatab in report designer, and it doesn't like it. The message says: TITLE: Microsoft Report Designer
------------------------------

Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.


------------------------------
ADDITIONAL INFORMATION:

Incorrect syntax near the keyword 'BETWEEN'. (Microsoft SQL Server, Error: 156)

This is how I have it now. Do you see anything obvious?

(CASE @CNX
WHEN 'Y' THEN
CASE oh.cnx
WHEN ASCII(oh.cnx) BETWEEN 65 AND 90 then 1
WHEN '' THEN 1
ELSE 0
END
WHEN 'N' THEN
CASE oh.cnx
WHEN ASCII(oh.cnx) BETWEEN 65 AND 90 then 0
WHEN '' THEN 1
ELSE 0
end
else ' '
END) = 1

 

[RiverGuy]

Try the following:

 CASE 
                WHEN ASCII(oh.cnx) BETWEEN 65 AND 90 then 1
                WHEN oh.cnx = '' THEN 1
                ELSE 0
            END

(Removing the oh.cnx from the declaration of the CASE statement).

 

[dianemarie]

Sweet! Many many thanks to you.