[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Complicated Select Case - When Statement

Posted on 2008-01-31
14
Medium Priority
?
1,412 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:jtrapat1
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20786475
here we go:
select Companyname, AgencyName 
        CASE
              WHEN CompanyName <> ' ' THEN AgencyName     
              WHEN AgencyName <> ' ' THEN CompanyName     
            END
from client 

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20786488
>> 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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20786525
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).
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:jtrapat1
ID: 20786563
I tried this but I get an error:
Incorrect syntax near the keyword 'CASE'.

Maybe Im missing a comma somewhere.

John
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20786616
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

0
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 500 total points
ID: 20786633
I guess u missed a comma (,) after the AgencyName field in Angel's suggestion.
select Companyname, AgencyName, ---> added this comma
        CASE
              WHEN CompanyName <> ' ' THEN AgencyName     
              WHEN AgencyName <> ' ' THEN CompanyName     
        END
from client 

Open in new window

0
 
LVL 5

Expert Comment

by:mfsamuel
ID: 20786642
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

0
 
LVL 5

Expert Comment

by:mfsamuel
ID: 20786674
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

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20788199
@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 ???
0
 
LVL 5

Expert Comment

by:mfsamuel
ID: 20788240
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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20788293
@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.
0
 
LVL 5

Expert Comment

by:mfsamuel
ID: 20788589
>>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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20815096
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.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question