Skip to Content
0
Former Member
Feb 27, 2009 at 07:53 PM

Problems with B1 parsing a complex query that has string replacements

44 Views

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