jtrapat1
asked on
Complicated Select Case - When Statement
This is a case where I have two fields: CompanyName and AgencyName;
I display the results in a GridView but in some cases, because we have three different user groups,
the CompanyName may be blank or the AgencyName may be blank.
I try to take care of this logic on the back end but I would like to display one column on the Grid:
Company/Agency.
i thought I could do this with a SELECT...CASE.....WHEN statement but I get an error when I .
-------------------------- ---------- -
select Companyname, AgencyName
CASE
WHEN CompanyName <> ' ' THEN AgencyName
ELSE
WHEN AgencyName <> ' ' THEN CompanyName
END
from client
-------------------------- ---------- --
P.S.
If I do get this working, will it be a problem when I try to sort by the column header?
Or, could I just give agencyname/companyname an alias?
I display the results in a GridView but in some cases, because we have three different user groups,
the CompanyName may be blank or the AgencyName may be blank.
I try to take care of this logic on the back end but I would like to display one column on the Grid:
Company/Agency.
i thought I could do this with a SELECT...CASE.....WHEN statement but I get an error when I .
--------------------------
select Companyname, AgencyName
CASE
WHEN CompanyName <> ' ' THEN AgencyName
ELSE
WHEN AgencyName <> ' ' THEN CompanyName
END
from client
--------------------------
P.S.
If I do get this working, will it be a problem when I try to sort by the column header?
Or, could I just give agencyname/companyname an alias?
>> If I do get this working, will it be a problem when I try to sort by the column header?
Or, could I just give agencyname/companyname an alias?
No, it won;t be a problem - Just give an alias to this column and include that alias in the ORDER BY clause.
Or, could I just give agencyname/companyname an alias?
No, it won;t be a problem - Just give an alias to this column and include that alias in the ORDER BY clause.
Note that even if multiple conditions are true in the CASE, the action for the first condition that evaluates to true will be executed i.e. if CompanyName <> ' ' and AgencyName <> ' ', then AgencyName will be returned since it was evaluated first. If both are false, then the value specified in the ELSE part will be returned (NULL in ur case).
ASKER
I tried this but I get an error:
Incorrect syntax near the keyword 'CASE'.
Maybe Im missing a comma somewhere.
John
Incorrect syntax near the keyword 'CASE'.
Maybe Im missing a comma somewhere.
John
Ohh SQL Server, I guess CASE can't be used like that. You will have to use an IIF construct.
SELECT Companyname, AgencyName,
IIF(CompanyName <> ' ', AgencyName, IIF(AgencyName <> ' ', CompanyName, 'Both are empty')) AS Temp
FROM client
ORDER BY Temp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Case can be used you are setting the Alias wrong.
...AS AgencyName
OR
"AgencyName"=Case...
...AS AgencyName
OR
"AgencyName"=Case...
select Companyname, (CASE
WHEN CompanyName <> '' THEN AgencyName
WHEN AgencyName <> '' THEN CompanyName
END) AS AgencyName
from client
sorry thought you were setting an alias to get the field in the AgencyName column. jinesh_kamdar is correct all you are missing is a comma.
select Companyname, AgencyName, (CASE
WHEN CompanyName <> '' THEN AgencyName
WHEN AgencyName <> '' THEN CompanyName
END) AS MergedName
from client
@jtrapat1: Don't u think Angel deserved the credit (read points) since he was the one to correct ur CASE syntax in the first place ???
it was 2 things. a comma problem and the ELSE. I think you had both.
angel forgot the comma and also added a space which did not work. I fixed both, and did not add the space.
angel forgot the comma and also added a space which did not work. I fixed both, and did not add the space.
@mfsamuel:
>> angel forgot the comma
The asker himself had fogotten the comma in his question. It is not an error that Angel introduced by himself.
>> I fixed both, and did not add the space
The space was specified in the question itself and so it SHOULD have been included in the solution as well - so technically speaking, ur final solution was also incorrect.
My concern here is not wat all problems u solved, but wat prompted u to solve those problems and in that regard, Angel was the first to point in the right direction.
>> angel forgot the comma
The asker himself had fogotten the comma in his question. It is not an error that Angel introduced by himself.
>> I fixed both, and did not add the space
The space was specified in the question itself and so it SHOULD have been included in the solution as well - so technically speaking, ur final solution was also incorrect.
My concern here is not wat all problems u solved, but wat prompted u to solve those problems and in that regard, Angel was the first to point in the right direction.
>>The asker himself had fogotten the comma in his question. It is not an error that Angel introduced by himself.
But that was 1/3 the reason for the query not working, so if it was not fixed the answer was not correct.
>> The space was specified in the question itself and so it SHOULD have been included in the solution as well - so technically speaking, ur final solution was also incorrect.
QUOTE: the CompanyName may be blank or the AgencyName may be blank.
That means he did not intend to have the space.
>>My concern here is not wat all problems u solved, but wat prompted u to solve those problems and in that regard, Angel was the first to point in the right direction.
There were 3 problems as I see in and my solution had all 3 corrected. You and angel should both have assisted solution. If the space was supposed to be there then you should get credit (for fixing the comma), angel should ge the assit, and I should get nothing.
But that was 1/3 the reason for the query not working, so if it was not fixed the answer was not correct.
>> The space was specified in the question itself and so it SHOULD have been included in the solution as well - so technically speaking, ur final solution was also incorrect.
QUOTE: the CompanyName may be blank or the AgencyName may be blank.
That means he did not intend to have the space.
>>My concern here is not wat all problems u solved, but wat prompted u to solve those problems and in that regard, Angel was the first to point in the right direction.
There were 3 problems as I see in and my solution had all 3 corrected. You and angel should both have assisted solution. If the space was supposed to be there then you should get credit (for fixing the comma), angel should ge the assit, and I should get nothing.
V - I think the Asker had requested for a split. Why was then only my comment accepted as the solution? I think a split would only be fair to everyone involved.
Open in new window