CODEWITHSUNDEEP
c#asp.netsql
Pod Mays
Pod Mays

Reputation: 2593

using SELECT results as INSERT INTO VALUES shows CLR type error

I'm trying to execute this SQL command in the VS Query Builder:

INSERT INTO DATA_TEMP (ITEM, Q1, Q2, Q3, Q4, TOTAL)
SELECT DISTINCT
    DATA_2.ITEM,
    (SELECT AMOUNT AS Expr1
     FROM DATA
     WHERE (QTR = 1) AND (DATA_2.ITEM = ITEM) AND (FY = @fy)
       AND (BU = @bu) AND (PERIOD = @per)) AS Q1,
    (SELECT AMOUNT AS Expr1
     FROM DATA AS DATA_3
     WHERE (QTR = 2) AND (DATA_2.ITEM = ITEM) AND (FY = @fy)
       AND (BU = @bu) AND (PERIOD = @per)) AS Q2,
    (SELECT AMOUNT AS Expr1
     FROM DATA AS DATA_3
     WHERE (QTR = 3) AND (DATA_2.ITEM = ITEM) AND (FY = @fy)
       AND (BU = @bu) AND (PERIOD = @per)) AS Q3,
    (SELECT AMOUNT AS Expr1
     FROM DATA AS DATA_3
     WHERE (QTR = 4) AND (DATA_2.ITEM = ITEM) AND (FY = @fy)
       AND (BU = @bu) AND (PERIOD = @per)) AS Q4,
    (SELECT SUM(AMOUNT) AS Expr1
     FROM DATA AS DATA_3
     WHERE (DATA_2.ITEM = ITEM) AND (FY = @fy) AND (BU = @bu)
       AND (PERIOD = @per)) AS Total
FROM DATA AS DATA_2 INNER JOIN
     DATA AS DATA_1 ON DATA_2.ID = DATA_1.ID
WHERE (DATA_2.ITEM = @item)

Gives me this error:

Error Source: .Net SqlClient Data Provider Error Message: Parameter 3 ([C:\test.MDF].[].[unknown]): The CLR type does not exist or you do not have permissions to access it.

Weird thing is, when I execute the SELECT and INSERT INTO commands individually, it works fine. It also works when I compile the website by the way.

But it doesn't work when using Stored Procedures which is why I wanted to know what's causing this.

Upvotes: 1

Views: 924

Answers (2)

sweetfa
sweetfa

Reputation: 5845

The problem most likely is due to a parsing problem.

Try adding the following to your final WHERE clause:

WHERE (DATA_2.ITEM = @item) AND (@fy = @fy) AND (@bu = @bu) AND (@per = @per)

I suspect it gets hung up on parameters within the SELECT portion of the code that are not defined in the final where clause.

Upvotes: 2

Thomas
Thomas

Reputation: 64645

You could write this query much simpler:

Insert DATA_TEMP( ITEM, Q1, Q2, Q3, Q4, TOTAL )
Select D2.ITEM
    , Min( Case When QTR = 1 Then D1.AMOUNT End ) As Q1
    , Min( Case When QTR = 2 Then D1.AMOUNT End ) As Q2
    , Min( Case When QTR = 3 Then D1.AMOUNT End ) As Q3
    , Min( Case When QTR = 4 Then D1.AMOUNT End ) As Q4
    , Sum( AMOUNT ) As Total
From DATA As D1
    Join DATA_2 As D2
        On D2.ID = D1.ID
Where ITEM = @item
    And FY = @fy
    And BU = @bu
    And PERIOD = @per
Group By D2.ITEM

Granted, this doesn't solve your CLR issue but it might make it simpler to test.

Upvotes: 1

Related Questions