SQL - variables - SQL Server Forums

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

SQL - variables

Author

Topic

OWSQL
Starting Member

27 Posts

Posted - 2011-06-20 : 04:36:49

Hi
I am trying to apply the use of variables in an SQL statement where I have to change dates many times in my Where conditions. I have declared 3 variables and set the variable values. The values are dates. I have tried many different variable types but always get the error msg "must declare the scalar variable @ValuationT". Is there something wrong with the syntax I am using? Please see query below.
Thanks

use AllegroMRTesting
GO
Declare @ValuationT nvarchar;
Set @ValuationT = '2011-05-12 23:00:00.000';
Declare @BegT nvarchar;
Set @BegT = '2011-05-01 00:00:00.000';
Declare @EndT nvarchar;
Set @EndT = '2015-10-01 00:00:00.000';
GO
SELECT
valuationdetail.tradebook tradebook,
valuationdetail.tradetype tradetype,
valuationdetail.pricestatus pricestatus,
valuationdetail.unit unit,
valuationdetail.priceindex priceindex,
valuationdetail.begtime begtime,
product.producttype producttype,
sum(valuationdetail.exposurequantity) as exp
--sum(valuationdetail.exposurequantity) as exp where unit = 'mt')
--(sum(valuationdetail.exposurequantity / 6.35)) as exp where unit = 'mt')
FROM valuation, valuationdetail, position, finposition, product
WHERE valuation.valuationmode = 'Position' and valuation.valuation=valuationdetail.valuation
and valuationdetail.position=finposition.position and valuationdetail.posdetail=finposition.posdetail and valuationdetail.product = product.product
and valuationdetail.position = position.position and valuationdetail.quantitytype <> 'LOSS'
and ( (valuation.valuationtype = 'SUMMATION'
and valuation.valuationtime = @ValuationT ) or (valuation.valuationtype = 'INCREMENTAL'
and valuation.valuationtime >= @ValuationT and valuation.valuationtime <= @ValuationT) )
and position.trade is not null AND ((valuationdetail.begtime< @EndT OR valuationdetail.begtime is null)
AND (valuationdetail.endtime> @BegT OR valuationdetail.endtime is null))
AND ( valuationdetail.exposure = 'MARKET'
AND valuationdetail.tradebook IN ('IRM Fuel Prop 1','IRM Fuel Prop 2','IRM Hedging','IRM Proprietary')
AND valuationdetail.tradetype IN ('Bullit Swap','Swap Exchange','Swap OTC','Swap OTC Special') )
--and (pricestatus
--and (valuationdetail.tradetype
--and valuationdetail.exposurequantity <> 0
group by valuationdetail.tradebook, valuationdetail.tradetype, valuationdetail.begtime, valuationdetail.unit, valuationdetail.pricestatus, valuationdetail.priceindex, product.producttype
order by valuationdetail.tradebook, valuationdetail.tradetype, valuationdetail.begtime, valuationdetail.unit, valuationdetail.pricestatus, valuationdetail.priceindex, product.producttype

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-20 : 04:39:11

A "GO" is the end of a batch.
After that the declared vars are not longer alive...

No, you're never too old to Yak'n'Roll if you're too young to die.