• 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

SQL Syntax Error - Intersect

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Everyone,
This is my first post to the forum, thank you in advance for your time and help. I've got an exam scheduling application that's hooking into a SQL server database for a class. I'm trying to run the following command:

statement = connection.createStatement();
statement.executeQuery("select count(instructor_in_both_blocks.inst_id)"
+ "from(select distinct(bsOne.instructor_id) inst_id"
+ " FROM block_staging_one bsOne, block_staging_one bsOneTwo"
+ " where bsOne.schedule_group = " + blockUnderQuestion + "and bsOne.instructor_id = bsOneTwo.instructor_id"
+ "and bsOneTwo.schedule_group =" + comparisonBlock + ") instructor_in_both_blocks;");

Where blockUnderQuestion and comparisonBlock are both simple int's that I'm passing into the SQL Query. When I run this command, NetBeans is throwing the following error:

Apr 23, 2012 10:13:19 AM backend.ERSMain assignScheduleBlock
SEVERE: null
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'bsOneTwo'.

Is there something I'm missing about SQL syntax for java? I can run the exact same syntax in an SQL editor and it returns without issue.

Thanks for your time!

Jonathan
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jonathan, welcome to the Ranch!

I don't know what you mean when you say

I can run the exact same syntax in an SQL editor



because what you posted there is not valid SQL at all. It's Java code. Perhaps the variable it constructs contains valid SQL, but there's no evidence that you have tested that. In other words, look at the SQL and not at the Java code.
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
extract the sql statement out into a string.
Then you can debug it, by using system.out.println (or a debugger if you are using an IDE like eclipse or netbeans).
I expect you will find you have made a mistake in constructing your string which isn't easily visible in the code.

As the syntax for sql is exactly the same whether you run it from java or an sql window.
 
Jonathan Greene
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank you both for your replies. The syntax was concatenated incorrectly, and it was as simple as that. When I say I took the code and ran it in an SQL editor, I meant I took the SQL piece of the code, and ran it in an SQL editor. I might be a noob to this board, but I understand you can't run java code in SQL.

Thanks for your help.
 
Oh sure, it's a tiny ad, but under the right circumstances, it gets bigger.
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic