CODEWITHSUNDEEP
sql-servert-sql
Melinda
Melinda

Reputation: 1531

Datatype in INNER select statement is defined as MONEY but then is changed in outer SELECT statement to int datatype

In my t-sql code below, in the INNER SELECT I am doing a SUM(t.WrittenPremium) and the t.WrittenPremium is defined as a MONEY datatype but then in the OUTER SELECT statement when I hover over the a.WrittenPremium it is NOW an INT datatype. The reason for the question is that I'm getting an error in a SSRS report that I'm working on from the results of this query stating that the WrittenPremium cannot be converted to String though I'm not trying to convert anything to a string. Any help/direction would be appreciated. Thanks.

Here is my SQL code:

SELECT 
    CASE 
        WHEN a.[Period] = '' THEN a.[Year] + ' - Total '+a.TermType
        ELSE a.[Year] 
    END as [Year]
    , a.[Period]
    , a.TermType
    , a.WrittenPremium
FROM (
    SELECT
        CASE 
            WHEN GROUPING (t.[Year]) = 1 THEN 'Total'
            ELSE t.[Year]
        END as [Year]
        , ISNULL(t.[Period],'') as [Period]
        , ISNULL(t.TermType,'') as TermType
        , SUM(t.WrittenPremium) as WrittenPremium
        , RN
    FROM #temp t 
    GROUP BY GROUPING SETS ((t.[Year], t.[Period], t.TermType, t.RN), (t.[Year], t.TermType))
) a
ORDER BY 1 asc, a.RN asc, a.TermType;

Upvotes: 0

Views: 43

Answers (1)

whynot
whynot

Reputation: 106

Under the assuption that Year is an INT datatype I think the type mismatch is happen in the CASE setting Year. Please try this:

        CASE 
            WHEN GROUPING (t.[Year]) = 1 THEN 'Total'
            ELSE CAST(t.[Year] AS VARCHAR(10))
        END as [Year]

Upvotes: 1

Related Questions