| Author |
 Topic  |
|
|
rjhe22
Posting Yak Master
151 Posts |
 Posted - 10/09/2012 : 10:14:54
|
anyone who whats wrong with is sql getting this error
Msg 1087, Level 15, State 2, Line 12
Must declare the table variable "@IRBT2".
DECLARe @IRBT2 int
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(@IRBT2.IRBRATING)
FROM @IRBT
WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 10/09/2012 : 10:20:13
|
I think you have to declare @TRDT2 as follows
DECLARE @@TRDT2 TABLE(IRBRATING int)
And also that red marked part is typo......
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(@IRBT2.IRBRATING)
FROM @IRBT2
WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x
--
Chandu |
 |
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 10/09/2012 : 10:24:01
|
hi
thanks for replying
still getting error |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/09/2012 : 10:26:38
|
show us the FULL query that you just tried and the error messages
KH
Time is always against us
|
Edited by - khtan on 10/09/2012 10:26:54 |
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 10/09/2012 : 10:29:28
|
DECLARE @IRBT2 TABLE(IRBT int)
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(@IRBT2.IRBRATING)
FROM @IRBT2
WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x
error message
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@IRBT2".
tried it the same was as code from bandi same error message |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/09/2012 : 23:20:16
|
quote:
Originally posted by rjhe22
DECLARE @IRBT2 TABLE(IRBT int)
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(@IRBT2.IRBRATING)
FROM @IRBT2
WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x
error message
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@IRBT2".
tried it the same was as code from bandi same error message
this code doesnt make any sense to me
can i ask what you're trying to achieve?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 10/10/2012 : 00:24:52
|
DECLARE @IRBT2 TABLE(IRBT int)
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(T2.IRBT)
FROM @IRBT2 T2
WHERE T2.IRBT > IRBT.IRBRATING
) x
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/10/2012 : 01:57:16
|
quote:
Originally posted by bandi
DECLARE @IRBT2 TABLE(IRBT int)
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(T2.IRBT)
FROM @IRBT2 T2
WHERE T2.IRBT > IRBT.IRBRATING
) x
--
Chandu
Even then this code makes no sense as table is empty
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 10/10/2012 : 02:07:44
|
quote:
Originally posted by visakh16
quote:
Originally posted by bandi
DECLARE @IRBT2 TABLE(IRBT int)
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(T2.IRBT)
FROM @IRBT2 T2
WHERE T2.IRBT > IRBT.IRBRATING
) x
--
Chandu
Even then this code makes no sense as table is empty
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Hi vishak,
I provided solution for that error message only...
--
Chandu |
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 10/10/2012 : 05:12:53
|
o right thought i was on the right line but ovhbiously not.
so what im trying to do is
if in the acrt table NEGWATCH =1 then for MIDIRB in the acrt table i want to add the next value from irbt
so
say negwatch =1 and midird before this update is 16 then when update is done and from the irtb table or table 1 the midirb = 20 as the next number in the irbt table after 16 is 20 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/10/2012 : 23:49:05
|
sorry can you illustrate this with some sample data? i'm not quite getting this
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 10/11/2012 : 05:10:52
|
ya no problem iv actually changed they way im going to do it so will put up sample data and explain how im doing it now
table 1
id mo fi sp value y/n
1 a a a y
2 b b b n
3 c c c y
4 d d d n
table 2
mo fi sp value
a a a 1
b b b 2
c c c 3
d d d 4
so what i want to do is update the value in table1 with the value in table 2
but if any of the data in table 1 is y then i want it to update table 1 with the next value in table 2
so my table 1 after the update should look like this
id mo fi sp value y/n
1 a a a 2 y
2 b b b 2 n
3 c c c 4 y
4 d d d 4 n
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 10/11/2012 : 07:24:24
|
DECLARE @table1 TABLE (id int, mo varchar(10), fi varchar(10), sp varchar(10), value int, [y/n] char(1))
INSERT INTO @table1
SELECT 1, 'a', 'a', 'a', null, 'y' UNION ALL
SELECT 2, 'b', 'b', 'b', null ,'n' UNION ALL
SELECT 3, 'c', 'c', 'c', null, 'y' UNION ALL
SELECT 4, 'd', 'd', 'd', null ,'n'
DECLARE @table2 TABLE (mo varchar(10), fi varchar(10), sp varchar(10), value int)
INSERT INTO @table2
SELECT 'a', 'a', 'a', 1 UNION ALL
SELECT 'b', 'b', 'b', 2 UNION ALL
SELECT 'c', 'c', 'c', 3 UNION ALL
SELECT 'd', 'd', 'd', 4
;with cte
as
(select *, ROW_NUMBER() over(order by mo) AS rn FROM @table2),
cte1
as (SELECT mo,value, rn-1 as nextValue FROM cte)
update t1 SET t1.value = case when [y/n] = 'y' then c2.value else c1.value end
FROM @table1 t1
JOIN cte c1 ON t1.mo = c1.mo
join cte1 c2 on (c1.rn = c2.nextValue OR c1.rn = (SELECT MAX(rn) from cte))
SELECT * FROM @table1
--
Chandu |
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 10/11/2012 : 07:28:28
|
thanks for reply
just out of interest is there any other way of doing it.
like just a simple enough update without variable tables etc |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 10/11/2012 : 07:52:17
|
quote:
Originally posted by rjhe22
thanks for reply
just out of interest is there any other way of doing it.
like just a simple enough update without variable tables etc
LAG() and LEAD() functions are available in SQL Server2012 to perform this kind of operations, but not in MSSQL 2008
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/11/2012 : 15:24:21
|
if you dont want to use CTE you can use a logic like below
UPDATE t1
SET t1.value = CASE WHEN [y/n] = 'y' THEN NextVal ELSE t2.value END
FROM table1 t1
INNER JOIN table2 t2
ON t2.mo = t1.mo
and t2.fi = t1. fi
and t2.sp = t1.sp
OUTER APPLY (SELECT MIN(value) AS NextVal
FROM table2
WHERE value> t2.value
)t3
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Must declare the table variable
