Skip to Content
author's profile photo Former Member
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 a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on 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 a 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

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.