CASE statement with two conditions in SQL Server 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

CASE statement with two conditions in SQL Server 2

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2008-07-24 : 20:50:34

I have the attached CASE statement for one condition but I would like to add a second statement based on this CASE block but I dont know if it can be done this way:

I would like to declare a string variable, strIns, for insurance, which is a varchar(200) - a block of text so that i could pull that variable inside a SSRS report.
the strIns, would be something like:
declare @strInsA varchar(200);
declare @strInsB varchar(200);
set strInsA = "No Insurance"
set strInsB= "You need to provide valid insurance proof."

and I would like to set this variable inside this CASE statement;
(i.e., when the client is a company...)
Since I already string together the contact name for the dropdown, can I also do this strInsA in the same block?
Thanks.
John
----------------------------------
CASE
----case 1: concatenate clientid field to companyname in drop down list box
WHEN ([Client].[CompanyName] != '' AND [Client].[CompanyName] IS NOT NULL)
AND ([Client].[osccode] IS NULL OR [Client].[osccode] = '')
AND ([Client].[ContactLastName] != '' AND [Client].[ContactLastName] IS NOT NULL)
THEN SUBSTRING([Client].[CompanyName],1,34)
+ ' ' +
+ '(' + Client.ContactLastName + ', ' + Client.ContactFirstName + ')'
+ ' '
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 11:52:18

can you explain what you're trying to get here?

jtrapat1
Starting Member

43 Posts

Posted - 2008-07-29 : 20:20:07

Heres what Im trying to do:
I have three stored procedures (see attached file)
which build upon each other to generate a two page report of a confirmation letter with a cover letter in SSRS.
Since I may have two types of clients but only one line of language is different on the two reports;
I would like to pass this line of text as a string and show or hide it depending on the type of client chosen.
I found a temporary fix but I cant get the correct value to show up on the report-
Since I need to use the same case logic for the string I need to pass,
I just repeated the same block and selected a field called strInsurance.
When I run this query in sql server, I get the correct result but when I drag it to my form, I only get the First value.
I guess its more of an SSRS question but this is strange how I drag all of the other fields from my master query and they are correct on the form.

Please Help.

Thanks
John

jtrapat1
Starting Member

43 Posts

Posted - 2008-07-29 : 20:22:16

I think it has something to do with where I place this field on the form but I don't know-
Usually, if I can get the query correct, if I drag and drop the field to the report, it is correct, but this field only returns First(Fields!strInsurance)
Heres my latest:
---

strInsurance=
CASE
----case 1: concatenate clientid field to companyname in drop down list box
WHEN ([Client].[CompanyName] != '' AND [Client].[CompanyName] IS NOT NULL)
AND ([Client].[osccode] IS NULL OR [Client].[osccode] = '')
AND ([Client].[ContactLastName] != '' AND [Client].[ContactLastName] IS NOT NULL)
THEN 'Insurance Required.'

------------case 2: concatenate clientid field to companyname in drop down list box
WHEN ([Client].[CompanyName] != '' AND [Client].[CompanyName] IS NOT NULL )
AND ([Client].[osccode] IS NULL OR [Client].[osccode] = '')
AND ([Client].[ContactLastName] IS NULL OR [Client].[ContactLastName] = '')
THEN 'Insurance Required.'
--------case 3: concatenate osccode field to agencyname in drop down list box
WHEN ([Client].[CompanyName] IS NULL OR [Client].[CompanyName] = '')
AND ([Client].[osccode] != '' AND [Client].[osccode] IS NOT NULL)
AND ([Client].[ContactLastName] IS NULL OR [Client].[ContactLastName] = '')
THEN 'NO Insurance.'
--------------case 4: concatenate osccode field to agencyname in drop down list box
WHEN ([Client].[CompanyName] IS NULL OR rtrim([Client].[CompanyName]) = '')
AND (rtrim([Client].[osccode]) != '' AND [Client].[osccode] IS NOT NULL)
AND ([Client].[ContactLastName] IS NOT NULL AND rtrim([Client].[ContactLastName]) != '')
THEN 'NO Insurance.'
--------case 5: concatenate clientid field to contactname in drop down list box
WHEN ([Client].[CompanyName] IS NULL OR RTRIM([Client].[CompanyName]) = '')
AND ([Client].[osccode] IS NULL OR RTRIM([Client].[osccode]) = '')
AND ([Client].[ContactLastName] IS NOT NULL AND rtrim([Client].[ContactLastName]) != '')
THEN 'Insurance Required.'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources