• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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".
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic