Named query parameter

Ignition
#1

Hi, I am using the below query. The parameter type is value(also tried using QueryString). And enclosed the value under value columns in quotes.

SELECT
[WorkCentre]–0
,[ProdOrder]–1
,[Material]–2
,[MatDescription]–3
,[DIFOTReqDlvDt]–37
,[DIFOTAckDlvDt]–38
,[DIFOTAckShipDt]–39
,[lastUpdated]–64
,[revenueTotal] ,
,[LengthExtra]–70
,[ALFExtra]
,[certificate]
,[OrderStatus]–86
,[OpenOrder]–87
,[Priority]–88
,[Complete]–89
FROM [TblExtOrders]
INNER JOIN
(
– SELECT B.Section,SUM(inDateRange) AS inDateRange,SUM(within28Days) AS within28Days FROM(
– SELECT TblExtOrders.Section ,count(TblExtOrders.Section) AS inDateRange,NULL AS within28Days FROM TblExtOrders WHERE 1=1 {whereInnerRange}

– GROUP BY TblExtOrders.Section
– UNION
SELECT TblExtOrders.Section,count(TblExtOrders.Section) AS within28Days FROM TblExtOrders WHERE 1=1 {whereOuterRange}

GROUP BY TblExtOrders.Section
– ) b GROUP BY b.Section
) AS B ON TblExtOrders.Section=B.Section WHERE 1=1

:whereAlloy
:whereState
:WhereFilter
:WhereProperty

But i am getting this error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘{’.
Can anyone suggest where am i going wrong?

#2

When you post code, you need to wrap it within a preformatted block using either this icon image or surround your code by three back ticks (```)

E.g.:

SELECT
[WorkCentre]–0
,[ProdOrder]–1
,[Material]–2
,[MatDescription]–3
,[DIFOTReqDlvDt]–37
,[DIFOTAckDlvDt]–38
,[DIFOTAckShipDt]–39
,[lastUpdated]–64
,[revenueTotal] ,
,[LengthExtra]–70
,[ALFExtra]
,[certificate]
,[OrderStatus]–86
,[OpenOrder]–87
,[Priority]–88
,[Complete]–89
FROM [TblExtOrders]
INNER JOIN
(
– SELECT B.Section,SUM(inDateRange) AS inDateRange,SUM(within28Days) AS within28Days FROM(
– SELECT TblExtOrders.Section ,count(TblExtOrders.Section) AS inDateRange,NULL AS within28Days FROM TblExtOrders WHERE 1=1 {whereInnerRange}
– GROUP BY TblExtOrders.Section
– UNION
SELECT TblExtOrders.Section,count(TblExtOrders.Section) AS within28Days FROM TblExtOrders WHERE 1=1 {whereOuterRange}

GROUP BY TblExtOrders.Section
– ) b GROUP BY b.Section
) AS B ON TblExtOrders.Section=B.Section WHERE 1=1

Looking at your query, at face value it looks like it has a number of syntax errors. I’m not sure what all of the “-0” “-38” parts are for?
Or whether this part below is part of your query?

:whereAlloy
:whereState
:WhereFilter
:WhereProperty

I assume this query is inside a named query since you mention a QueryString parameter.
What are the values you’re trying for the parameter (whereOuterRange)?

These are the parts that I can see that are wrong:

image
image1143×476 55.1 KB

Try this:

SELECT
	 [WorkCentre]
	,[ProdOrder]
	,[Material]
	,[MatDescription]
	,[DIFOTReqDlvDt]
	,[DIFOTAckDlvDt]
	,[DIFOTAckShipDt]
	,[lastUpdated]
	,[revenueTotal]
	,[LengthExtra]
	,[ALFExtra]
	,[certificate]
	,[OrderStatus]
	,[OpenOrder]
	,[Priority]
	,[Complete]
FROM 
	[TblExtOrders]
		INNER JOIN
	(
		SELECT Section, SUM(inDateRange) AS inDateRange, SUM(within28Days) AS within28Days FROM
		(
			SELECT Section, COUNT(Section) AS inDateRange, NULL AS within28Days
			FROM TblExtOrders
			WHERE 1=1 {whereInnerRange}
			GROUP BY TblExtOrders.Section
			
			UNION
			
			SELECT Section, COUNT(Section) AS inDateRange, NULL AS within28Days
			FROM TblExtOrders
			WHERE 1=1 {whereOuterRange}
			GROUP BY Section
			
		) b GROUP BY Section
	) AS B ON TblExtOrders.Section = B.Section
WHERE
	1=1
	{whereAlloy}
	{whereState}
	{WhereFilter}
	{WhereProperty}

PS. You’ll need to supply all of the QueryString parameters with a value; you can’t leave these blank. If you don’t want to add an extra where clause, set these to a single space " "

1 Like