Question Case Statement within the Where clause

[Ottawa]

I have problem that while not terribly complex, has me stumped.

I have a combobox that contains either just the person's name, or the person's name and the company. For example, the combobox could contain:

Craig Garnett
Craig Garnett, Plumbers R Us
Billy Bob
Joe Bob
Joe Bob, Electrical Services


I use the value in the combobox (cmbCust.text) as the where criteria in a select statement (SQL Express). Here is the select statement so far:


Select cusCompany, cusID, CusName = case
when cusCompany = '' then CusName
else CusName + ', ' + cusCompany End
from Customer WHERE CusName = '" & cmbCust.Text & "'
AND cusStatus = '" & varActive & "'"

This select statement almost works when just cusName is populated, but cusCompany is blank. However, I need to be able to compare a column with cmbCust when only the cusName is populated or when the cusCompany and cusName is populated. Basically I need to be able to compare either:


cusFName + ', ' + cusCompany with cmbCust.text (if there is a value in cusCompany),
- or -
cusFName with cmbCust.text (if there is no value in cusCompany).

Can I use a case statement in the WHERE clause? I have tried that as well, but can't get it to work. I am trying to do something like the following:

Select cusCompany, cusID, CusFName = case when cusCompany = '' " & _
" then CusFName else CusFName + ', ' + cusCompany End from Customer " & _
" WHERE (Case WHEN cusCompany = '' then CusFName = '" & cmbCust.Text & "' " & _
" else CusFName + ', ' + cusCompany = '" & cmbCust.Text & "' End) " & _
"AND cusStatus = '" & varActive & "'

The error message I get when adding the CASE within the WHERE clause that there is "incorrect syntax near the '='".


Thanks!

 

[IanRyder]

Hi,

Can I use a case statement in the WHERE clause?

Technically, Yes you can, but I would not recommend it and certainly not in the manner you are suggesting. I would therefore suggest keeping your SQL strings simple and do this logic testing within your project. To start, I would suggest that you do something along the following lines:-

1) Use the Split method on the cmbCust.text property to split the string on the comma character and create an ArrayOfResults.

2) If the length of the ArrayOfResults is 1 then you know that only a Persons name exists in the cmbCust.text property but if the length of the ArrayOfResults is 2 then you know that a Persons name and a Company name exists.

3) Once you have got point #2 sorted you can then use a simple If statement in your project to build the correct Where clause and add that to the SQL Statement.

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

Before worrying about your SQL issue, let's take a look at how you can improve your VB code to make it more robust and more readable. Firstly, don't use string concatenation to insert values into SQL code. Always use parameters. To learn why and how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET. Secondly, it's a good idea to use an XML literal when writing multi-line SQL code in VB because it means that you don;t have to join multiple Strings. Here's an example of using an XML literal to create the SQL code for a SqlCommand:

Dim query = <sql>
                SELECT *
                FROM MyTable
            </sql>

Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand(query.Value, connection)
    '...
End Using

As you can see, no quotes or concatenation required for the SQL code so it becomes eminently more readable and even moreso for lone queries. Here's that extended to include a parameter:

Dim query = <sql>
                SELECT *
                FROM MyTable
                WHERE Column1 = @Column1
            </sql>

Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand(query.Value, connection)
    command.Parameters.AddWithValue("@Column1", ComboBox2.Text)

    '...
End Using

On the odd occasion where you need to insert an identifier into SQL code then you can do that with an XML literal too:

Dim query = <sql>
                SELECT *
                FROM MyTable
                WHERE <%= ComboBox1.Text %> = @Column1
            </sql>

Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand(query.Value, connection)
    command.Parameters.AddWithValue("@Column1", ComboBox2.Text)

    '...
End Using

So, your code can be cleaned up quite a bit with those two techniques. As for your SQL syntax, you don't need a CASE statement at all in your WHERE clause. I would also do things a little differently in the column list. This is how I would write that query:

SELECT cusCompany,
       cusID,
       CASE
           WHEN cusCompany = '' THEN CusName
           ELSE cusCompany + ', ' + CusName
       END AS CusName
FROM Customer
WHERE cusStatus = @cusStatus
AND
(
    (cusCompany = '' AND CusName = @CusName) OR
    (CusName + ', ' + cusCompany = @CusName)
)

and this is how that would look in VB:

Dim query = <sql>
                SELECT	cusCompany,
                        cusID,
                        CASE
	                        WHEN cusCompany = '' THEN CusName
	                        ELSE cusCompany + ', ' + CusName
                        END AS CusName
                FROM Customer
                WHERE cusStatus = @cusStatus
                AND
                (
                    (cusCompany = '' AND CusName = @CusName) OR
                    (CusName + ', ' + cusCompany = @CusName)
                )
            </sql>

Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand(query.Value, connection)
    command.Parameters.AddWithValue("@cusStatus", varActive)
    command.Parameters.AddWithValue("@CusName", cmbCust.Text)

    '...
End Using

On another note, I'm a big believer is consistency in whatever you do. Is there a particular reason that some of your columns start with "cus" and some with "Cus"? That's just a bit lazy and adds that little bit of extra, unnecessary confusion to your code.

 

[jmcilhinney]

I would therefore suggest keeping your SQL strings simple and do this logic testing within your project.

I considered that too but, from the look of the SQL, it would appear that the database itself may contain the company and customer names in one column or two. As such, I think that handling both cases in SQL is the only way. Fortunately, doing that is not too hard.

 

[IanRyder]

Morning jmcilhinney,

I just automatically went with the two column thought due to the OP's use of:-

CusName + ', ' + cusCompany

but then its gets a bit confusing due to:-

CusFName + ', ' + cusCompany

Either way, I am sure both of our posts should get the OP where they need to be.

Cheers,

Ian

 

[Ottawa]

Thanks, but that won't work in this case

Thank you all for your responses and the effort you put in.

I already thought of parsing the string like you suggested, but that could cause problems if there is a comma in the name field (ie. "Craig Garnett, Esq.")

I am trying to make this very flexible for the end users so they don't have to worry about what they enter. The users of my software will not be the most computer literate.....

Also, I am curious about putting a CASE statement in the WHERE clause, and would like to get it working. I could probably recode things to avoid this whole exercise, but it would be fun and interesting to learn how to do it. I think it might also be useful for others out there.

The other option would be to do the following (not sure if this is possible...)

Select cusCompany, cusID, CusName = case when cusCompany = '' then CusName else CusName + ', ' + cusCompany End as NameCompany
from Customer WHERE NameCompany = '" & cmbCust.Text & "'
AND cusStatus = '" & varActive & "'"

However, I get the error "Invalid syntax near as"

Therefore.... any help in this would be greatly appreciated.

(Also, I will be converting this to a stored procedure with parameters later. I thought it would be easier for others to see what I'm trying to do by posting it the way I did.....)

Thanks again!!

 

[Ottawa]

Resolved

This works. Just had to rethink it a little bit. As usual, I was making it more complicated that I had to.

Select cusCompany, cusID, cusFName = case when cusCompany = '' then cusFName else CusFName + ', ' + cusCompany End from Customer " & _
" WHERE '" & cmbCust.Text & "' = case when cusCompany = '' then CusFName else cusFName + ', ' + cusCompany End " & _
" AND cusStatus = '" & varActive & "' "

Thanks!!

 

[Herman]

This will severely degrade performance of that query. For every single record you fetch you have to evaluate a conditional expression. If you select 1000 records that is AT THE VERY LEAST 4000 more operations (retrieve + compare + concat + return). You also have ignored everything everyone has posted to help you...

You should really just fetch both fields and assemble them locally.

 

[Ottawa]

This will severely degrade performance of that query. For every single record you fetch you have to evaluate a conditional expression. If you select 1000 records that is AT THE VERY LEAST 4000 more operations (retrieve + compare + concat + return). You also have ignored everything everyone has posted to help you...

You should really just fetch both fields and assemble them locally.

Thanks Herman. In this case I am not worried about performance as there will be at the very most about 100 records (probably less than 50) and I select only one at a time. That said, you are correct, I should do it the best way possible. However, I was really curious about using the case statement, and I wanted to figure it out in case one day I need to do it.

As this was the last thing to complete in the application I developed, I intend on going back and looking at it in more detail as I can now see how it can be improved. I have already cut and pasted some of the examples into my code and will use it in the near future......

Thank you to all for your advice.