Dale Fye
asked on
Follow-up question, Dynamic PIVOT query
Previous question here.
In the previous question, I forgot to mention that my production table was not exactly as listed because I assumed I could make the modifications I needed to the final query to pass in an additional variable @Product (values of 'Gas', 'Oil', 'Water') and select the appropriate column using a Case statement. So, the following query works.
Msg 8114, Level 16, State 5, Line1
Error converting data type varchar to float
I also tried it with:
Must declare variable "@Product"
So, the question is, how do I pass in a value for the Product (@Product) and retrieve the correct column from tbl_sysProduction based on that value in the dynamic SQL Statement?
In the previous question, I forgot to mention that my production table was not exactly as listed because I assumed I could make the modifications I needed to the final query to pass in an additional variable @Product (values of 'Gas', 'Oil', 'Water') and select the appropriate column using a Case statement. So, the following query works.
SET DATEFORMAT mdy;
Declare @Product nvarchar(5)
Declare @StartDate datetime2
Declare @EndDate datetime2
SET @Product = 'Gas'
Set @StartDate = '2014-09-01'
SET @EndDate = '2014-09-30'
--Define the string of dates to use in the Pivot clause
DECLARE @docDatesTable TABLE
(
[docDate] Datetime
);
INSERT INTO @docDatesTable
SELECT DISTINCT docDate FROM tbl_sysProduction
WHERE docDate >= @StartDate AND docDate < DATEADD(day, 1, @EndDate);
--Build the String containing the date values to be used in the pivot
DECLARE @docDates VARCHAR(MAX);
SELECT @docDates = COALESCE(@docDates + '],[', '') +
(LEFT(CONVERT(VARCHAR, [docDate], 101),6) + RIGHT(YEAR([docDate]),2))
FROM @docDatesTable ORDER BY [docDate]
--Build the dynamic SQL String
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM (
SELECT IsNull(W.Bolo_ID, F.Bolo_ID) as Bolo_ID
, IsNull(W.Common_Name, F.Facility_Name) as Well_Facility
, P.[Entity_ID]
, P.[docDate]
, Volume = [Gas]
FROM tbl_sysProduction as P
LEFT JOIN tbl_sysWells as W ON P.Entity_ID = W.Entity_ID
LEFT JOIN tbl_sysFacilities as F on P.Entity_ID = F.Entity_ID
) AS [SubTable]
PIVOT
(
SUM([Volume])
FOR [docDate] IN ([' + @docDates + '])
) AS [Pivot]
ORDER BY Bolo_ID, Well_Facility;
';
EXEC sp_executesql @sql;
But when I replace this line:, Volume = [Gas]
with:, Volume = CASE WHEN ' + @Product + ' = ''Gas'' THEN [Gas]
WHEN ' + @Product + '= ''Oil'' THEN [Oil]
WHEN ' + @Product + '= ''Water'' THEN [Water]
ELSE NULL END
I get an error message:Msg 8114, Level 16, State 5, Line1
Error converting data type varchar to float
I also tried it with:
, Volume = CASE WHEN @Product = ''Gas'' THEN [Gas]
WHEN @Product = ''Oil'' THEN [Oil]
WHEN @Product = ''Water'' THEN [Water]
ELSE NULL END
but that raised an error also:Must declare variable "@Product"
So, the question is, how do I pass in a value for the Product (@Product) and retrieve the correct column from tbl_sysProduction based on that value in the dynamic SQL Statement?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
And your name is?
CREATE TABLE [dbo].[tbl_sysProduction](
[Prod_ID] [int] IDENTITY(1,1) NOT NULL,
[Entity_ID] [int] NOT NULL,
[docDate] [datetime] NOT NULL,
[Oil] [float] NULL,
[Gas] [float] NULL,
[Water] [float] NULL,
[TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [PK_tbl_sysProduction] PRIMARY KEY CLUSTERED
(
[Prod_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Although I've accessed data from SQL Server a lot in the past, I've only recently really started building my own, so selection of float may have been the wrong datatype to emulate a double in Access.
it's Ben. Float is correct for a double.
Thanks
Thanks
ASKER
That took care of it. I should have done a SELECT on the value of @SQL before trying to execute it and it would have been obvious.
Yay excellent.
Do you mind posting the design of the table so I can see what the data types for the Gas, Oil and Water columns are?
Thanks