November 22, 2019 at 9:08 am
Hello
Im having issues with my stored procedure. The procedure has this query that uses dynamic pivot to view my information. However when i tried to put where clause it gives me an
'Operand type clash: date is incompatible with int' i keep finding solution for this error but no luck fixing it. Below is my query
ALTER procedure [dbo].[xSalePerformance]
@fromDate NVARCHAR(MAX),
@toDate NVARCHAR(MAX)
AS
DECLARE
@columns NVARCHAR(MAX) = '',
@sqlquery NVARCHAR(MAX) = '';
SELECT
@columns += ISNULL(QUOTENAME(supplier_name),0) + ','
FROM
[dbo].[psgtcSupplier]
ORDER BY
supplier_name;
SET @columns = LEFT(@columns, LEN(@columns) - 1);
SET @sqlquery =N'
SELECT * FROM (
SELECT c.xname as Salesman,f.supplier_name as SupplierName, ISNULL(SUM(a.[netAmount]),0) as NetAmount FROM [dbo].[psgtcOrder_items] as a
INNER JOIN(SELECT [orderID],[orderNumber],[cust_id],[salesman_id],[dateReceived] FROM [dbo].[psgtcOrders]) as b on a.order_id = b.orderID
INNER JOIN(SELECT [id] ,[fname] + [lname] as xname ,[warehouseID] FROM [dbo].[psgtc_saleman]) as c on b.salesman_id = c.id
INNER JOIN(SELECT [product_ID],[category_id] FROM [dbo].[psgtcProducts]) as d on a.product_id = d.product_ID
INNER JOIN(SELECT [category_id],[category_name],[supplier_id]FROM [dbo].[psgtcSupplierCategory]) as e on d.category_id = e.category_id
INNER JOIN(SELECT [supplier_id],[supplier_name]FROM [dbo].[psgtcSupplier]) f on e.supplier_id = f.supplier_id
WHERE b.[dateReceived] >= '+@fromDate+' AND b.[dateReceived] <= '+@toDate+'
GROUP BY c.xname, f.supplier_name,b.[dateReceived]
) as INDZ
PIVOT ( SUM(INDZ.NetAmount) FOR INDZ.SupplierName IN ('+ @columns +') ) as Ara;'
EXECUTE sp_executesql @sqlquery;
Here is the result of the above query
DECLARE @return_value int
EXEC @return_value = [dbo].[xSalePerformance]
@fromDate = N'2019-01-01',
@toDate = N'2019-01-31'
SELECT 'Return Value' = @return_value
Msg 206, Level 16, State 2, Line 5
Operand type clash: date is incompatible with int
(1 row(s) affected)
Can someone help me solve my problem in this query? Been searching google for the error but it does not help me resolve the problem.
November 22, 2019 at 9:22 am
Without being able to see your tables, it's difficult to advise. Have you tried capturing @sqlquery and running it from a query window?
By the way, your stored procedure is horribly vulnerable to SQL injection. I strongly advise that you declare @fromDate and @toDate as date and pass them as parameters into your sp_executesql statement.
John
November 22, 2019 at 12:13 pm
this is what i want to achieve. In here i had problems regarding how to change null to 0, i tried using ISNULL or COALESCE and its not working gotta find a way how to do that. Please refer to the attached information
November 22, 2019 at 12:18 pm
And when i add WHERE Clause i get the error "Operand type clash: date is incompatible with int" not sure whats causing the issue why i get that error.
All i want is to get the total sales of each sales personnel for each Products of the given date using pivot.
Please refer the the attached for the error.
November 22, 2019 at 12:25 pm
Just put a PRINT @sqlquery in there and you'll see exactly what's going on. You don't have any quotes round your date value.
Now, all you need to do is choose appropriate data types and parameterise your query. That way, your name won't appear on the news as the person who wrote the code that caused a data breach.
John
November 22, 2019 at 1:06 pm
Thanks John i found the problem... I noticed in the query that i don't have single qoutes( ' ) to my dates. Got it fixed now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply