The column was specified multiple times

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

General SQL Server Forums

New to SQL Server Programming

The column was specified multiple times

Author

Topic

LexW
Starting Member

3 Posts

Posted - 2012-08-28 : 03:05:54

Hello,

With the following statement I get this error:

"The column 'ID' was specified multiple times for 'gbkmut'"

Can anyone explain why this error is raised? In my understanding the column ID is specified only one time in the FROM area.

Here is the code:

SELECT
gbkmut.dbk_verwnr AS JournalNumber,
gbkmut.bkstnr AS GLEntryNumber,
gbkmut.regel AS GLRowNumber,
convert(varchar, gbkmut.docdate, 120) AS GLBookDate,
convert(varchar, perdat.bkjrcode) + '/' + convert(varchar, perdat.per_fin) AS GLBookPeriod,
gbkmut.bkjrcode AS GLBookYear,
convert(varchar, gbkmut.syscreated, 120) AS GLEntryDate,
gbkmut.syscreator AS UserName,
gbkmut.reknr AS GLCode,
gbkmut.dagbknr AS JournalCode,
gbkmut.debnr AS CustomerCode,
gbkmut.crdnr AS VendorCode,
gbkmut.oms25 AS GLDescription,
gbkmut.bdr_hfl AS GLAmount

FROM

(SELECT *, ROW_NUMBER() OVER (ORDER BY gbkmut.ID) as row FROM gbkmut, perdat WHERE (gbkmut.bkjrcode = perdat.bkjrcode AND gbkmut.docdate >= perdat.bgdatum AND gbkmut.docdate <= perdat. eddatum) AND (gbkmut.transtype = 'N' OR gbkmut.transtype = 'C' OR gbkmut.transtype = 'P'))

gbkmut, perdat

WHERE (gbkmut.bkjrcode = perdat.bkjrcode AND gbkmut.docdate >= perdat.bgdatum AND gbkmut.docdate <= perdat. eddatum) AND (gbkmut.transtype = 'N' OR gbkmut.transtype = 'C' OR gbkmut.transtype = 'P')

AND row >= 2001 And row <= 4000

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-28 : 04:24:51

It's because gbkmut and perdat both have an id column. You need to specify the column list for the derived table.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LexW
Starting Member

3 Posts

Posted - 2012-08-28 : 04:29:26

That is true, but I think I did this by adding gbkmut.ID, that's the only time I use the ID column.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-28 : 06:55:18

The way you are using that subquery confuses SQL Server. What you need to do is probably something like the following, where I am selecting all the columns you want including the row number in a subquery and then selecting only the rows you want in the outer query.

SELECT * FROM
(
SELECT
	gbkmut.dbk_verwnr AS JournalNumber,
	gbkmut.bkstnr AS GLEntryNumber,
	gbkmut.regel AS GLRowNumber,
	CONVERT(VARCHAR, gbkmut.docdate, 120) AS GLBookDate,
	CONVERT(VARCHAR, perdat.bkjrcode) + '/' + CONVERT(VARCHAR, perdat.per_fin) AS 
	GLBookPeriod,
	gbkmut.bkjrcode AS GLBookYear,
	CONVERT(VARCHAR, gbkmut.syscreated, 120) AS GLEntryDate,
	gbkmut.syscreator AS UserName,
	gbkmut.reknr AS GLCode,
	gbkmut.dagbknr AS JournalCode,
	gbkmut.debnr AS CustomerCode,
	gbkmut.crdnr AS VendorCode,
	gbkmut.oms25 AS GLDescription,
	gbkmut.bdr_hfl AS GLAmount
	ROW_NUMBER() OVER(ORDER BY gbkmut.ID) AS row
FROM
	gbkmut,
	perdat
WHERE
	(
	    gbkmut.bkjrcode = perdat.bkjrcode
	    AND gbkmut.docdate >= perdat.bgdatum
	    AND gbkmut.docdate <= perdat. eddatum
	)
	AND (
	        gbkmut.transtype = 'N'
	        OR gbkmut.transtype = 'C'
	        OR gbkmut.transtype = 'P'
	)
) s WHERE 
	row >= 2001
	AND row <= 4000

LexW
Starting Member

3 Posts

Posted - 2012-08-28 : 07:08:45

Thanks it works!

(can I close this topic somewhere?)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-28 : 07:30:37

You are welcome.

And, let it be - no need to close out. Some people edit the subject line to include the word closed, but most don't.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-28 : 09:02:45

quote:

And, let it be - no need to close out. Some people edit the subject line to include the word closed, but most don't.

cast stone in the pond
Help! Help! multiple results
ripples fade in time

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-28 : 10:35:49

quote:
cast stone in the pond
Help! Help! multiple results
ripples fade in time

I must be wearing the wrong hair color today - I read that 3 times, and couldn't figure out the meaning!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-28 : 12:20:40

Look up Haiku.
It's meant to produce an emotional response rather than make sense.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

plmrr27
Starting Member

1 Post

Posted - 2012-10-16 : 13:26:51

Hello,

Getting the same error message here. Can someone help? Here is the script.

SELECT sum (Result) FROM
(SELECT CASE
WHEN WO010032.MANUFACTUREORDERST_I=7
THEN (ENDQTY_I - total)
ELSE ENDQTY_I
END as Result, *
FROM (MHG.dbo.WO010032 WO010032 INNER JOIN MHG.dbo.IV00101 IV00101
ON WO010032.ITEMNMBR=IV00101.ITEMNMBR) LEFT OUTER JOIN MHG.dbo.PartialMO PartialMO
ON WO010032.MANUFACTUREORDER_I=PartialMO.MANUFACTUREORDER_I
WHERE (WO010032.MANUFACTUREORDERST_I=2 OR WO010032.MANUFACTUREORDERST_I=3 OR
WO010032.MANUFACTUREORDERST_I=7) AND WO010032.ENDDATE<{ts '2012-10-20 00:00:00'}
and ITMCLSCD = 'x09-FG')a

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-19 : 07:59:11

the inner query does


SELECT ..., * FROM ....

Don't do that. Always specify the columns you want and give them unique aliases

See above for details:


SELECT [col1] AS [Col1_name] FROM ....

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources