Using two different columns in a Where statement depending on if one is empty

Question

I've currently got a WHERE clause in a SQL string that looks like this:

CmdTxt = CmdTxt + "Where FA.CSE_LAN = '" + UID + "' ";

It eventually culminates in a call to Oracle:

OracleCommand cmd = new OracleCommand(CmdTxt, connection);
cmd.Connection = connection;

cmd.CommandText = CmdTxt;
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);

da.Fill(dt);

What I now need to do is change that WHERE logic, so that it first looks at a column called FA.BUILD_CSE_LAN. If that has a value, it will use that column in the WHERE clause.

If it doesn't have a value, it will use the column FA.CSE_LAN in the WHERE clause.

How can I do this? I'm using C# code-behind in an ASP.Net environment against an Oracle 12c database, if any of that is important.

You need to [use parameterized queries](http://stackoverflow.com/q/7505808/557445). Your existing code, as Will pointed out, can leave you vulnerable to SQL Injection attacks. — Rion Williams, Feb 06 '17 at 21:32
While I agree parameterized queries are important, assuming UID is either a UniqueID or UserID, generally those don't come from string inputs. Potentially userID but then that would actually be a user name. — Andrew, Feb 06 '17 at 21:36
I agree with parameterized queries, but it's an intranet and this is the way they do things here. I'm more concerned with the syntax than the variables. — Johnny Bones, Feb 06 '17 at 21:45
It may not be as important due to the trusted nature of the users, and depending on the source of the values, but it is always better practice. Though I admit even in the code we use in my workplace we sometimes just put values right into the string, but those values came from a database already. — Andrew, Feb 06 '17 at 21:47
BTW, UID is set in code and is read from WindowsIdentity.GetCurrent().Name, so there really is no chance for injection. — Johnny Bones, Feb 06 '17 at 21:49

score 1 · Answer 1 · answered Feb 06 '17 at 21:32

I am no expert on oracle but I would try something like this.

CmdTxt = CmdTxt + "Where FA.BUILD_CSE_LAN = @UID  OR ( FA.BUILD_CSE_LAN IS NULL AND FA.CSE_LAN = @UID)";

That way it if your build field is null, no value, it checks the other one. And declare @UID as a parameter on your command to avoid injection or special character issues.

score 1 · Answer 2 · edited Sep 15 '17 at 12:55

1

This doc on Oracle might help: NVL or COALESCE could help here.

CmdTxt = CmdTxt + "Where NVL(FA.BUILD_CSE_LAN, FA.CSE_LAN) = '" + UID + "' ";

or

CmdTxt = CmdTxt + "Where COALESCE(FA.BUILD_CSE_LAN, FA.CSE_LAN) = '" + UID + "' ";

edited Sep 15 '17 at 12:55

MT0

143,790
11
59
117

answered Feb 06 '17 at 22:03

JeromeFr

1,869
2
17
20

Using two different columns in a Where statement depending on if one is empty

2 Answers2