sql query trouble - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql query trouble

Author

Topic

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 13:54:14

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

22859 Posts

Posted - 2007-10-10 : 13:57:06

You have several separate SELECT statements in there, what are you expecting them to do?

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 14:02:09

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.

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 14:02:26

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

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 14:04:14

"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

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 14:05:18

After every select statement i have put a commae so that it would correspond to the insert fields specified in the bracket.

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 14:06:38

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

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 14:08:39

[code]
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
[/code]
still appears to have an extra parameter

Kristen

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 14:10:49

can you please tell me how to write this query.

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 14:21:06

Thanks a lot kristen, it works. This means that first i have to put all selects within SELECT(...)

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 14:24:50

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

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 14:42:33

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 ?

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 15:19:18

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

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 15:26:26

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.

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 15:27:52

Anand_1203
Starting Member

9 Posts

Posted - 2007-10-10 : 15:28:56

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 02:57:05

[code]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[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 03:56:09

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources