Expressions

Question

Post reply

Expressions

Mike Levan

SSChampion

Points: 14201
More actions
March 15, 2006 at 9:32 am

#111449

When i add the following code to my text box as an expression, it doesnt work, why?
SELECT
CASE WHEN address2 IS NULL
THEN address1
ELSE address2
END
FROM tablename

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Haven't tested this, but try:

SELECT 'Address' =

CASE WHEN address2 IS NULL

THEN address1

ELSE address2

END

FROM tablename

John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

... or, even better:

SELECT COALESCE(address2, address1)

FROM tablename

Mike Levan SSChampion Points: 14201 More actions · Answer 3

I am ok with my select statement, i got the result i need but when when i add this select to my expression in my report its not working. not sure if i am adding it in right way to my expression in report.

Thanks!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

OK, I see from your thread on SQL-Server-Performance that your problem is that only the first row of the result set is displayed in your app. If you say what you're using to design your app and provide the code that is connecting to the database and running the query then I'm sure someone will be able to help you. I'm afraid it's not my area, though.

John

Mike Levan SSChampion Points: 14201 More actions · Answer 5

what do u mean by app, I am doing these in my sql server reporting services.

moe_mayne SSCommitted Points: 1933 More actions · Answer 6

=IIF(fields!address2.value = '',fields!address1.value,fields!address2)

Moe C

Tom Schuettke SSCarpal Tunnel Points: 4127 More actions · Answer 7

I ran into this same problem. SSRS allows functions in the text box (like Moe's IIF example). But if you have a large nested IIF function, it can get really ugly. If you want to use a case statement, you have to create it in a custom function in the code window in Report Proerties. You can then call the code from the textbox using the code member (Code.YourFunctionName()).

Martin N Ten Centuries Points: 1216 More actions · Answer 8

Why not add the CASE Statement to your Stored Procedure data source and just return it to the report as a field... might wotk better...

Kindest Regards,

Martin