英文:
Teradata Database and Eclipse/Java error: UPPERCASE or CASESPECIFIC specified for non-CHAR data
问题
我正在运行Eclipse和Java，使用“？”符号在运行时传递日期参数到下面的SQL语句中。然而，当执行下面的代码时，它会出现以下错误：
[Teradata数据库] [TeraJDBC 15.00.00.35] [错误 3536] [SQLState HY000] 针对非CHAR数据指定了UPPERCASE或CASESPECIFIC。
只有在我尝试使用“？”符号在运行时传递日期时才会出现此错误。如果我在我的SQL中硬编码日期并通过Eclipse运行它，它会正常运行。有人能否提供修复建议？
SELECTTO_DATE(TRIM(EXTRACT(YEAR FROM CAST(? AS VARCHAR(100)))) || '-0' ||TRIM(EXTRACT(MONTH FROM S1.birthday)) || '-' ||TRIM(EXTRACT(DAY FROM S1.birthday)), 'YYYY-MM-DD') AS start_DateFROM STAFF S1WHERE S1.TEAMID IN (4)AND (start_date BETWEEN date - 10 AND date)AND birthday IS NOT NULL
英文:
I am passing a date parameter using the "?" notation at run time using Eclipse and Java to the following SQL below. However, when the below code is executed it breaks with the following error:
> [Teradata Database] [TeraJDBC 15.00.00.35] [Error 3536] [SQLState
> HY000] UPPERCASE or CASESPECIFIC specified for non-CHAR data.
This error only appears when I try to pass the date on rumtime using the ? notation. If I hard code the date in my SQL below and run it through eclipse it runs fine. Can anyone please suggest a fix?
SELECTTO_DATE (TRIM(EXTRACT (YEAR FROM cast(? as varchar(100)) )) || '-0' ||TRIM(EXTRACT (MONTH FROM S1.birthday)) ||'-'||TRIM(EXTRACT (DAY FROM S1.birthday)) ,'YYYY-MM-DD') AS start_DateFROM STAFF S1WHERE S1.TEAMID IN (4)AND (start_date between date-10 and date)AND birthday IS NOT NULL
答案1
得分: 0
仅提取年份尝试此SQL：
SELECT (S1.birthday MOD 10000 + ((? (INT))-1900)*10000) (DATE) as start_date FROM STAFF S1WHERE S1.TEAMID IN (4)AND (start_date between date-10 and date)AND birthday IS NOT NULL;```缺少 Eclipse/Java 封装，我不确定是否完全正确。但 SQL 本身是没问题的。<details><summary>英文:</summary>Just passing the Year try this SQL:`SELECT (S1.birthday MOD 10000 + ((? (INT))-1900)*10000) (DATE) as start_date FROM STAFF S1WHERE S1.TEAMID IN (4)AND (start_date between date-10 and date)AND birthday IS NOT NULL;`Missing the Eclipse/Java Wrapper, I'm not 100% sure. But the SQL itself is fine.</details># 答案2**得分**: 0你的逻辑有误，你试图从一个VarChar字段中提取年份，而不是日期。但即使修正了，每当有一个2月29日的出生日期时，它也会失败。看起来你想在当前年份内计算生日，然后基于此进行过滤，就像*谁在过去一周内过生日*。最简单的方法是计算年龄，如下所示：```sqlcast(months_between(current_date, birthday)/12 as int)
然后将这个年龄数加到出生日期上，如下所示：
add_months(birthday, cast(months_between(current_date, birthday)/12 as int)*12) as birthday_this_year
然后你可以进行过滤：
where birthday_this_year between current_date - 10 and current_date
英文:
Your logic is wrong, you try to extract a year from a VarChar instead of a date. But even if fixed it will fail whenever there's a birthdate of Feb 29.
Seems you want to calculate the birthday within the current year and then filter based on this like who had birthday within the the last week. The easiest way to do this is to calculate the age like
cast(months_between (current_date, birthday)/12 as int)
and add that number of years to the birthdate
add_months(birthday, cast(months_between(current_date, birthday)/12 as int)*12) as birthday_this_year
Then you can filter
where birthday_this_year between current_date -10 and current_date
专注分享java语言的经验与见解，让所有开发者获益！
评论