This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes JDBC template outer join and cast(@myValue AS date) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC template outer join and cast(@myValue AS date)" Watch "JDBC template outer join and cast(@myValue AS date)" New topic
Author

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

Angelika Angley
Ranch Hand

Joined: Feb 17, 2009
Posts: 40
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?


[Blog] [SCJP6 75%] [SCBCD]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Angelika Angley
Ranch Hand

Joined: Feb 17, 2009
Posts: 40
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

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

Joined: Feb 17, 2009
Posts: 40
Substring doesn't work either. I tried cast, convert and substring. All of them give me an error message: "not allowed in OUTER JOIN".
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC template outer join and cast(@myValue AS date)