SBO / ITK: Problem with query for legal consolidation

[Former Member]

Hello,

I just discovered a strange behaviour with a query, which is described in the SAP Configuration Guide for Legal Consolidation (part of ITK Vers. 1.0).

This is the Query (R3_EC_CS_EXCEL_RESULT_FINAL):

*********************************************************

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT LEFT (LTRIM(T3.DocCurrCod + ' ' + 'PLN'),3) AS CURRENCY,

T2.U_PACCT, 'C1000' AS CCODE, LEFT((SUBSTRING(T0.U_PPCODE,2,5) + SUBSTRING(T1.U_PPCODE,2,5) +

SUBSTRING(T2.U_PPCODE,2,5)),5) AS PPCODE, SUBSTRING(T2.U_FCODE,2,4) AS FUNC_AREA,

LEFT((SUBSTRING(T1.U_REGION,2,3) + SUBSTRING(T2.U_REGION,2,3)),3) AS COUNTRY, LEFT(LTRIM(SUBSTRING (T0.U_MVTP,2,3) +

SUBSTRING (T2.U_MVTP,2,3)), 3) AS TTY, SUM (T0.DEBIT - T0.CREDIT) AS VAL_LOC,

SUM (T0.FCDEBIT - T0.FCCREDIT) AS VAL_FRG

FROM (JDT1 T0 LEFT OUTER JOIN OCRD T1 ON T0.ShortName = T1.CardCode)

INNER JOIN OACT T2 ON T0.Account = T2.AcctCode

LEFT OUTER JOIN OCRN T3

ON T0.FcCurrency = T3.CurrCode

WHERE t0.RefDate >= '2004-01-01' and t0.RefDate <= '2004-12-31'

GROUP BY LEFT (LTRIM(T3.DocCurrCod + ' ' + 'PLN'),3), T2.U_PACCT,

LEFT((SUBSTRING(T0.U_PPCODE,2,5) + SUBSTRING(T1.U_PPCODE,2,5) +

SUBSTRING(T2.U_PPCODE,2,5)),5),

SUBSTRING(T2.U_FCODE,2,4), LEFT((SUBSTRING(T1.U_REGION,2,3) + SUBSTRING(T2.U_REGION,2,3)),3), LEFT(LTRIM(SUBSTRING (T0.U_MVTP,2,3) +

SUBSTRING (T2.U_MVTP,2,3)), 3)

*********************************************************

I found it quite inflexible to type in the date for the where clause direct into the query and modified it to

***********************

WHERE T0.RefDate >= CONVERT(DATETIME, '[%0]', 112) AND T0.RefDate <= CONVERT(DATETIME, '[%1]', 112)

***********************

In every other query I've made, this works, but it seems that in this query I'm not able to use ANY parameter (e.g. [%0]). I tested this for different variables and also without the set clause.

As a solution, I think of creating an UDT, where I store the values for the date, but this behaviour is quite strange, anyway...

Anyone got an idea, why this happens? Or is it just a bug in the SQL interpreter?

[former_member185703]

Hi Dierk,

there was / is a bug in the SAP Business One SQL interpreter when you are using complex SQL statements in conjunction with parameters and datetime conversions. As far as I know there is somebody caring for this, but I am not sure when there will be a fix available or whether it is already fixed in the current patch of rel. 6.5 or 6.2.

Best regards,

Frank

[Former Member]

Hello,

I just wanted to give some update to this problem (variables in queries sometimes not working) because I think that many of you have struggeld with this..

I recognized, that the tables which are used have to be determined in ONLY ONE SYNTAX: [dbo].[tablename] name

Now the query looks like:

-

---

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT

LEFT (LTRIM(T3.DocCurrCod + ' ' + 'PLN'),3) AS CURRENCY,

T2.U_PACCT,

'C1000' AS CCODE,

LEFT((SUBSTRING(T0.U_PPCODE,2,5) + SUBSTRING(T1.U_PPCODE,2,5) + SUBSTRING(T2.U_PPCODE,2,5)),5) AS PPCODE,

SUBSTRING(T2.U_FCODE,2,4) AS FUNC_AREA,

LEFT((SUBSTRING(T1.U_REGION,2,3) + SUBSTRING(T2.U_REGION,2,3)),3) AS COUNTRY,

LEFT(LTRIM(SUBSTRING (T0.U_MVTP,2,3) + SUBSTRING (T2.U_MVTP,2,3)), 3) AS TTY,

SUM (T0.DEBIT - T0.CREDIT) AS VAL_LOC,

SUM (T0.FCDEBIT - T0.FCCREDIT) AS VAL_FRG

FROM

([dbo].[JDT1] T0

LEFT OUTER JOIN [dbo].[OCRD] T1 ON T0.ShortName = T1.CardCode)

INNER JOIN [dbo].[OACT] T2 ON T0.Account = T2.AcctCode

LEFT OUTER JOIN [dbo].[OCRN] T3 ON T0.FcCurrency = T3.CurrCode

WHERE

(T0.RefDate >= CONVERT(DATETIME, '[%0]', 112) ) AND (T0.RefDate <= CONVERT(DATETIME, '[%1]', 112) )

GROUP BY

LEFT (LTRIM(T3.DocCurrCod + ' ' + 'PLN'),3), T2.U_PACCT,

LEFT((SUBSTRING(T0.U_PPCODE,2,5) + SUBSTRING(T1.U_PPCODE,2,5) +

SUBSTRING(T2.U_PPCODE,2,5)),5),

SUBSTRING(T2.U_FCODE,2,4), LEFT((SUBSTRING(T1.U_REGION,2,3) + SUBSTRING(T2.U_REGION,2,3)),3), LEFT(LTRIM(SUBSTRING (T0.U_MVTP,2,3) +

SUBSTRING (T2.U_MVTP,2,3)), 3)

For Browse

-

---

and it works!

I used this syntax anyway, but didn't recognize, that it wasn't used in this query (which was from SAP )...

As the brackets [] are placeholders in normal transact sql syntax, this has to be some special syntax of the implementation of the sql interpreter in SBO.

Bur this doesn't solve all my problems: I've got some more complicated queries with subselects as temporary tables and these still don't work with variables.

Example:

I have a query

<b>Select

T0.Var1,

T2.Var2,

subselect.alpha,

from

...

(select <i>alpha</i> from <i>beta</i>) as <i>subselect</i>

...

</b>

In this case I guess that I would have to use the same syntax as above - but how?

I tried the following:

[dbo].[(select <i>alpha</i> from <i>beta</i>)] subselect

and

(select <i>alpha</i> from <i>beta</i>) as subselect t0

One thing I still have to test is a double declaration like:

[dbo].[subselect] T0,

(select <i>alpha</i> from <i>beta</i>) as subselect

This testing will take a while, I guess because these queries are HUGE...

If someone has an idea, please keep me informed as I will do the other way round.

By the way: If you ever wondered, what for you need the term 'for browse': In Browse mode you have drill down functionality in the result panes!

Regards,

Dierk