Skip to Content
author's profile photo Former Member
Former Member

Problems with B1 parsing a complex query that has string replacements

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Feb 27, 2009 at 08:42 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.