我在Oracle中有两个表,我试图找到现有的记录,这样我就可以添加数据。一个数据集工作,另一个不工作。
这是表格(剪掉了,只有适用的字段)
CREATE TABLE STAGING.WEATHER_API_ACTUAL
(
STATIONID VARCHAR2(4 BYTE) NOT NULL,
WEATHER_DATETIME DATE NOT NULL,
TEMPERATURE NUMBER(8,2),
(other fields, also null)
RETRIEVAL_DATETIME DATE)
和
CREATE TABLE STAGING.WEATHER_API_PREDICTED
(
STATIONID VARCHAR2(4 BYTE) NOT NULL,
WEATHER_DATETIME DATE NOT NULL,
PREDICTION_DATETIME DATE NOT NULL,
HIGH_TEMP NUMBER(8,2),
(other fields, also null)
RETRIEVAL_DATETIME DATE)
这两张表的数据很相似,但其中一张每小时都有一个日期。
WEATHER_API_ACTUAL
STATIONID WEATHER_DATETIME TEMPERATURE RETRIEVAL_DATETIME
KNYC 10/12/2019 8:00:00 AM {null} {null}
KNYC 10/12/2019 9:00:00 AM {null} {null}
和
WEATHER_API_PREDICTED
STATIONID WEATHER_DATETIME PREDICTION_DATETIME TEMPERATURE RETRIEVAL_DATETIME
KNYC 10/12/2019 10/13/2019 {null} {null}
KNYC 10/12/2019 10/13/2019 {null} {null}
我有一些查询使用stationID和weather_Datetime查找记录,并获取空值,我想在数据库中更新它们。其中一个查询可以工作并更新数据。另一个没有找到匹配的。据我所知,唯一的区别是一个人没有时间部分。
第一个查询有效:
static bool insertForecastWeather(string stationID, string weatherDateTime, string predictionDateTime, WeatherData weatherData, string snow)
{
using (OracleConnection connection = new OracleConnection(Strings.Staging_ConnectionString))
{
//NOTE THE TIMEZONE IS CST HARDCODED at this time.
connection.Open();
OracleCommand command = new OracleCommand("UPDATE Weather_API_Predicted " +
"SET High_Temp = :High " +
",Low_Temp = :Low " +
",Average_Temp = :Average " +
",Wind_Speed = :WindSpeed " +
",Wind_Direction = :WindDirection " +
",Wind_Chill = :WindChill " +
",Cloud_Cover = :CloudCover " +
",Snow = :Snow " +
",Retrieval_DateTime = :RetrievalDateTime " +
",Retrieval_DateTimeTZ = 'US/Central' " +
"WHERE StationID = :StationID " +
"AND Weather_DateTime = to_date(:WeatherDateTime,'MM/DD/YYYY hh:mi:ss am') " +
"AND Prediction_DateTime = to_date(:PredictionDateTime,'MM/DD/YYYY hh:mi:ss am')", connection);
OracleTransaction trans = connection.BeginTransaction();
command.Transaction = trans;
//Values come through from the API as text values. We need to convert them to decimals first and then convert to ints to load into SQL.
int parameterSnow = Decimal.ToInt32(Convert.ToDecimal(snow));
string parameterWinddirection = getWindDirection(weatherData.WindDirection);
command.Parameters.Add(new OracleParameter(":Average", weatherData.AverageTemperature));
command.Parameters.Add(new OracleParameter(":High", weatherData.MaxTemperature));
command.Parameters.Add(new OracleParameter(":Low", weatherData.MinTemperature));
command.Parameters.Add(new OracleParameter(":WindSpeed", weatherData.WindSpeed));
command.Parameters.Add(new OracleParameter(":WindDirection", parameterWinddirection));
command.Parameters.Add(new OracleParameter(":WindChill", weatherData.WindChill));
command.Parameters.Add(new OracleParameter(":CloudCover", weatherData.CloudCoverPercentage));
command.Parameters.Add(new OracleParameter(":Snow", parameterSnow));
command.Parameters.Add(new OracleParameter(":RetrievalDateTime", DateTime.Now));
command.Parameters.Add(new OracleParameter(":StationID", stationID));
command.Parameters.Add(new OracleParameter(":WeatherDateTime", weatherDateTime));
command.Parameters.Add(new OracleParameter(":PredictionDateTime", predictionDateTime));
command.ExecuteNonQuery();
trans.Commit();
connection.Close();
}
return true;
}
实际上,还有另一个区别——第一个查询使用的是内部表weatherData。但不包括日期和地点。这是找不到匹配的代码。
static bool insertActualWeather(string stationID, string weatherDateTime, string temperature, string windspeed, string winddirection, string windchill, string cloudcover, string snow)
{
using (OracleConnection connection = new OracleConnection(Strings.Staging_ConnectionString))
{
//NOTE THE TIMEZONE IS CST HARDCODED at this time.
connection.Open();
OracleCommand command = new OracleCommand("UPDATE Weather_API_Actual " +
"SET Temperature = :Temperature " +
",Wind_Speed = :WindSpeed " +
",Wind_Direction = :WindDirection " +
",Wind_Chill = :WindChill " +
",Cloud_Cover = :CloudCover " +
",Snow = :Snow " +
",Retrieval_DateTime = :RetrievalDateTime " +
",Retrieval_DateTimeTZ = 'US/Central' " +
"WHERE trim(StationID) = trim(:StationID) " +
"AND trunc(Weather_DateTime,'HH') = trunc(to_date(:WeatherDateTime,'MM/DD/YYYY hh:mi:ss am'),'HH') ", connection);
OracleTransaction trans = connection.BeginTransaction();
command.Transaction = trans;
//Values come through from the API as text values. We need to convert them to decimals first and then convert to ints to load into SQL.
int parameterTemperature = Decimal.ToInt32(Convert.ToDecimal(temperature));
int parameterWindSpeed = Decimal.ToInt32(Convert.ToDecimal(windspeed));
int parameterWindDirection = Decimal.ToInt32(Convert.ToDecimal(winddirection));
int parameterWindChill = Decimal.ToInt32(Convert.ToDecimal(windchill));
int parameterCloudCover = Decimal.ToInt32(Convert.ToDecimal(cloudcover));
int parameterSnow = Decimal.ToInt32(Convert.ToDecimal(snow));
command.Parameters.Add(new OracleParameter(":Temperature", parameterTemperature));
command.Parameters.Add(new OracleParameter(":WindSpeed", parameterWindSpeed));
command.Parameters.Add(new OracleParameter(":WindDirection", parameterWindDirection));
command.Parameters.Add(new OracleParameter(":WindChill", parameterWindChill));
command.Parameters.Add(new OracleParameter(":CloudCover", parameterCloudCover));
command.Parameters.Add(new OracleParameter(":Snow", parameterSnow));
command.Parameters.Add(new OracleParameter(":RetrievalDateTime", DateTime.Now));
command.Parameters.Add(new OracleParameter(":WeatherDateTime", weatherDateTime));
command.Parameters.Add(new OracleParameter(":StationID", stationID));
command.ExecuteNonQuery();
trans.Commit();
connection.Close();
}
return true;
}
如您所见,我在StationID上添加了一个Trim以尝试匹配,但问题似乎出在日期上。
当我运行它时,我使用表中的字段从一个API获取数据,然后想写入找到的结果。我的weatherDateTime是一个字符串“10/12/2019 8:00:00 a m”我正在查看返回的数据,它是匹配的。但没有记录。
如果我更改这两段代码,使AND WeatherData行与工作的行完全相同,那么当我转到ExecuteNonQuery时,会得到错误:ORA-01858:在需要数字的地方找到了一个非数字字符。
当我观察stationID和weatherDateTime这两个字段时,它们都是字符串,看起来很好。那里没有坏数据。
我错过了什么?为什么这对一个有用而不是另一个?
