" The declare SQL construct or statement is not supported" on gridview visual studio 2015

See more:

VB

Gridview

SQL-Server-2014

Hi.

I have this query , using unpivot SQL statement. When I run the query on SQL server, it execute and gives out the result that I desired. But, when I want to use the same query on Gridview function on Visual Studio 2015, using VB , it said that " The Declare SQL construct or statement is not supported." So, How do I need to rearrange the unpivot SQL statement as it is not supported on gridview?

What I have tried:

 declare @MyTable table(
 
   [Total Interest] money,
  [Total Principal] money,
  [Total Penalty] money,
  [Total Charged] money

   )

  insert into @MyTable 
  
select (SELECT SUM([Total Interest Overdue(MYR)]) AS [Total Interest Overdue(MYR)] FROM (SELECT SUM(l.interestOverdue) * d.bidspotrate AS [Total Interest Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR] ),
(SELECT SUM([Total Principal Overdue(MYR)]) AS [Total Principal Overdue(MYR)] FROM (SELECT SUM(l.principalOverdue) * d.bidspotrate AS [Total Principal Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR] ),
(SELECT SUM([Total Penalty Overdue(MYR)]) AS [Total Penalty Overdue(MYR)] FROM (SELECT SUM(l.penaltyOverdue) * d.bidspotrate AS [Total Penalty Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode WHERE (l.source = 'Excel ') GROUP BY d.bidspotrate) AS [Total in MYR]),
(  select sum([Total Charged Overdue(MYR)])  as 
 [Total Charged Overdue(MYR)] from (select (SUM (l.chargedOverdue) * d.bidspotrate) as 
 [Total Charged Overdue(MYR)] from loan l, danadb d  where l.currency=d.ccycode   group by d.bidspotrate) as [Total in MYR])

    select [Type], [Total] from
 (select * from @MyTable) Src
  unpivot ([Total] for [Type] in ([Total Interest],[Total Principal],[Total Penalty],[Total Charged])) Res

The result of the query is

Type | Total
Total Interest | 10039176.9812
Total Principal | 92207576.6798
Total Penalty | 91959.7905
Total Charged | 21677.153


-------------------------------------------------------------------

The second query that I tried is

select [TOTAL]
from
(
select (SELECT SUM([Total Interest Overdue]) AS [Total Interest Overdue] FROM (SELECT SUM(l.interestOverdue) * d.bidspotrate AS [Total Interest Overdue] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR] ) as [Interest],
(SELECT SUM([Total Principal Overdue]) AS [Total Principal Overdue] FROM (SELECT SUM(l.principalOverdue) * d.bidspotrate AS [Total Principal Overdue] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR] ) [Principal],
(SELECT SUM([Total Penalty Overdue]) AS [Total Penalty Overdue] FROM (SELECT SUM(l.penaltyOverdue) * d.bidspotrate AS [Total Penalty Overdue] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode WHERE (l.source = 'Excel ') GROUP BY d.bidspotrate) AS [Total in MYR]) [Penalty],
( select sum([Total Charged Overdue]) as
[Total Charged Overdue] from (select (SUM (l.chargedOverdue) * d.bidspotrate) as
[Total Charged Overdue] from loan l, danadb d where l.currency=d.ccycode group by d.bidspotrate) as [Total in MYR]) [Charged] ) d

unpivot

(
[TOTAL] for amount in ([Interest], [Principal],[Penalty] , [Charged] )) pvt1

The result is as below

TOTAL
--------------
10039176.9812472
92207576.6797772
91959.790477519
21677.1530053711


I want another column which is TYPE to appear, but I did not know how to do it .