Michael asked 3 years ago
I’ve seen this report before, but cant’ find the solution now. For the short-term, I’m just appending a WHERE clause on all my subqueries( WHERE 1 = 1).
I’ve just upgraded to 2.8 and any of my reports that have a sub-query are generating an error when I also include filters. I use sub-queries to avoid issues with sorting and grouping with complex table joins.
The Query is getting an “AND” clause appended, it should be a WHERE Clause.
Steps: Create a report with a sub-query and then add a filter to the report. I use the keyword SUBQUERY to designate an inline query in this format:
Select a,b,c from
(Select a, b, c from table_name) SUBQUERY
When I run the report and add a filter, it appends AND to the Query like this:
Select a,b,c from
(Select a, b, c from table_name) SUBQUERY AND FILTER = ‘SELECTION’
Any thoughts? I had a gander at the code but didn’t see anything obvious.
Thanks
Mike
Please advise
Thanks
Mike
Abu Ghufran Staff answered 3 years ago
Hello,
Please send the exact sql query so I can generate same scenario here. The code behind checks if the query contains WHERE keyword in query to decide whether to use WHERE or AND in appending filters.
Michael answered 3 years ago
Attached is the full query and the error-message that’s returned when you select a drop-down filter and enter search-text in one of the fields. The field “PRODUCT-CODE” is a search-field, OPEN_CO is a drop-down select-field with Yes or No as options.
Thanks
Mike
———————-8< —————————-
SELECT
KEYFIELD,
BASE_ID,
DESCRIPTION,
LOT_ID,
PRODUCT_CODE,
OPEN_CO
FROM
(
SELECT
WO.BASE_ID + ‘-‘ + WO.LOT_ID AS KEYFIELD,
WO.BASE_ID,
P.DESCRIPTION,
WO.LOT_ID,
WO.PRODUCT_CODE,
CASE
WHEN COL.PART_ID IS NULL THEN ‘NO’
ELSE ‘YES’
END AS OPEN_CO
FROM
{OJ WORK_ORDER WO
LEFT JOIN PART_SITE_VIEW P
ON WO.PART_ID = P.PART_ID
LEFT JOIN
(SELECT DISTINCT COL.PART_ID FROM
{OJ CUST_ORDER_LINE COL
LEFT JOIN CUSTOMER_ORDER CO
ON COL.CUST_ORDER_ID = CO.ID
}
WHERE CO.STATUS = ‘R’
AND COL.ORDER_QTY > TOTAL_SHIPPED_QTY
AND COL.LINE_STATUS = ‘A’
) COL
ON WO.PART_ID = COL.PART_ID
}
WHERE WO.TYPE = ‘M’ AND WO.SUB_ID = ‘0’
) SUBQUERY
Couldn’t execute query. SQLState: 42000 Error Code: 156 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword ‘AND’. – SELECT count(*) as c FROM (SELECT KEYFIELD, BASE_ID, DESCRIPTION, LOT_ID, PRODUCT_CODE, OPEN_CO FROM ( SELECT WO.BASE_ID + ‘-‘ + WO.LOT_ID AS KEYFIELD, WO.BASE_ID, P.DESCRIPTION, WO.LOT_ID, WO.PRODUCT_CODE, CASE WHEN COL.PART_ID IS NULL THEN ‘NO’ ELSE ‘YES’ END AS OPEN_CO FROM {OJ WORK_ORDER WO LEFT JOIN PART_SITE_VIEW P ON WO.PART_ID = P.PART_ID LEFT JOIN (SELECT DISTINCT COL.PART_ID FROM {OJ CUST_ORDER_LINE COL LEFT JOIN CUSTOMER_ORDER CO ON COL.CUST_ORDER_ID = CO.ID } WHERE CO.STATUS = ‘R’ AND COL.ORDER_QTY > TOTAL_SHIPPED_QTY AND COL.LINE_STATUS = ‘A’ ) COL ON WO.PART_ID = COL.PART_ID } WHERE WO.TYPE = ‘M’ AND WO.SUB_ID = ‘0’ ) SUBQUERY AND ( “PRODUCT_CODE” LIKE N’%Twin%’ AND “OPEN_CO” = ‘Yes’ )) pg_tmp
Abu Ghufran Staff answered 3 years ago
This issue was solved by using latest build (remote session).
Closing this ticket.
