Skip to Content
author's profile photo
Former Member

SQL Syntax in SBO

Hello,

I just had to open a new Topic for that issue, cause I see it's drifting away from my first chosen title

"SBO / ITK: Problem with query for legal consolidation"´

and so misleading.

If you didn't read that thread:

I have a problem with variables in queries.

Sometimes it's not possible to use variables for e.g. date ranges as I get the error message 'registration data error -2006 occured'.

I recognized, that it has to do with the referencing on tables, which has to be done in a special way: .[tablename] myname

This works for most queries, but not all.

I've got some queries using subselects. I want to reference on these subselects like temporary tables.

Example:

I have a query

Select

T0.Var1,

T2.Var2,

subselect.alpha,

from

...

(select alpha from beta) as subselect

...</b>

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

I tried the following:

.[(select alpha from beta)] subselect

and

(select alpha from beta) as subselect t0

The naming of subselects works when using the very first method (in the example) but not if I try to use variables.

My next idea was creting views out of the subselects ending up in a query like this:

-

-


SELECT

T0.Docnum AS 'Doc_Number',

T2.Deliveryno AS 'Delivery',

T0.DocDate AS 'PostingDate',

T0.CardCode AS 'Customer/Supplier Code',

T0.CardName AS 'Customer/Supplier Name',

(T0.DocTotalSy-T0.VatSumSy) AS 'Net Sales',

COALESCE (T1.Prep,0) AS 'Prepayment',

T2.COGS AS 'COGSDel',

T3.COGS AS 'COGSINV',

(COALESCE(T2.COGS,0) + COALESCE(T3.COGS,0)) as 'Sum_Cogs',

((T0.DocTotalSy - T0.VatSumSy) + COALESCE (T1.Prep,0) - (COALESCE(T2.COGS,0) + COALESCE(T3.COGS,0)) ) as 'Gross Profit',

(CASE ((T0.DocTotalSy-T0.VatSumSy) + COALESCE (T1.Prep,0)) when '0' then '0' else (((T0.DocTotalSy-T0.VatSumSy) + COALESCE

(T1.Prep,0) - (COALESCE(T2.COGS,0) + COALESCE(T3.COGS,0))) / ((T0.DocTotalSy-T0.VatSumSy) + COALESCE (T1.Prep,0)) * 100)

end) as 'Gross Profit %'

FROM

.[OINV] T0

LEFT OUTER JOIN .[PPM] T1 on T0.DocNum = T1.DocNum

LEFT OUTER JOIN .[DLN] T2 ON t0.DocNum = T2.DocNum

LEFT OUTER join .[INV] T3 on T0.DocNum = T3.DocNum

WHERE

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

-

-


PPM, DLN and INV are views, I created.

But also this doesn't work!

Anyone else got an idea?

By the way: What is the syntax in SBO for creating batches? I tried to use the 'Go' command of MS-SQL and SBO recognized it as a key word, but only to tell me that my syntax was wrong around it...

Any other idea, how to put several statements (including several Create Views) in one query?

Regards,

Dierk

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • author's profile photo
    Former Member
    Posted on Apr 22, 2004 at 12:58 PM

    Hello,

    I have the same experience when working with date range. It might be a bug - you can not use convert() function (or more than 2 convert() functions, I don't remember) in SBO query. You need to use something like "WHERE (T0.DocDate >= '[%0]' AND T0.DocDate <= '[%1]')".

    Xuechen

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Jun 11, 2004 at 12:08 PM

    Hey rebiata ( rebiata its in Russian) i'am from Poland and i would like to show how i manage this problem :

    first i declare variables:

    DECLARE @SlpCode int

    DECLARE @dataOD datetime

    DECLARE @DataDo datetime

    second i assign values and make the parameters ( remeber datasources must have unique numbers!!!)

    set @SlpCode = ( SELECT T0.SlpCode AS 'Kod sprzedawcy' FROM [dbo].[OSLP] T0 WHERE T0.SlpName = N'[%0]' )

    set @DataOd = (SELECT Top 1 T1.DocDate FROM [dbo].[OINV] T1 WHERE T1.DocDate = CONVERT(DATETIME, '[%1]', 112) )

    set @DataDo = (SELECT Top 1 T2.DocDate FROM [dbo].[OINV] T2 WHERE T2.DocDate = CONVERT(DATETIME, '[%2]', 112) )

    and then i assign some data again

    set @DataOd = CONVERT(DATETIME, '[%1]', 112)

    set @DataDo = CONVERT(DATETIME, '[%2]', 112)

    and run my procedure

    execute dbo.rozliczenie @SlpCode, @DataOd, @DataDo

    give me a sign - it is work??

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Jun 11, 2004 at 12:09 PM

    give me a solution to put parameters on paper!!!

    please!!!

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Jun 14, 2004 at 10:24 AM

    HI all,

    Currently - SAP Business One Recordset does not support complex queries like :

    1. Nested Query

    2. Union

    3. Like

    4. Alias

    you can use Stored Procedures to workaround these limitations.

    Regards,

    Yaniv G.

    SDK Consultant,

    SAP Manage Israel.

    Add comment
    10|10000 characters needed characters exceeded