cancel
Showing results for 
Search instead for 
Did you mean: 

Simple Cuestion

Former Member
0 Kudos

Hi All:

I am trying to make a simple Query an a UDT, and I can't get the results is I use a condition, I get the error (-2006) encoutered, has any one the same problem?. No way to make conditions in UDT? I am using 6.5 SP01.

The QUERY is:

SELECT * FROM [@PLAZOS] T0 WHERE T0.U_numped =[%0] FOR BROWSE

No way to show the data in any UDT.

Thanks all

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Yaniv:

I just want to make the Query with the Query Analizer, I don't understand How such an easy query I can't make it in SBO.

What is the solution for this???.

I am going to post a Sap Note in the market Place, because I need to make condition's query's in SBO 6.5

Former Member
0 Kudos

Hi,

If I understand correctly - you're trying to use a Variables with UT.

SAP Business One query interface does not recognize variable for fields of User-Defined Tables, nevertheless fields of tables that was add directly to the database, not using SAP Business One customizing tools.

It is important to understand that the syntax ‘[%0]’ is not sql server syntax, but Business One’s – which means that Business One reads the SQL statement and edits it before it is being executed by the sql server,

In case that there are variables with non system tables involved in the SQL statement, Business One cannot translate it to valid SQL statement, and this is the reason that it fails when the sql sever tries to execute.

In general, it is not recommended to add table directly to the database, since those table will not be recognize by the application when the tables list is presented (by the query generator or interface), it is not possible to add data to these table using SAP Business One GUI, and they will not necessarily be supported in upgrade process.

A possible solution in order to execute the query will be to store the variable as sql server variables, by the following syntax:

/SELECT FROM [dbo].[OINV] T0/

declare @fromdate as datetime

/* WHERE */

set @fromdate = /* T0.DocDate */ '[%0]'

/SELECT FROM [dbo].[OINV] T1/

declare @todate as datetime

/* WHERE */

set @todate = /* T1.DocDate */ '[%1]'

Now, @fromdate & @todate can be used instead of '[%0]', '[%1]' in the query or as parameters of the stored procedure.

The workaround was suggested by Avi Lupu

Regards,

Yaniv G.

Former Member
0 Kudos

Yaniv,

First, thanks for your interest.

Now, I must say that we are not understanding each other.

The table we are trying to read and filter had been created inside Business One. Every field where added inside Business One And every try is done with the Query Generator.

Perhaps your phrase "SAP Business One query interface does not recognize variable for fields of User-Defined Tables" is the answer to our question. But I think that would be a bug. And you have explain the reason why it should work.

"Business One reads the SQL statement and edits it before it is being executed by the sql server". This said, SBO must only show the parameter input window, and let the user fill it with a right value.

It doesn´t even work with the code and name field of the user tables.

Hope you understand me,

Ibai Peña

PD: Regards to Avi Lupu, I´ve meet him in a workshop in Madrid.

Former Member
0 Kudos

Another comment about it.

The filters did work with system table´s user defined fields. The fields are same created for user or system tables.

Why did then filters work for system table´s UDF and not for user tables UDF?

Regards again,

Ibai Peñ

Former Member
0 Kudos

Hi

Quering a User Table is possible and easy.

Some of the SQL syntax is not supported in the DI API Recordset object (there is an SDN post on this issue).

The query you've posted looks to be valid and if the SQL Where clause works fine in the SAP Business one application - it should be executed via the DI API recordset.

you can try to run the same query via ADO to isolate DI problems.

Please refer to the DI API HELP on how to use a Recordset Query.

If you still encounter problems implementing this issue -

post another reply and I'll attach a sample.

Regards,

Yaniv G.

SDK Consultant,

SAP Manage Israel.

Former Member
0 Kudos

Sorry Yaniv, but it doesnt work, not even in the query generator. I remenber that I go throw this and didn´t succed to make it work.

It looks like very simple, but it really doesn´t work for me too. The problem happens only with user table and trying to compare a parameter to its field.

Natalia´s example is enough to simulate the error again.

Hope you can give us a solution.

Regards,

Ibai Peña

PD: I have try it with single quotes in the '[%0]' too.

PD2:The exact error is 'Registratrion Data' (-2006)

Former Member
0 Kudos

Hi again,

I used the following Q:

Call oRS.DoQuery("SELECT * FROM [@SERVICECALLMAINDATA] T0 WHERE T0.U_cardcode ='a' ")

works fine for me.

Maybe try and remove the FOR BROWSE statement

since it might conflict with the recordset definition.

Yaniv G.

Former Member
0 Kudos

hi Yaniv:

I thing I have not posted my cuestion well.

If I put the condition handly as you have make,it work's well, even in the query generator, and with a recodset in DI.

But my problem is to make it directly in SBO, without any development, directly in the Query generator o with thi wizard, and when I try to make it a get the exact result as Ibai, error registration data (-2006)

when a Put :

SELECT * FROM [@CERT1] T0 WHERE T0.U_fase <b>=[%0]</b> FOR BROWSE

thank's