June 21, 2012 at 4:10 am
declare @AsOfDate DATETIME = '2012-06-20 00:00:00.000'
DECLARE @CSV NVARCHAR(4000) = ''
DECLARE @CurveFamily_Swap INT = dbo.GetConfigurationValue_Int('Fireball', 'CurveFamily - Swap')
DECLARE @CurveFamily_Libor INT = dbo.GetConfigurationValue_Int('Fireball', 'CurveFamily - LIBOR')
SELECT
@CSV =
@CSV +
CONVERT(VARCHAR, Date, 101) + ',' +
CONVERT(VARCHAR, Value/10000.0) + ',' +
CONVERT(VARCHAR, POWER(1.0 + (Value/40000.0), -(4.0 * DATEDIFF(D, AsOfDate, Date)/360.0))) + ','
FROM
Fireball.dbo.BenchMarkCurvePoint
WHERE
AsOfDate = @AsOfDate AND
(
CurveFamilyId = @CurveFamily_Swap OR
CurveFamilyId = @CurveFamily_Libor
)
ORDER BY
Date
SELECT @CSV = LEFT(@CSV, LEN(@CSV) - 1)
--getting error at above statement please help me to resolve it.
@CSV value is coming empty what to do?
June 21, 2012 at 8:19 am
What happens if you issue the below command before you run the select
SET CONCAT_NULL_YIELDS_NULL OFF
It could be that some component of your concatenation is NULL and therefore the concatenation comes back as NULL.
June 21, 2012 at 8:22 am
ashuthinks (6/21/2012)
declare @AsOfDate DATETIME = '2012-06-20 00:00:00.000'DECLARE @CSV NVARCHAR(4000) = ''
DECLARE @CurveFamily_Swap INT = dbo.GetConfigurationValue_Int('Fireball', 'CurveFamily - Swap')
DECLARE @CurveFamily_Libor INT = dbo.GetConfigurationValue_Int('Fireball', 'CurveFamily - LIBOR')
SELECT
@CSV =
@CSV +
CONVERT(VARCHAR, Date, 101) + ',' +
CONVERT(VARCHAR, Value/10000.0) + ',' +
CONVERT(VARCHAR, POWER(1.0 + (Value/40000.0), -(4.0 * DATEDIFF(D, AsOfDate, Date)/360.0))) + ','
FROM
Fireball.dbo.BenchMarkCurvePoint
WHERE
AsOfDate = @AsOfDate AND
(
CurveFamilyId = @CurveFamily_Swap OR
CurveFamilyId = @CurveFamily_Libor
)
ORDER BY
Date
SELECT @CSV = LEFT(@CSV, LEN(@CSV) - 1)
--getting error at above statement please help me to resolve it.
@CSV value is coming empty what to do?
Not much info to go on here since you didn't provide ddl and sample data. My guess is you have a null somewhere in your data.
CONVERT(VARCHAR, Date, 101) + ',' +
CONVERT(VARCHAR, Value/10000.0) + ',' +
CONVERT(VARCHAR, POWER(1.0 + (Value/40000.0), -(4.0 * DATEDIFF(D, AsOfDate, Date)/360.0))) + ','
If Date or Value is null then the entire addition will be NULL.
BTW, you really should not use reserved words as column names, it will cause you nothing but grief. Also you are converting this data to varchar but you did not specify the size. Do you know what the default varchar size? Do you know what it is in all version of sql server? Me neither, that is why you should specify a size. varchar(10) or whatever is a reasonable size for your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply