Where Clause on Dynamic Pivot causes error

  • 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.

  • 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

  • Hi john this is my table.

    Attachments:
    You must be logged in to view attached files.
  • 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

    Attachments:
    You must be logged in to view attached files.
  • 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.

    Attachments:
    You must be logged in to view attached files.
  • 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

  • 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