Strange behavior with select statement and order by RecId in x++

(2) Share

Report

Posted on by jmcmillan

Greetings,

I'm trying to pull the first record in a table (CustConfirmJour) ordering by RecId. But for some reason I'm getting some arbitrary record about 42 rows into the table.

public static void main(Args _args)
{
    CustConfirmJour custConfirmJour;

    select firstonly crosscompany RecId, SalesId from custConfirmJour
        order by custConfirmJour.RecId asc
        where custConfirmJour.RecId > 5637144575;
}

If I add 'generateonly' and pull the query from the table buffer, this is what comes up:

SELECT TOP 1 T1.RECID,T1.SALESID,T1.DATAAREAID FROM CUSTCONFIRMJOUR T1

WHERE ((PARTITION=5637144576) AND (RECID>?))

The order by portion of the query seems to not be used. The RecId used in the where clause is one less from the start of the partition. The same result occurs if I remove that clause. I'm not sure what I'm missing here.

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (6)

Answers (2)

Anton Venter 20,301 Super User 2025 Season 2 on at

Like (0)

Report

Strange behavior with select statement and order by RecId in x++

Hi,

Your select statement contians the firstOnly X++ keyword, that's why in SQL the "order by" is dropped in the SQL statement. The partition parted is added automatically and you can just ignore it. Unless your data is partioned of course but I doubt it.

Was this reply helpful? Yes No
jmcmillan 96 on at

Like (0)

Report
Strange behavior with select statement and order by RecId in x++

@Anton Venter

Thank you for the response. That's interesting, I didn't think it would assume that seeing how the SQL generated for the firstonly keyword is TOP 1. I've taken out the order by clause and left the where clause the same to see what the outcome would be.

public static void main(Args _args) { RecId currentRecId = 0; CustConfirmJour custConfirmJour; select firstonly crosscompany RecId, SalesId from custConfirmJour where custConfirmJour.RecId > currentRecId; info( strFmt( "RecId: %1 SO: %2, Co: %3", int642Str(custConfirmJour.RecId), custConfirmJour.SalesId, custConfirmJour.DataAreaId)); }

I also declared a RecId variable earlier in the code to use it in the where clause just in case there was some kind of type mismatch issue (which I wouldn't think it'd matter as it is a bigint type in the SQL table). Unfortunately, it's still pulling the same record as before instead of the very first record.

RecId: 5637145337 SO: SO0000005, Co: 10

As a test, I declared the currentRecId variable by assigning it the very first record's RecId, and change the where clause to filter for just that single record. This time it pulled it correctly.

public static void main(Args _args) { RecId currentRecId = 5637144576; CustConfirmJour custConfirmJour; select firstonly crosscompany RecId, SalesId from custConfirmJour where custConfirmJour.RecId == currentRecId; info( strFmt( "RecId: %1 SO: %2, Co: %3", int642Str(custConfirmJour.RecId), custConfirmJour.SalesId, custConfirmJour.DataAreaId)); }

RecId: 5637144576 SO: 1280314, Co: 12

Finally, I tested again by changing the where clause back to greater than, in hopes of pulling the next record as is the case when tested directly in SQL.

public static void main(Args _args) { RecId currentRecId = 5637144576; CustConfirmJour custConfirmJour; select firstonly crosscompany RecId, SalesId from custConfirmJour where custConfirmJour.RecId > currentRecId; info( strFmt( "RecId: %1 SO: %2, Co: %3", int642Str(custConfirmJour.RecId), custConfirmJour.SalesId, custConfirmJour.DataAreaId)); }

RecId: 5637145337 SO: SO0000005, Co: 10

It pulls the same arbitrary record in the 42nd row again. So it seems the strange behavior occurs when there is a comparative operator in the where clause. I'm new to x++ development... is this normal?

Was this reply helpful? Yes No
Verified answer

Anton Venter 20,301 Super User 2025 Season 2 on at

Like (0)

Report

Strange behavior with select statement and order by RecId in x++

Yes, this normal. In your last example, the current RecId is 5637144576 and the X++ select query returns RecId 5637145337. This is completely correct because the returned RecId is greater than the current RecId.

If you want the first one, you will have to force the sorting of the data, but as I said, using the firstOnly keyword drops the order by clause in SQL. You could try to add the Index <IndexName> keyword to your X++ select statement to force sorting by an index. Or drop the firstOnly keyword but this fetches more data than required.

Was this reply helpful? Yes No
Verified answer

Raj Borad 1,362 on at

Like (0)

Report

Strange behavior with select statement and order by RecId in x++

Hi @jmcmillan,

You need to explicitly sort using an index.

The below X++ code you can try:

select firstonly crosscompany index IndexColumn RecId, SalesId from custConfirmJour
order by RecId asc
where custConfirmJour.RecId > yourvalue;

Replace IndexColumn with the name of an index that includes RecId. This forces the SQL layer to follow the order.

Have you tried while select and beark after get the first record?

while select crosscompany RecId, SalesId from custConfirmJour order by RecId asc
where custConfirmJour.RecId > yourvalue
{
info(...);
break;
}

Thanks.

Was this reply helpful? Yes No
Suggested answer

jmcmillan 96 on at

Like (0)

Report
Strange behavior with select statement and order by RecId in x++

Thank you @Raj Borad and @Anton Venter for your responses. Both responses included the answer to my question, so I'll be sure to mark them appropriately. This is the final code that I landed on that provided the expected results I was looking for in my scenario.

RecId currentRecId = 0; CustConfirmJour custConfirmJour; select firstonly crosscompany RecId, SalesId from custConfirmJour index RecId where custConfirmJour.RecId > currentRecId;

I verified with a loop that included updating the currentRecId variable that this does indeed pull the very next record in the table each time. I also found that by using the index keyword, x++ automatically uses an order by clause as well. I had to dig a little to figure out what to use as the identifier for the RecId index as it isn't in the listed indexes in the AOT for this specific table (RecId indexes are a parameterized function for D365 tables in VS), but was able to verify the naming convention with what is in the SQL database for the CustConfirmJour table.

I still don't know why this is necessary in the first place, but I'm happy that I have a solution to my problem. Thank you again for your help!

Was this reply helpful? Yes No
Anton Venter 20,301 Super User 2025 Season 2 on at

Like (0)

Report

Strange behavior with select statement and order by RecId in x++

You're welcome. Good to hear that it's working.

Was this reply helpful? Yes No