wood burning stoves 2.0*
The moose likes JDBC and the fly likes sql query trouble 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 "sql query trouble" Watch "sql query trouble" New topic
Author

sql query trouble

Anand Shrivastava
Ranch Hand

Joined: Jul 22, 2007
Posts: 125
Hello friends,

I am working on a project with sqlserver in the backend.

Recently i have made the following query :



but it gives the following error message
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.

Kindly help.


Anand Shrivastava
SCJA
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Anand,

There are several issues with you're query... so lets break it down to what it appears you are wanting to accomplish (so let me know where it's wrong... or right):

you want to return dmmaster.dmid, itemmaster.itemcode, and batchmaster.batchcode

from the dmmaster, itemmaster, and batchmaster tables. you will need to add the keys to that the tables join on or you will (when your query is cleaned up) have a cartesian join (returns all rows from all tables that meet your criteria for the tables).

You also seem to be wanting to do some type of correlated sub query including itemmaster that seems unnecessary since you specified it previously... but i'm not completely sure of what you are trying to do here.

you query should look something more like this... but I'm really just guessing now.

select dmmaster.dmid
, itemmaster.itemcode
, batchmaster.batchcod
, 100 -- I am just guessing with the literals... if you meant them
, 500 -- to be part of a where clause, you need to explain what
, 1000 -- they represent.
, supplier.pcode
, 2000
from dmmaster
Join itemmaster on (dmmaster.column_goes_here = itemmaster.column_goes_here) -- or vice versa
Join batchmaster using (itemcode) -- depending on db may have to use
-- on(batchmaster.itemcode =
-- itemmaster.itemcode)
Join supplier on (something that joins supplier to
either dmmaster, itemmaster, batchmaster)
where dmmaster.dmno = 'latest3'
and itemmaster.itemname='Pacimol'
and supplier.name='Subhash Medicals'

[ October 09, 2007: Message edited by: Paul Campbell ]
[ October 09, 2007: Message edited by: Paul Campbell ]
Anand Shrivastava
Ranch Hand

Joined: Jul 22, 2007
Posts: 125
I will try to explain you :

dmtrans is a transaction table where I am inserting primary keys of other
master tables as foreign keys. Joins are not required as the select queries
will return a single row only because what we are selecting is a primary key.

I will give you the structure of the tables here :

dmmaster table -
dmid = primary key
dmno = text field fed by the user
dmdate= date field fed by the user
pcode = primary key of the supplier table, here placed as foreign key.

batchmaster table
batchcode = primary key
batchno = text field fed by the user
itemcode = primary key of the itemmaster table, here placed as foreign key

itemmaster table
itemcode = primary key
itemname = text field fed by the user

dmtras table (This is a transaction table where we are inserting data based on the selections made by the user on the screen)
dmid = primary key of the dmmaster table, here a foreign key
itemcode = primaty key of the itemmaster table, here a foreign key
batchcode = primary key of the batchmaster table, here a foreign key
pcode = primay key of the supplier table, here a foreign key
qty = text field fed by the user
freeqty = text field fed by the user
amount = text field fed by the user

Now on the screen user from combobox selects the following
party name, item name and batchno
To fill in the dmtrans table we need
pcode, itemcode and batchcode
He also feeds dmno which goes into dmmaster table and from there
we require corresponding dmcode to place in dmtrans table.

One method would have been to make separate queries that is to say
first execute the select queries, read the fields and then execute
the insert query in the dmtrans table. This method works fine.
But to cutshort the code, I am trying to run all the select queries
as subqueries of a single insert statement so that the entire
exercise is completed in a single query.

Therefore I have written the query like this

insert dmtrans(dmid,itemcode,batchcode,qty,freeqty,pcode)//(insert fields)
select dmmaster.dmid from dmmaster where dmmaster.dmno="latest3" -
Here latest3 is just for simplicity sake i have written. In the original query it is dmno which the user feeds on the screen.
Then i placed a commae after this and wrote
itemmaster.itemcode from itemmaster where itemmaster.itemname="Pacimol"-
Here pacimol is just for simplicity sake i have written. In the original
query it is itemname which the user selects from the combobox.
Then i placed a commae after thi and wrote
batchmaster.batchcode from batchmaster where batchmaster.itemcode in (select itemmaster.itemcode from itemmaster where itemmaster.itemname="Pacimol") -
you see in the batchmaster file for each itemcode there is a batchcode associated with it. I dont have the itemcode here as the user selects itemname from the combobox, therefore as a subquery i select itemcode from itemmaster based on the itemname fed by the user. Pacimol is just written for simplicity sake. In original query it is the name selected by the user.

