SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sql query trouble
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  13:54:14  Show Profile  Reply with Quote
Dear friends,

I have written the following sql server query :
insert dmtrans (dmid,itemcode,batchcode,qty,freeqty,pcode,amount)
select dmmaster.dmid from dmmaster where dmmaster.dmno = 'latest3',
itemmaster.itemcode from itemmaster where itemmaster.itemname='Pacimol',
batchmaster.batchcode from batchmaster where batchmaster.itemcode in
(select itemmaster.itemcode from itemmaster where itemmaster.itemname='Pacimol'),100,
500,1000,supplier.pcode from supplier where supplier.name='Subhash Medicals',2000

but it returns the following error :

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.

Kindly help.

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/10/2007 :  13:57:06  Show Profile  Reply with Quote
You have several separate SELECT statements in there, what are you expecting them to do?
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  14:02:09  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/10/2007 :  14:02:26  Show Profile  Reply with Quote
I suppose it would be quite cool if it worked, but I have no idea how it would know what to associate with what ...

insert dmtrans
(
	dmid,itemcode,batchcode,qty,freeqty,pcode,amount
) 
select	dmmaster.dmid AS dmid 
from	dmmaster 
where	dmmaster.dmno = 'latest3'
	,	-- <<<
	itemmaster.itemcode AS itemcode
from	itemmaster 
where	itemmaster.itemname='Pacimol'
	,	-- <<<
	batchmaster.batchcode AS batchcode
from	batchmaster 
where	batchmaster.itemcode in 
	(
		select	itemmaster.itemcode 
		from	itemmaster 
		where	itemmaster.itemname='Pacimol'
	)
	,100 AS qty,
	500 AS freeqty,
	1000 AS SQL_WillKnowThisOneIsRedundant
	,	-- <<<
	supplier.pcode AS pcode
from	supplier 
where	supplier.name='Subhash Medicals'
	,	-- <<<
	2000 AS amount

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/10/2007 :  14:04:14  Show Profile  Reply with Quote
"I hope you follow."

Well I get a rough idea what you are after, but this is so far removed from anything that SQL Server can execute its hard to know where to begin ....

My recommendation would be that you learn some basic SQL Skills first.

Kristen
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  14:05:18  Show Profile  Reply with Quote
After every select statement i have put a commae so that it would correspond to the insert fields specified in the bracket.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/10/2007 :  14:06:38  Show Profile  Reply with Quote
Yes, but you have to put all the tables together in a single FROM statement, or you need to use Nested Sub-Selects. And if you use SubSelects then they MUST only return ONE result ... (maybe that is a given in your data, hard to tell from here)

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/10/2007 :  14:08:39  Show Profile  Reply with Quote

insert dmtrans
(
	dmid,itemcode,batchcode,qty,freeqty,pcode,amount
) 
SELECT
(select	dmmaster.dmid AS dmid 
from	dmmaster 
where	dmmaster.dmno = 'latest3')
	,	-- <<<
(SELECT	itemmaster.itemcode AS itemcode
from	itemmaster 
where	itemmaster.itemname='Pacimol')
	,	-- <<<
(SELECT	batchmaster.batchcode AS batchcode
from	batchmaster 
where	batchmaster.itemcode in 
	(
		select	itemmaster.itemcode 
		from	itemmaster 
		where	itemmaster.itemname='Pacimol'
	))
	,100 AS qty,
	500 AS freeqty,
	1000 AS SQL_WillNotKnowThisOneIsRedundant
	,	-- <<<
(SELECT	supplier.pcode AS pcode
from	supplier 
where	supplier.name='Subhash Medicals')
	,	-- <<<
	2000 AS amount

still appears to have an extra parameter

Kristen
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  14:10:49  Show Profile  Reply with Quote
can you please tell me how to write this query.
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  14:21:06  Show Profile  Reply with Quote
Thanks a lot kristen, it works. This means that first i have to put all selects within SELECT(...)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/10/2007 :  14:24:50  Show Profile  Reply with Quote
Well I can;t see how it works, because there are too many parameters.

And its a terrible way to write such a query.

But there we go ...

Kristen
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  14:42:33  Show Profile  Reply with Quote
Kirsten i removed that extra parameter.

Moreover, one thing i could not understand,
I have a similar query like this : insert dmmaster (dmno,dmdate,pcode) select 12,'10/5/2007',supplier.pcode from supplier where name='Subhash Medicals';
and this query works successfully. I wrote the query in question in this way only and it did not work. Can you tell me the difference between the two.

and why it is a terrible way to write such a query ?
Is there some better way too ?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/10/2007 :  15:19:18  Show Profile  Reply with Quote
You have to select a set of data, and then that data can be retrieved, or inserted into another table (as you are doing).

insert dmmaster
(
	dmno,dmdate,pcode
)
select	12,'10/5/2007',supplier.pcode
from	supplier
where	name='Subhash Medicals'

is getting a Set of data from the [supplier] table, and inserting it into the [dmmaster] table.

What you are doing is trying to write separate queries column-based, rather row-based.

Kristen
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  15:26:26  Show Profile  Reply with Quote
you mean to say that sql server thinks that 12 and '10/5/2007' is coming from the supplier table when in fact they are just
constants and have no relation with the supplier table and only pcode is coming from the supplier table. you mean to say
that the sqlserver thinks that the entire set of data (12,'10/5/07' and pcode) is coming from the supplier table.
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  15:27:52  Show Profile  Reply with Quote
you mean to say that sql server thinks that 12 and '10/5/2007' is coming from the supplier table when in fact they are just
constants and have no relation with the supplier table and only pcode is coming from the supplier table. you mean to say
that the sqlserver thinks that the entire set of data (12,'10/5/07' and pcode) is coming from the supplier table.
Go to Top of Page

Anand_1203
Starting Member

9 Posts

Posted - 10/10/2007 :  15:28:56  Show Profile  Reply with Quote
you mean to say that sql server thinks that 12 and '10/5/2007' is coming from the supplier table when in fact they are just
constants and have no relation with the supplier table and only pcode is coming from the supplier table. you mean to say
that the sqlserver thinks that the entire set of data (12,'10/5/07' and pcode) is coming from the supplier table.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 10/11/2007 :  02:57:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
insert		dmtrans
		(
			dmid,
			itemcode,
			batchcode,
			qty,
			freeqty,
			pcode,
			amount
		) 
select		(select dmmaster.dmid from dmmaster where dmmaster.dmno = 'latest3'), 
		(select itemmaster.itemcode from itemmaster where itemmaster.itemname = 'Pacimol'),
		(select batchmaster.batchcode from batchmaster where batchmaster.itemcode in (select itemmaster.itemcode from itemmaster where itemmaster.itemname = 'Pacimol')),
		100,	-- one of these three has to go, or add a new column to the insert list
		500,	-- one of these three has to go, or add a new column to the insert list
		1000,	-- one of these three has to go, or add a new column to the insert list
		(select supplier.pcode from supplier where supplier.name = 'Subhash Medicals'),
		2000



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/11/2007 :  03:56:09  Show Profile  Reply with Quote
Sub-select's 1 & 2 are similar, and should be in a FROM.

But I just don;t get how this approach is useful int eh real world.

Actually, maybe I do at long last:

Data entry form has SELECT lists, the values from those need processing from the Lookup Tables
to get the ID values.

But why wouldn't the SELECT lists return the ID rather than the Value?

And what if any of the Values are missing, its just going to insert NULL in that column,
which obviously is NOT what the Data Entry person excepts since they provided a value!

But that's OK if the columns are defined as NOT NULL. But that's probably a big "But" ...

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000