SQL Syntax in SBO

[Former Member]

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

[Former Member]

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.

[Former Member]

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

please!!!

[Former Member]

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??

[Former Member]

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

[Former Member]

Hello Xuechen,

in my experience this problem isn't connected to the convert function. I have the same issue with ALL variables when using queries with subselects or other kind of 'complex' queries...

Without using the convert, I wouldn't be able to get results by date anyway (may depends on the language setting of SBO !?).

One point is definetely the syntax used when referencing tables - not all is allowed when using variables.

See the other mentioned thread according to this: It was a query by SAP (!!!) and it did work only with fixed date range (I guess, they weren't able to solve the Problem themselves ). But after reworking it with another syntax for referencing tables, it worked with variable date range.

Regrads,

Dierk

[Former Member]

Has SAP found a fix for this yet?? I am getting the same problem when working with date ranges... It appears that it does not matter which way you twist the SQL code..COVERT, CAST what ever... It does not work..

Where T0.DocDate >= '[%01]' And docdate < = '[%01]'

[Former Member]

Have you tried using a stored procedure ? I use them for my complex queries, but I have not passed any parameters yet.