It looks like the original Access logic has one DB row with three date fields, [RWT010], [RWT010BP], and [RWP000]. But in Oracle that's been normalized so you're getting back multiple rows, each of which has a a datetime field named [DATE_LATEST], and then name field called [TYPE] that's equal to "RWT010", "RWT010BP", or "RWP000".
And you were thinking, you want to handle those RWP000 date values by name, just like in Access. You were right, that's the clearest way to do it, and I'll show you how. I misunderstood what you were asking.
One way to do this would be to write an Oracle stored procedure that duplicates the Access logic. That's not the question you asked, but it's a legitimate way to do it. However, it would be more complicated than the Access version due to the change in the database, and anyway I haven't written Oracle SQL in years and I don't have an Oracle server handy to give me arbitrary, cryptic syntax errors about semicolons and whitespace.
So what I'm going to do is write a loop in C# to grab the datetimes from the DB rows and put them in local variables, and then duplicate the Access logic in C# using those variables instead of fields. It'll be a little verbose compared to the Access version, but sometimes that's how it goes.
int idnum = Convert.ToInt32(Console.ReadLine());
string sql = "SELECT EXPID, TYPE, DATE_LATEST FROM TRAINING_TABLE where expid =" + idnum;
// I don't know how you're using this so I'll just declare it here
// and leave that to you.
String dateLabel = "";
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
using (DbDataReader reader = cmd.ExecuteReader())
{
DateTime? RWT010 = null;
DateTime? RWT010BP = null;
DateTime? RWP000 = null;
// No need to check reader.HasRows. If it has no rows, reader.Read()
// will return false the first time, that's all.
while (reader.Read())
{
// Doesn't look to me like expid is used
//int expid = reader.GetInt32(0);
string trainType = reader.GetString(1);
DateTime trainDate = reader.GetDateTime(2);
switch (trainType) {
case "RWT010":
RWT010 = trainDate;
break;
case "RWT010BP":
RWT010BP = trainDate;
break;
case "RWP000":
RWP000 = trainDate;
break;
}
}
if (RWT010 == null || RWT010BP > RWT010) {
dateLabel = String.Format("RWT010BP: {0:d}", RWT010BP);
} else {
dateLabel = String.Format("RWT010: {0:d} & RWP000: {1:d}", RWT010, RWP000);
}
}
The original logic was this:
If RWT010 isn't null,
Do A
Otherwise, if RWT010BP > RWT010
ALSO do A
But if none of the above,
Do B
The first two branches do the exact same thing, so we can condense them both into one branch.
"Don't Repeat Yourself", as they say. You don't want to return to this code a year from now, wonder if those two lines were required to be the same, and then guess wrong or else not notice that they are the same, and only change one or the other. It's just a mess.
If you're not familiar with String.Format(), there's a lot to it. In the first argument string, {0} means "insert the second argument here"; {1} means "insert the third", and so on. The ":d" inside the curly braces is optional; it means to pass "d" as format information to the value its inserting. DateTime will interpret that "d" to mean "Short Date". You could also do it like this:
dateLabel = String.Format("RWT010BP: {0}", RWT010BP.Value.ToShortDateString());
Or like this:
dateLabel = "RWT010BP: " + RWT010BP.Value.ToShortDateString();
I have to use RWT010BP.Value in that line instead of just RWT010BP because RWT010BP is declared with a ? after it. That makes it a "nullable" value. A regular DateTime can't be null, but we need to accommodate nulls here.
If you're using C#6, you can do it like this, which I prefer. I didn't use it above because I don't know what version of C# you're on. Always prefer the least amount of "noise" cluttering up the code.
dateLabel = $"RWT010BP: {RWT010BP:d}";
That's the same ":d" as in String.Format("{0:d}", ...) above.
One more thing: idnum is an int, but don't ever concatenate a string value into a SQL string. That's a massive security vulnerability and people here will (rightly, I'm afraid) give you a very hard time for even contemplating it.
Use OracleCommand.Parameters instead, as shown in this answer. I would have used that even in this case, personally, just as a conditioned reflex.
