| Author |
 Topic  |
|
|
regata
Starting Member
Kyrgyzstan
4 Posts |
 Posted - 04/26/2012 : 07:36:51
|
Hi everyone
this code is perfectly working on sql express 2012
but in sql server 2008 r2 it throws an error
actually it does for all my queries when i ported them to sql 2008
Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.
Incorrect syntax near '='.
can anybody help me out in here. is there a difference btwn tsqls in this versions
thnx
declare @cuser INT
declare @ukind int
set @cuser = :cuser
set @ukind = :ukind
SELECT
SS.DATE AS [date],
SUM(CASE WHEN (SS.CUSTOMER > 0) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C1,
SUM(CASE WHEN (SS.CUSTOMER = 1) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C2,
SUM(CASE WHEN (SS.CUSTOMER > 1) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C3,
SUM(CASE WHEN (SS.CUSTOMER > 1) AND (SS.ITEM = 0) THEN SS.TOTAL ELSE 0 END) AS C4,
SUM(CASE WHEN (SS.CUSTOMER > 1) AND (SS.ITEM > 0) THEN SS.DISCOUNT ELSE 0 END) AS C5,
SUM(CASE WHEN SS.CUSTOMER = 1 THEN SS.DISCOUNT ELSE 0 END) AS C6,
SUM(CASE WHEN (SS.CUSTOMER = 0) AND (SS.ITEM = 0) THEN SS.TOTAL ELSE 0 END) AS C7,
SUM(CASE WHEN SS.CUSTOMER = -1 THEN SS.TOTAL ELSE 0 END) AS C8,
SUM(CASE WHEN (SS.CUSTOMER = 0) AND (SS.ITEM > 0) THEN SS.TOTAL ELSE 0 END) AS C9,
SUM(CASE WHEN SS.ITEM=-1 THEN SS.TOTAL ELSE 0 END) AS C10
FROM
(
SELECT
SD.DATE AS [DATE],
SD.CUSTOMER,
S.ITEM,
S.DISCOUNT,
[TOTAL] =
CASE WHEN S.EK = 1 THEN
(
round((S.QUANTITY * I.WEIGHT) * (((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (S.ITEM = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) / I.WEIGHT) + 5),0)
)
WHEN S.EK = 2 THEN
(
CASE
WHEN (S.ITEM = 7) THEN ROUND((S.QUANTITY * I.WEIGHT) * ((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE ( 2 = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) - 1),0)
WHEN (S.ITEM = 11) THEN ROUND((S.QUANTITY * I.WEIGHT) * ((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE ( 8 = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) - 1),0)
WHEN (S.ITEM = 15) THEN ROUND((S.QUANTITY * I.WEIGHT) * ((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (12 = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) - 1),0)
ELSE
round((S.QUANTITY * I.WEIGHT) * (((SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (S.ITEM = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC) / I.WEIGHT) + 4),0)
END
)
ELSE
(S.QUANTITY * (SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (S.ITEM = P.ITEM) AND (P.DATE <=SD.DATE) ORDER BY P.DATE DESC))
END
FROM
DOCS AS SD
JOIN STOCK AS S ON SD.ID = S.DOC
JOIN ITEMS AS I ON I.ID = S.ITEM
WHERE SD.USR = @cuser
UNION ALL
SELECT
SI.DATE AS [DATE],
-1 AS CUTOMER,
SI.ITEM,
0 AS DISCOUNT,
(SI.QUANTITY * (SELECT TOP 1 IIF(@ukind = 1,P.PRICE,P.PRICE1) FROM PRICES P WHERE (SI.ITEM = P.ITEM) AND (P.DATE <=SI.DATE) ORDER BY P.DATE DESC )) AS [TOTAL]
FROM
STOCKIN SI
WHERE SI.KIND <> 2 AND SI.USR = @cuser
UNION ALL
SELECT
K.DATE AS [DATE],
K.CUSTOMER,
K.REASON AS ITEM,
0 AS DISCOUNT,
K.AMOUNT AS [TOTAL]
FROM
KASA K
WHERE K.USR = @cuser
UNION ALL
SELECT
GK.DATE AS [DATE],
0 AS CUSTOMER,
-1 AS ITEM,
0 AS DISCOUNT,
GK.AMOUNT AS [TOTAL]
FROM
GKASA GK
WHERE GK.USR = @cuser
) AS SS
WHERE month(getdate()) = month(SS.DATE) and year(getdate()) = year(SS.DATE)
GROUP BY SS.DATE;
Nurlan |
Edited by - regata on 04/26/2012 07:38:17
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/26/2012 : 07:43:29
|
I think a bunch of colons (:) have gotten into your code during porting. If you double-click on the error message in SSMS, it will take you to the line where you have the error. For example, the first error is on line 4: set @cuser = :cuser Remove the colon and the error goes away.set @cuser = cuser |
 |
|
|
regata
Starting Member
Kyrgyzstan
4 Posts |
Posted - 04/26/2012 : 22:54:47
|
ok tnx sunitabeck i.v never used any sql manager, most advanced sql manager was notepad. so anyway i downloaded SSMS tnx for that.
but now another question came up. How do i use or pass data to parametrs because that (the semicln ':' ) was the only way i know.
by the way. i use (sql server+delphi)
Nurlan |
Edited by - regata on 04/26/2012 22:57:19 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/26/2012 : 23:06:02
|
remove the :
just simply
set @cuser = cuser
KH
Time is always against us
|
|
|
|
regata
Starting Member
Kyrgyzstan
4 Posts |
Posted - 04/26/2012 : 23:26:15
|
quote:
Originally posted by khtan
remove the :
just simply
set @cuser = cuser
KH
Time is always against us
ok now i'm realy confused
so i did remove the semis and this happen
<< MS Sql Server mgmt Studio >>
Msg 207, Level 16, State 1, Line 4
Invalid column name 'cuser'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ukind'.
ok let me clear the situation. i am trying to pas data from Delphi to query as parameter and parameter supposed to init variables in this case @cuser with param value. it was working on my pc
(Win 7, Delphi 2010, Sql server Express 2012) and when i ported to another pc
(Win 2008 Server, Sql Server 2008 r2) it starts to throw errors
Nurlan |
Edited by - regata on 04/26/2012 23:32:38 |
|
|
| |
Topic |
|
|
|
Incorrect syntax error. need some qlue
