79518031

Date: 2025-03-18 16:00:22
Score: 3.5
Natty:
Report link

Can anybody tell me what's wrong with this query? Getting Error Incorrect syntax near ','. but it doesn't tell me where it is.


    mysql = mysql + " SET @sql = N'"
    mysql = mysql + " SELECT [REP CODE], [CUST CODE], [REP NAME], [CUSTOMER NAME],"
    mysql = mysql + "     '' +"
    mysql = mysql + "     (SELECT STRING_AGG(''ISNULL('' + QUOTENAME(MonthYear) + '', 0) AS '' + QUOTENAME(MonthYear), '', '')"
    mysql = mysql + "     FROM ("
    mysql = mysql + "         SELECT DISTINCT"
    mysql = mysql + "             DATENAME(MONTH, bolh_shp_or_prt_date) + '' '' + CAST(YEAR(bolh_shp_or_prt_date) AS NVARCHAR) AS MonthYear,"
    mysql = mysql + "             MIN(bolh_shp_or_prt_date) As MinDate"
    mysql = mysql + "         From sisl_data04.dbo.so_bol_headers"
    mysql = mysql + "         WHERE (bolh_salesrep_id = @salesrep_id OR @salesrep_id='''')"
    mysql = mysql + "           AND bolh_shp_or_prt_date BETWEEN @from_date AND @to_date"
    mysql = mysql + "           AND bolh_stage_flg >= @bolh_stage_flg"
    mysql = mysql + "         GROUP BY DATENAME(MONTH, bolh_shp_or_prt_date), YEAR(bolh_shp_or_prt_date)"
    mysql = mysql + "     ) AS MonthList"
    mysql = mysql + "     ) +"
    mysql = mysql + "     '',"
    mysql = mysql + "     ISNULL('' + (SELECT STRING_AGG(''ISNULL('' + QUOTENAME(MonthYear) + '', 0)'', '' + '') "
    mysql = mysql + "     FROM ("
    mysql = mysql + "         SELECT DISTINCT"
    mysql = mysql + "             DATENAME(MONTH, bolh_shp_or_prt_date) + '' '' + CAST(YEAR(bolh_shp_or_prt_date) AS NVARCHAR) AS MonthYear,"
    mysql = mysql + "             MIN(bolh_shp_or_prt_date) As MinDate"
    mysql = mysql + "         From sisl_data04.dbo.so_bol_headers"
    mysql = mysql + "         WHERE (bolh_salesrep_id = @salesrep_id OR @salesrep_id='''')"
    mysql = mysql + "           AND bolh_shp_or_prt_date BETWEEN @from_date AND @to_date"
    mysql = mysql + "           AND bolh_stage_flg >= @bolh_stage_flg"
    mysql = mysql + "         GROUP BY DATENAME(MONTH, bolh_shp_or_prt_date), YEAR(bolh_shp_or_prt_date)"
    mysql = mysql + "     ) AS MonthList) + '', 0) AS [TOTAL],"
    mysql = mysql + "     SortOrder, SortKey"
    mysql = mysql + " INTO #PivotResult"
    mysql = mysql + " FROM ("
    mysql = mysql + "     SELECT"
    mysql = mysql + "         bolh_salesrep_id AS [REP CODE],"
    mysql = mysql + "         bolh_cust_id AS [CUST CODE],"
    mysql = mysql + "         UPPER(sr.sr_salesrep_name) AS [REP NAME],"
    mysql = mysql + "         UPPER(c.cu_name) AS [CUSTOMER NAME],"
    mysql = mysql + "         DATENAME(MONTH, bolh_shp_or_prt_date) + '' '' + CAST(YEAR(bolh_shp_or_prt_date) AS NVARCHAR) AS [MonthYear],"
    mysql = mysql + "         SUM(ISNULL(bolh_taxinclship_amt, 0)) AS TOTALSales,"
    mysql = mysql + "         0 AS SortOrder, -- Regular rows (SortOrder = 0)"
    mysql = mysql + "         bolh_salesrep_id + ISNULL(bolh_cust_id, '''') AS SortKey"
    mysql = mysql + "     FROM so_bol_headers bh WITH (NOLOCK)"
    mysql = mysql + "     INNER JOIN sales_reps sr WITH (NOLOCK) ON bh.bolh_salesrep_id = sr.sr_salesrep_id"
    mysql = mysql + "     INNER JOIN customers c WITH (NOLOCK) ON bh.bolh_cust_id = c.cu_cust_id"
    mysql = mysql + "     WHERE (bolh_salesrep_id = @salesrep_id OR @salesrep_id='''')"
    mysql = mysql + "       AND bolh_shp_or_prt_date BETWEEN @from_date AND @to_date"
    mysql = mysql + "       AND bolh_stage_flg >= @bolh_stage_flg"
    mysql = mysql + "     GROUP BY bolh_salesrep_id, bolh_cust_id, sr.sr_salesrep_name, c.cu_name, DATENAME(MONTH, bolh_shp_or_prt_date), YEAR(bolh_shp_or_prt_date)"
    mysql = mysql + " ) AS SourceTable"
    mysql = mysql + " PIVOT ("
    mysql = mysql + "     SUM (TOTALSales)"
    mysql = mysql + "     FOR [MonthYear] IN ('' + @cols + '')"
    mysql = mysql + " ) AS PivotTable;"
    mysql = mysql + " SELECT [REP CODE], [CUST CODE], [REP NAME], [CUSTOMER NAME],"
    mysql = mysql + "     '' + @cols + '',"
    mysql = mysql + "     [Total]"
    mysql = mysql + " FROM ("
    mysql = mysql + "     SELECT [REP CODE], [CUST CODE], [REP NAME], [CUSTOMER NAME],"
    mysql = mysql + "         '' + @cols + '',"
    mysql = mysql + "         [TOTAL], 0 AS SortOrder, SortKey"
    mysql = mysql + "     FROM #PivotResult"
    mysql = mysql + "     Union ALL"
    mysql = mysql + "     SELECT"
    mysql = mysql + "         '''' AS [REP CODE],"
    mysql = mysql + "         '''' AS [CUST CODE],"
    mysql = mysql + "         '''' AS [REP NAME],"
    mysql = mysql + "         [REP CODE] + '' TOTAL'' AS [CUSTOMER NAME],"
    mysql = mysql + "         '' + @TOTALCols + '',"
    mysql = mysql + "         ISNULL(SUM([TOTAL]),0) AS [TOTAL],"
    mysql = mysql + "         1 AS SortOrder,"
    mysql = mysql + "         [REP CODE] + ''ZZZZZZ'' AS SortKey"
    mysql = mysql + "     FROM #PivotResult"
    mysql = mysql + "     GROUP BY [REP CODE]"
    mysql = mysql + "     Union ALL"
    mysql = mysql + "     SELECT"
    mysql = mysql + "         '''' AS [REP CODE],"
    mysql = mysql + "         '''' AS [CUST CODE],"
    mysql = mysql + "         '''' AS [REP NAME],"
    mysql = mysql + "         ''GRAND TOTAL'' AS [CUSTOMER NAME],"
    mysql = mysql + "         '' + @TOTALCols + '',"
    mysql = mysql + "         ISNULL(SUM([TOTAL]), 0) AS [TOTAL],"
    mysql = mysql + "         2 AS SortOrder,"
    mysql = mysql + "         ''ZZZZZZZZZZ'' AS SortKey"
    mysql = mysql + "     FROM #PivotResult"
    mysql = mysql + " ) AS FinalResult"
    mysql = mysql + " ORDER BY SortKey, SortOrder, [CUST CODE];"
    mysql = mysql + " DROP TABLE #PivotResult;';"

    mysql = mysql + " EXEC sp_executesql @sql, N'@salesrep_id NVARCHAR(MAX), @from_date DATE, @to_date DATE, @bolh_stage_flg NVARCHAR(10)', @salesrep_id, @from_date, @to_date, @bolh_stage_flg;"
Reasons:
  • RegEx Blacklisted phrase (2.5): Can anybody tell me what
  • Long answer (-1):
  • Has code block (-0.5):
  • Contains question mark (0.5):
  • Unregistered user (0.5):
  • Starts with a question (0.5): Can anybody
  • Low reputation (1):
Posted by: Harpreet