Hi -
I am curious to know why B1 fails on certain complex, yet correct, queries seemingly because they use a union and several string replacements.
Example, this query works fine in B1 client:
SELECT x.CardCode, x.Customer, x.State, x.Rep, sum(x.Total) as u2018Net Totalu2019
FROM (SELECT T0.CardCode as CardCode, T0.[CardName] as Customer, T1.state as State, T4.slpname as Rep, sum(T3.lineTotal) u2013 (t2.discsum) as Total
FROM OCRD T0 INNER JOIN CRD1 T1 ON (T0.CardCode = T1.CardCode and t0.shiptodef = t1.address) LEFT OUTER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry LEFT OUTER JOIN OSLP T4 ON T0.slpcode = t4.slpcode
WHERE T0.[CardType] = u2018Cu2019 and t1.adrestype = u2018Su2019 and t2.docdate between u2018[%0]u2019 and u2018[%1]u2019
group by t0.cardcode, t0.cardname, t1.state, t4.slpname, t2.discsum
UNION ALL
SELECT t0.cardcode as CardCode, T0.CardName AS Customer, t1.state as State, t4.slpname as Rep, -1*(SUM u2013 t2.discsum) AS Total
FROM OCRD T0 INNER JOIN CRD1 T1 ON (T0.CardCode = T1.CardCode and t0.shiptodef = t1.address) LEFT OUTER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry LEFT OUTER JOIN OSLP T4 ON T0.slpcode = t4.slpcode
WHERE T0.[CardType] = u2018Cu2019 and t1.adrestype = u2018Su2019 and T2.DocDate BETWEEN u2018[%0]u2019 AND u2018[%1]u2019
GROUP BY t0.cardcode, T0.CardName, t1.state, t4.slpname, t2.discsum) x
group by x.CardCode, x.Customer, x.State, x.Rep
order by x.CardCode
But adding one more string replacement, even though the query is still correct and can be run with success by using a hard coded string rather than the third string replacement, the query fails in B1's client?:
Failing example:
SELECT x.CardCode, x.Customer, x.State, x.Rep, sum(x.Total) as u2018Net Totalu2019
FROM (SELECT T0.CardCode as CardCode, T0.[CardName] as Customer, T1.state as State, T4.slpname as Rep, sum(T3.lineTotal) u2013 (t2.discsum) as Total
FROM OCRD T0 INNER JOIN CRD1 T1 ON (T0.CardCode = T1.CardCode and t0.shiptodef = t1.address) LEFT OUTER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry LEFT OUTER JOIN OSLP T4 ON T0.slpcode = t4.slpcode
WHERE T0.[CardType] = u2018Cu2019 and t1.adrestype = u2018Su2019 and t2.docdate between u2018[%0]u2019 and u2018[%1]u2019 and t4.slpname like '[%2]'
group by t0.cardcode, t0.cardname, t1.state, t4.slpname, t2.discsum
UNION ALL
SELECT t0.cardcode as CardCode, T0.CardName AS Customer, t1.state as State, t4.slpname as Rep, -1*(SUM u2013 t2.discsum) AS Total
FROM OCRD T0 INNER JOIN CRD1 T1 ON (T0.CardCode = T1.CardCode and t0.shiptodef = t1.address) LEFT OUTER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry LEFT OUTER JOIN OSLP T4 ON T0.slpcode = t4.slpcode
WHERE T0.[CardType] = u2018Cu2019 and t1.adrestype = u2018Su2019 and T2.DocDate BETWEEN u2018[%0]u2019 AND u2018[%1]u2019 and t4.slpname like '[%2]'
GROUP BY t0.cardcode, T0.CardName, t1.state, t4.slpname, t2.discsum) x
group by x.CardCode, x.Customer, x.State, x.Rep
order by x.CardCode
Is there some limitation in B1's client and if so, does anyone know of a workaround for queries like this, i.e., queries that are correct, but that B1's client cannot parse correctly?
Thanks,
Lang