• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC template outer join and cast(@myValue AS date)

 
Angelika Angley
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Trying to run the following sql:



It runs fine in a database client SQuirrel, but gives a BadSqlGrammarException if I run it from code and read data from XML.
The error is: "not allowed in OUTER JOIN [select * ...]".

I guess it's because I cast a birthday to date within left join on. I need to do a cast. And I need to join these two tables.

Does anyone know how I can cast to date within a join with JDBCTemplate?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
378
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Angelika,
What type is p.birthday? A Birthday object? If so, you could use a field on it. Or a function to convert (not cast) it into a String.
 
Angelika Angley
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
p.Birtday is a String of type "2009-01-01 00:00:00.000"

I also use HSQLDB 1.8 as a db connection.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
378
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Angelika,
For a string, I would use substring to get the date part as a string and check equality on that.
 
Angelika Angley
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Substring doesn't work either. I tried cast, convert and substring. All of them give me an error message: "not allowed in OUTER JOIN".
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic