Asp Forum - Using 'in' keyword in axapta query - microsoft.public.axapta.programming

Hema

1/9/2006 6:43:00 AM

Hi,

i cannot use in keyword in axapta query. for eg:

select emp where emp.code in ('E001','E002','E003');

how do i convert the above sql query to axapta format? pls. provide the
solution. Thanx in advance.

regs,
Hema. S

12 Answers

Henke

1/9/2006 7:33:00 AM

I asked the same question a week ago. As far as I know, there is no
equivalence to the IN keyword. One solution is to loop through the first
table where you have ('E001','E002') and build a comma separated string and
then use it as range on the emp table.
/Henke

"Hema" <Hema@discussions.microsoft.com> skrev i meddelandet
news:41DD2FF3-B610-4CF2-B5F1-39501319B21A@microsoft.com...
> Hi,
>
> i cannot use in keyword in axapta query. for eg:
>
> select emp where emp.code in ('E001','E002','E003');
>
> how do i convert the above sql query to axapta format? pls. provide the
> solution. Thanx in advance.
>
> regs,
> Hema. S

Hema

1/9/2006 8:33:00 AM

Hi,

Thank u for ur solution. I got the result using range.

regs,
Hema. S

Andrew Jones

1/10/2006 10:29:00 AM

Be aware that that is a dangerous solution. If you have a lot of
possibilities to 'or', then you can end up creatnig a string which is too
long for SQL Server to handle (or maybe too long for Axapta. I'm not sure
where the actual problem lies). This will result in a SQL error when the
statement is passed through to the database server.

Andrew

"Hema" wrote:

> Hi,
>
> Thank u for ur solution. I got the result using range.
>
>
> regs,
> Hema. S

Necmi Göcek

2/2/2006 8:42:00 PM

The restriction about comma seperated string is 483. It means you can use 483
ORed values in QueryRange object. If i'm wrong please let me know.
--
_MIB_

"Andrew Jones" wrote:

> Be aware that that is a dangerous solution. If you have a lot of
> possibilities to 'or', then you can end up creatnig a string which is too
> long for SQL Server to handle (or maybe too long for Axapta. I'm not sure
> where the actual problem lies). This will result in a SQL error when the
> statement is passed through to the database server.
>
> Andrew
>
> "Hema" wrote:
>
> > Hi,
> >
> > Thank u for ur solution. I got the result using range.
> >
> >
> > regs,
> > Hema. S

Mike Frank

2/3/2006 7:26:00 AM

Hi Necmi,

> The restriction about comma seperated string is 483. It means you can use 483
> ORed values in QueryRange object. If i'm wrong please let me know.

Is it really 483 values or is the restriction on the string length? Where did you get this
information from?

I believe there is a restriction on query strings in SQL, bit I can't remember what the specifics are.

Mike

Necmi Göcek

2/7/2006 5:01:00 PM

First of all, The restriction about comma seperated field count is <= 474 not
483. Sorry for misinformation.

I found that number with this simple job.
static void N_QuerySize(Args _args)
{

InventTable inventTable;
Query q;
QueryRun qr;
QueryBuildDataSource qbdsInventTable;

int i;
str strItemIds;
;
while select ItemId from inventTable
{
i++;
if(i <= 474)
strItemIds += ","+inventTable.ItemId;
else
break;
}
i = 0;
strItemIds = strdel(strItemIds,1,1);
q = new Query ();
qbdsInventTable = q.addDataSource(tablenum(InventTable));
qbdsInventTable.addRange(fieldnum(InventTable, ItemId)).value(strItemIds);

qr = new QueryRun (q);
while (qr.next())
{
i++;
}

infolog.add(Exception::Info,qbdsInventTable.toString());
infolog.add(Exception::Info,strfmt("ORed fields Count: %1",i));
infolog.add(Exception::Info,strfmt("String Size:
%1",strlen(qbdsInventTable.toString())));
}
--
_MIB_

"Mike Frank" wrote:

> Hi Necmi,
>
> > The restriction about comma seperated string is 483. It means you can use 483
> > ORed values in QueryRange object. If i'm wrong please let me know.
>
> Is it really 483 values or is the restriction on the string length? Where did you get this
> information from?
>
> I believe there is a restriction on query strings in SQL, bit I can't remember what the specifics are.
>
> Mike
>

Steen Andreasen

2/7/2006 6:26:00 PM

The max length of a query range is 1000 chars as this is the max lenght
of a string in X++. The just released kernel rollout has changed the
limit to 999 as a range length of 1000 chars will fail using an Oracle
Database.

Please correct me if I am wrong.

Best regards,
Steen Andreasen
http://www.steenandreasen.com/axap...

Necmi GÃ¶cek wrote:

>First of all, The restriction about comma seperated field count is <= 474 not
>483. Sorry for misinformation.
>
>I found that number with this simple job.
>static void N_QuerySize(Args _args)
>{
>
> InventTable inventTable;
> Query q;
> QueryRun qr;
> QueryBuildDataSource qbdsInventTable;
>
> int i;
> str strItemIds;
> ;
> while select ItemId from inventTable
> {
> i++;
> if(i <= 474)
> strItemIds += ","+inventTable.ItemId;
> else
> break;
> }
> i = 0;
> strItemIds = strdel(strItemIds,1,1);
> q = new Query ();
> qbdsInventTable = q.addDataSource(tablenum(InventTable));
> qbdsInventTable.addRange(fieldnum(InventTable, ItemId)).value(strItemIds);
>
> qr = new QueryRun (q);
> while (qr.next())
> {
> i++;
> }
>
> infolog.add(Exception::Info,qbdsInventTable.toString());
> infolog.add(Exception::Info,strfmt("ORed fields Count: %1",i));
> infolog.add(Exception::Info,strfmt("String Size:
>%1",strlen(qbdsInventTable.toString())));
>}
>
>

--

Best Regards
Steen Andreasen

http://www.steenandreasen.com/axap...

Necmi Göcek

2/8/2006 7:56:00 AM

String size is 9512 so default max string size(1000) was exceeded

Here is sample output of previously mentioned job:
ORed fields Count: 474
String Size: 9512
SELECT * FROM InventTable WHERE ((ItemId = CD01111 OR ItemId = CD01112 .....
))

--
_MIB_

"Steen Andreasen" wrote:

> The max length of a query range is 1000 chars as this is the max lenght
> of a string in X++. The just released kernel rollout has changed the
> limit to 999 as a range length of 1000 chars will fail using an Oracle
> Database.
>
> Please correct me if I am wrong.
>
> Best regards,
> Steen Andreasen
> http://www.steenandreasen.com/axap...
>
>
> Necmi GÃ¶cek wrote:
>
> >First of all, The restriction about comma seperated field count is <= 474 not
> >483. Sorry for misinformation.
> >
> >I found that number with this simple job.
> >static void N_QuerySize(Args _args)
> >{
> >
> > InventTable inventTable;
> > Query q;
> > QueryRun qr;
> > QueryBuildDataSource qbdsInventTable;
> >
> > int i;
> > str strItemIds;
> > ;
> > while select ItemId from inventTable
> > {
> > i++;
> > if(i <= 474)
> > strItemIds += ","+inventTable.ItemId;
> > else
> > break;
> > }
> > i = 0;
> > strItemIds = strdel(strItemIds,1,1);
> > q = new Query ();
> > qbdsInventTable = q.addDataSource(tablenum(InventTable));
> > qbdsInventTable.addRange(fieldnum(InventTable, ItemId)).value(strItemIds);
> >
> > qr = new QueryRun (q);
> > while (qr.next())
> > {
> > i++;
> > }
> >
> > infolog.add(Exception::Info,qbdsInventTable.toString());
> > infolog.add(Exception::Info,strfmt("ORed fields Count: %1",i));
> > infolog.add(Exception::Info,strfmt("String Size:
> >%1",strlen(qbdsInventTable.toString())));
> >}
> >
> >
>
> --
>
> Best Regards
> Steen Andreasen
>
> http://www.steenandreasen.com/axap...
>

Mike Frank

2/8/2006 8:53:00 AM

I can confirm, what Necmi says, at least for SQL Server. The number seems to be 474. I added one
line to the test job <info(strfmt("String size range value: %1", strlen(strItemIds)));>
and I tested in an environment where the ItemId has various and different from what Necmi tested length.

For 474 the job works OK, starting from 475 the SQL Server reports an error
(Wrong syntax near ')'). Seems to be something with the value passing.

String size range value: 5190
SELECT * FROM InventTable WHERE ((ItemId = 0001 OR ItemId = 151129 ... OR ItemId = P 029 3810))
ORed fields Count: 474
String Size: 10911

As I wrote the line about the value passing, I thought off another possible test. So I added another
line <q.literals(true);> With this the query works up to 479 values, strange...

String size range value: 5257
SELECT WITH FORCE_LITERALS * FROM InventTable WHERE ((ItemId = 0001 ... OR ItemId = P 029 3820_KS))
ORed fields Count: 479
String Size: 11058

Steen: At least for SQL Server you seem to be wrong.

Mike

Necmi GÃ¶cek schrieb:
> String size is 9512 so default max string size(1000) was exceeded
>
> Here is sample output of previously mentioned job:
> ORed fields Count: 474
> String Size: 9512
> SELECT * FROM InventTable WHERE ((ItemId = CD01111 OR ItemId = CD01112 .....
> ))
>

Steen Andreasen

2/8/2006 8:33:00 PM

Thanks for correcting me.

I always use the EDT Range, and as a EDT of the type string has a max
length of 1000 chars I thought this was the limit. It is quite
interesting that the length of the string is not the limit.

I am not sure about Oracle. I have seen Axapta crash on Oracle using a
range more than 999 chars, but I don't know what the field count were.
However I haven't tried this example.

Best Regards
Steen Andreasen
http://www.steenandreasen.com/axap...

Mike Frank wrote:

> I can confirm, what Necmi says, at least for SQL Server. The number
> seems to be 474. I added one line to the test job <info(strfmt("String
> size range value: %1", strlen(strItemIds)));>
> and I tested in an environment where the ItemId has various and
> different from what Necmi tested length.
>
> For 474 the job works OK, starting from 475 the SQL Server reports an
> error
> (Wrong syntax near ')'). Seems to be something with the value passing.
>
> String size range value: 5190
> SELECT * FROM InventTable WHERE ((ItemId = 0001 OR ItemId = 151129 ...
> OR ItemId = P 029 3810))
> ORed fields Count: 474
> String Size: 10911
>
> As I wrote the line about the value passing, I thought off another
> possible test. So I added another line <q.literals(true);> With this
> the query works up to 479 values, strange...
>
> String size range value: 5257
> SELECT WITH FORCE_LITERALS * FROM InventTable WHERE ((ItemId = 0001
> ... OR ItemId = P 029 3820_KS))
> ORed fields Count: 479
> String Size: 11058
>
> Steen: At least for SQL Server you seem to be wrong.
>
> Mike
>
>
>
>
>
>
>
> Necmi GÃ¶cek schrieb:
>
>> String size is 9512 so default max string size(1000) was exceeded
>>
>> Here is sample output of previously mentioned job:
>> ORed fields Count: 474
>> String Size: 9512
>> SELECT * FROM InventTable WHERE ((ItemId = CD01111 OR ItemId =
>> CD01112 ..... ))
>>

microsoft.public.axapta.programming

Using 'in' keyword in axapta query

Hema

Henke

Hema

Andrew Jones

Necmi Göcek

Mike Frank

Necmi Göcek

Steen Andreasen

Necmi Göcek

Mike Frank

Steen Andreasen

x Login to ForumsZone