I hope you follow.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Anand,

I think I'm following what you want to do but it is really late for me and I'll have to follow up in the morning. But a couple of things do jump out at me, in SQL you can't insert data and return data at the same time... they are separate dml statements... a sub-query (uncorellated (in) or not... I prefer correllated (exists)) doesn't return data in your result set... it is there to identify the data to be returned... if you want a result set to include columns from multiple tables, you have to join those tables to each other in a manner that returns the desired set (i.e., uniquely identifies your rows and avoids to avoid a cartesian).

Does that make sense?

I'll read your post again in the morning and pay attention to the table structure you included (I really appreciate you including that... things make so much more sense when the data is all there).
Anand Shrivastava
Ranch Hand

Joined: Jul 22, 2007
Posts: 125
Paul answer found.

See here
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Annand, I see... I was completely misunderstanding you... you were wanting to populate the table columns dmid,itemcode,batchcode,qty,freeqty,pcode,amount with the information returned from a query.

Thanks for letting me know the outcome. I'm curious to hear how well it performs for you in its current format... what are you going to do with the extra columns that don't line up with the insert columns?
Anand Shrivastava
Ranch Hand

Joined: Jul 22, 2007
Posts: 125
The query is performing very well.
That extra parameter was put by mistake. I have removed it.
Now all the fields of the table are in the insert bracket and there is a corresponding select clause for all the insert fields.
I dont know why Kirsten says it is a terrible way to write such a query. I thought this is the best way as it accomplishes the task in a single query. I guess there is some better method too.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Anand Shrivastava:
The query is performing very well.
That extra parameter was put by mistake. I have removed it.
Now all the fields of the table are in the insert bracket and there is a corresponding select clause for all the insert fields.
I dont know why Kirsten says it is a terrible way to write such a query. I thought this is the best way as it accomplishes the task in a single query. I guess there is some better method too.


Anand,

I'm glad it is working for you.

The reason Kirsten editorialized about the query (I don't think I would use the term terrible) is that it is an inefficient query (column based results instead of row based). It may feel like a single query statement to you because you are looking at it in terms of the result set, but it is actually selects (every select is a separate query) being used to represent columns... in at least one instance part of your result set could be obtained via a natural join of batchmaster and itemmaster.

You shouldn't take any of it as personal... criticism is a valuable learning experience... if you allow yourself to commit to it. You should take it as a suggestion and opportunity to expand your SQL knowledge. A good start would be (when you have some time for it) to copy the select portion of your query (ignore the insert sql) and try to rewrite in a way that uses as few selects as possible.

I look at this experience and see that I should of worked harder at understanding your message instead of focusing purely on the syntax. I can also see that I need to take less of a data warehousing viewpoint and more of an application development mindset in how I approach this forum. I'll keep that in mind the next time I try to help.
[ October 10, 2007: Message edited by: Paul Campbell ]
Anand Shrivastava
Ranch Hand

Joined: Jul 22, 2007
Posts: 125
No Paul, I dont take anything as personal. I appreciate that people like you are always willing to help people like me. The problem is i dont have much knowledge of sql. This is my first project. I never had any formal training in sql. Can you suggest me some online good sql tutorial or book.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Anand Shrivastava:
No Paul, I dont take anything as personal. I appreciate that people like you are always willing to help people like me. The problem is i dont have much knowledge of sql. This is my first project. I never had any formal training in sql. Can you suggest me some online good sql tutorial or book.


Well by the same token, I find myself here because I'm trying to learn Java. ;)

I have found a lot of useful information at http://sqlzoo.net, http://www.techonthenet.com, and you can find an endless amount of information at IBMs DeveloperWorks site: http://http://www.ibm.com/developerworks/
Anand Shrivastava
Ranch Hand

Joined: Jul 22, 2007
Posts: 125
Thanks a lot for your worthy suggestions.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql query trouble