Query parameter

Emanuele Croci

Hi,
i implemented this query:

SELECT T3.U_CodiceArticolo AS CodiceArticolo, T5.ItemName AS DescrizioneArticolo,
T3.U_CodSeriale AS CodSeriale, T4.CardName AS NomeCliente, T3.U_NumOdV AS NumeroOdV,
T4.DocDate AS DataOdV, T4.DocStatus AS StatoOdV, T0.DocNum AS NumeroConsegna, T0.DocDate AS DataConsegna, T0.DocStatus AS StatoConsegna
FROM ODLN T0 INNER JOIN
DLN1 T1 ON T0.DocEntry = T1.DocEntry RIGHT OUTER JOIN
[@TC08_ODVCODSERIALI] T3 ON T1.BaseRef = T3.U_DocEntry LEFT OUTER JOIN
ORDR T4 ON T3.U_DocEntry = T4.DocEntry LEFT OUTER JOIN
OITM T5 ON T3.U_CodiceArticolo = T5.ItemCode
WHERE (T3.U_CodSeriale = '[%0]')

but it doesn't work.
'' SEWSY error message is visualized in SBO.

If I don't use the [%0] parameter, but insert a valid value, i.e. '1234' the query works fine.

I also used a new parameter [%TC_CODSERIAL], but it doesn't work.

Can someone help me please?

Regards

Emanuele

Philipp Emmenegger

Hi Emanuele

Use a Paramenter with is not longer than 8 Characters

Regards, Philipp

Emanuele Croci

Dear Philipp,
I used TC_CS (Code U003007, Type Alphanumeric):


SELECT T3.U_CodiceArticolo AS CodiceArticolo, T5.ItemName AS DescrizioneArticolo,
T3.U_CodSeriale AS CodSeriale, T4.CardName AS NomeCliente, T3.U_NumOdV AS NumeroOdV,
T4.DocDate AS DataOdV, T4.DocStatus AS StatoOdV, T0.DocNum AS NumeroConsegna, T0.DocDate AS DataConsegna,
T0.DocStatus AS StatoConsegna
FROM ODLN T0 INNER JOIN
DLN1 T1 ON T0.DocEntry = T1.DocEntry RIGHT OUTER JOIN
[@TC08_ODVCODSERIALI] T3 ON T1.BaseRef = T3.U_DocEntry LEFT OUTER JOIN
ORDR T4 ON T3.U_DocEntry = T4.DocEntry LEFT OUTER JOIN
OITM T5 ON T3.U_CodiceArticolo = T5.ItemCode
WHERE (T3.U_CodSeriale = '[%TC_CS]')



but it doesn't work...
I have the same error...


Regards,
Emanuele

Martin Cerasuolo

Hello my name is Martin Cerasuolo from the Netherlands. Maybe you can try to create the parameter without the _

Emanuele Croci

Dear Martin,
I tried with the TCCS user parameter:

SELECT T3.U_CodiceArticolo AS CodiceArticolo, T5.ItemName AS DescrizioneArticolo, T3.U_CodSeriale AS CodSeriale, T4.CardName AS NomeCliente, T3.U_NumOdV AS NumeroOdV, T4.DocDate AS DataOdV, T4.DocStatus AS StatoOdV, T0.DocNum AS NumeroConsegna, T0.DocDate AS DataConsegna, T0.DocStatus AS StatoConsegna
FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry RIGHT OUTER JOIN [@TC08_ODVCODSERIALI] T3 ON T1.BaseRef = T3.U_DocEntry LEFT OUTER JOIN ORDR T4 ON T3.U_DocEntry = T4.DocEntry LEFT OUTER JOIN OITM T5 ON T3.U_CodiceArticolo = T5.ItemCode
WHERE (T3.U_CodSeriale = '[%TCCS]')

but it doesn't work.....

Regards,
Emanuele

Philipp Knecht

Hi Emanuele

B1 has its own Query Parser so this smells like an B1 issue.

hth

Lars Pelzer

Dear Emanuele,

I had the same problem. The SQL engine from B1 can't handle that many joins in 1 query. It is possible when you call upon the query through Coresuite Designer, that the query will work. I know, sounds strange, but it worked for me....

Greetz

Philipp Knecht

fyi:

We are using our own optimized ADO.NET connection for data retrieval.
This means if the Query works in Query Analyzer (MS SQL) the it'll work in coresuite too.

hth

Emanuele Croci

Hi,

SAP Business One does not identify variables [%] in long queries composed either using the Query Generator or the Query Wizard. In this case modify your query for example as following:

/*SELECT FROM [dbo].[OUSR] T0*/
declare @Anno as Varchar(4)
/* WHERE */
set @Anno = /* T0.Info1File */ '[%0]'

declare @Mese As Varchar(2)
declare @Giorno As Varchar(2)
declare @FirstDayOfWeek as DateTime

set @Mese = '12'
set @Giorno = '14'

set @FirstDayOfWeek = DATEADD(DD, 1 - DATEPART(DW, @Mese + '/' +
@Giorno + '/' + @Anno), @Mese + '/' + @Giorno + '/' + @Anno)

select @FirstDayOfWeek


Bye

Emanuele

Philipp Knecht

Hi Emanuele

Thanks for providing this information.

Best Regards