| Author |
 Topic  |
|
|
LexW
Starting Member
3 Posts |
 Posted - 08/28/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/28/2012 : 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 - 08/28/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/28/2012 : 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 - 08/28/2012 : 07:08:45
|
Thanks it works!
(can I close this topic somewhere?) |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/28/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 08/28/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/28/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/28/2012 : 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
USA
1 Posts |
Posted - 10/16/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 10/19/2012 : 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/ |
|
|
| |
Topic |
|
The column was specified multiple times
