Click here to Skip to main content
15,907,001 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
DECLARE @parameter int
select @parameter = U.ProfileID ,

Case when (SELECT P.ProfileID FROM TM_profile P WHERE P.ProfileCategoryID in (2546 , 2547) AND P.ProfileID=@parameter)
Then
          SELECT DISTINCT U.fullname FROM TM_profile P
          JOIN TM_User U ON U.ProfileID= P.ProfileID
          WHERE P.ProfileCategoryID IN (2545,2544,2543) AND U.Is_Active=1



when (SELECT P.ProfileID FROM TM_profile P WHERE P.ProfileCategoryID=2544 AND P.profileid=@parameter)
Then
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2543) AND U.Is_Active=1


When (SELECT P.ProfileID FROM TM_profile P WHERE P.ProfileCategoryID=2545 AND P.profileid=@parameter)
Then
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2544,2543) AND U.Is_Active=1



When (SELECT P.ProfileID FROM TM_profile P WHERE P.ProfileCategoryID=2543 AND P.profileid=@parameter  )
Then
        SELECT distinct FullName FROM TM_profile P
        join TM_User U on U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID in (2544) and U.Is_Active=1

END
from TM_User U where U.TM_UserID=409
Posted
Updated 26-Feb-14 17:13pm
v5
Comments
Member 10501509 16-Jan-14 2:27am    
can any one modify the query to get the result

I can't fix it for you - I have no idea what you are trying to do, or hair your tables work - but... The problem is fairly clear.
SQL CASE statements need a boolean condition:
SQL
CASE WHEN condition THEN value
your SELECTs do not evaluate to a boolean, so SQL rightly complains.
 
Share this answer
 
Comments
Member 10501509 16-Jan-14 3:41am    
i am getting this error when i execute the above query :-A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
The error is quite clear.
Certainly you can not include a variable getting a value from the database along with data retrieval.

If I understand your scenario correctly, you want a standard profile ID but a different full name based on profile category ID. There are two approaches you can use.

1. Run two SQL queries. One for data retrieval and one for the value retrieval in the variable

2. Insert the values in a temporary table and then select from the temporary table
Example:
SQL
declare @tempTable table (profileID int, fullname varchar(100))

insert into @tempTable

select U.ProfileID,

Case when (P.ProfileCategoryID in (2546 , 2547) AND P.ProfileID=@parameter)
Then(
          SELECT DISTINCT U.fullname FROM TM_profile P
          JOIN TM_User U ON U.ProfileID= P.ProfileID
          WHERE P.ProfileCategoryID IN (2545,2544,2543) AND U.Is_Active=1)



when (P.ProfileCategoryID=2544 AND P.profileid=@parameter)
Then(
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2543) AND U.Is_Active=1)


When (P.ProfileCategoryID=2545 AND P.profileid=@parameter)
Then(
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2544,2543) AND U.Is_Active=1)



When ( P.ProfileCategoryID=2543 AND P.profileid=@parameter  )
Then(
        SELECT distinct FullName FROM TM_profile P
        join TM_User U on U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID in (2544) and U.Is_Active=1)

END
from TM_User U
JOIN TM_profile P ON U.ProfileID= P.ProfileID
where U.TM_UserID=106

select * from @tempTable


I don't know your circumstances for the DB design but please make sure the design is correct in order to prevent further hurdles in getting the data out for reporting purposes or any other purposes if any...

Happy coding..!!

-Nayan
 
Share this answer
 
Comments
Christian Graus 16-Jan-14 19:30pm    
I think perhaps a mapping table or a column with ids to the right name to pull out, might make sense. The first question though is, why do I want the adjacent name in certain instances ?
You cannot nest a select like that. I have no idea what you're hoping to do, but it's utterly broken.

First:

SQL
when (P.ProfileCategoryID=2544 AND P.profileid=@parameter)
Then(
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2543) AND U.Is_Active=1)



You seem to want to grab a different full name based on another id. If this is correct, it's certainly arcane and an indication that your DB is broken

Secondly, you just cannot nest a select like this. It does not work. The syntax is utterly wrong and broken. You need to post a new question, explaining your data structure ( preferably with scripts to create and populate the tables so we can help you ), and what it is you're trying to do.

This looks like you made something up at random.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900