Skip to Content
avatar image
Former Member

SBO / ITK: Problem with query for legal consolidation

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Mar 05, 2004 at 05:25 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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