SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Must declare the table variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Posting Yak Master

234 Posts

Posted - 10/09/2012 :  10:14:54  Show Profile  Reply with Quote
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
2202 Posts

Posted - 10/09/2012 :  10:20:13  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Posting Yak Master

234 Posts

Posted - 10/09/2012 :  10:24:01  Show Profile  Reply with Quote
hi
thanks for replying
still getting error
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 10/09/2012 :  10:26:38  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Posting Yak Master

234 Posts

Posted - 10/09/2012 :  10:29:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/09/2012 :  23:20:16  Show Profile  Reply with Quote
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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/10/2012 :  00:24:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/10/2012 :  01:57:16  Show Profile  Reply with Quote
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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/10/2012 :  02:07:44  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Posting Yak Master

234 Posts

Posted - 10/10/2012 :  05:12:53  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/10/2012 :  23:49:05  Show Profile  Reply with Quote
sorry can you illustrate this with some sample data? i'm not quite getting this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rjhe22
Posting Yak Master

234 Posts

Posted - 10/11/2012 :  05:10:52  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/11/2012 :  07:24:24  Show Profile  Reply with Quote

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
Go to Top of Page

rjhe22
Posting Yak Master

234 Posts

Posted - 10/11/2012 :  07:28:28  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/11/2012 :  07:52:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/11/2012 :  15:24:21  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000