Problems with B1 parsing a complex query that has string replacements

[Former Member]

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

[Former Member]

Hi Lang,

There is definitely limitation on B1 to run complicated queries. You probably need to simplify it to make the last step outside of the query.

However, when I correct the syntax error from your query, it runs well. It is not as complicated as it appear to be:

SELECT x.CardCode, x.Customer, x.State, x.Rep, sum(x.Total) AS 'Net Total'
FROM(SELECT T0.CardCode as CardCode, T0.CardName as Customer, T1.state as State, T4.slpname as Rep, (sum(T3.lineTotal) - (t2.discsum)) as Total
FROM DBO.OCRD T0 
INNER JOIN DBO.CRD1 T1 ON T0.CardCode = T1.CardCode and t0.shiptodef = t1.address and t1.adrestype = 'S'
LEFT JOIN DBO.OINV T2 ON T0.CardCode = T2.CardCode
LEFT JOIN DBO.INV1 T3 ON T2.DocEntry = T3.DocEntry
LEFT JOIN DBO.OSLP T4 ON T0.slpcode = t4.slpcode
WHERE T0.CardType = 'C' and t2.docdate between '[%0]' and '[%1]'
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, -(sum(T3.lineTotal) - (t2.discsum)) as Total
FROM DBO.OCRD T0 
INNER JOIN DBO.CRD1 T1 ON T0.CardCode = T1.CardCode and t0.shiptodef = t1.address and t1.adrestype = 'S'
LEFT JOIN DBO.ORIN T2 ON T0.CardCode = T2.CardCode
LEFT JOIN DBO.RIN1 T3 ON T2.DocEntry = T3.DocEntry
LEFT JOIN DBO.OSLP T4 ON T0.slpcode = t4.slpcode
WHERE T0.CardType = 'C' and t2.docdate between '[%0]' and '[%1]'
group by t0.cardcode, t0.cardname, t1.state, t4.slpname, t2.discsum) AS x
group by x.CardCode, x.Customer, x.State, x.Rep
order by x.CardCode

Thanks,

Gordon

[Former Member]

Thanks for varifying that. XL Reporter makes more sense for some of these complex queries that B1 can't handle. Also, one could create a web app that talks to the B1 database directly to do readonly queries of a more complex nature. Not sure about license limitations on connecting via a bespoke web app though. Anyone know about that?

Thanks,

Lang