Link to home
Start Free TrialLog in
Avatar of jtrapat1
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?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

here we go:
select Companyname, AgencyName 
        CASE
              WHEN CompanyName <> ' ' THEN AgencyName     
              WHEN AgencyName <> ' ' THEN CompanyName     
            END
from client 

Open in new window

>> 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.
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).
Avatar of jtrapat1
jtrapat1

ASKER

I tried this but I get an error:
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Case can be used you are setting the Alias wrong.

...AS AgencyName
OR
"AgencyName"=Case...
select Companyname, (CASE
              WHEN CompanyName <> '' THEN AgencyName     
              WHEN AgencyName <> '' THEN CompanyName     
            END) AS AgencyName
from client

Open in new window

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

Open in new window

@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.
@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.
>>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.
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.