Finance | Project Operations, Human Resources, ...

ORDER By in x++ sql statement

(0) Share

Report

Posted on by CRSW

627

Hi I wrote an SQL Statement in x++. This Statement should return the firstonly record ordered ascending by the inventsizeid

Now inventsizeid is a string value and so return the wrong value.

e.g. I do have records with inventsizeid 7.5 8.5 9 9.5 and 10

the Statement should return 7.5 but returns 10 as lowest value….and 9.5 as highest.

How can I get the correct results?
Is it possible to use conversion str2num in order by Expression?

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Martin Dráb 239,140 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Can you show us your code, please?

Was this reply helpful? Yes No
CRSW 627 on at

Like (0)

Report
Copy link

Link copied!

select firstOnly1 InventSizeId, OrderId, ExternalItemId,PriceGtz, PriceVend, LinePercentGtz, LinePercentVend from sizeLine
            order by sizeLine.InventSizeId asc
        where sizeLine.OrderId         == tmpVoucherLines.OrderId
            && sizeLine.ExternalItemId       == tmpVoucherLines.ExternalItemId
            && sizeLine.PurchPriceGtz     == tmpVoucherLines.PurchPriceGtz
            && sizeLine.PurchPriceVend       == tmpVoucherLines.PurchPriceVend
            && sizeLine.LinePercentGtz    == tmpVoucherLines.LinePercentGtz
            && sizeLine.LinePercentVend      == tmpVoucherLines.LinePercentVend;

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,140 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

This code returns just a single record - the one with the lowest InventSizeId.

I think you're confused by the fact that strings aren't sorted in the same way as numbers. Number 10 is higher than number 7.5, but a string starting with "1" is lower than a string starting with "7".

Was this reply helpful? Yes No
CRSW 627 on at

Like (0)

Report
Copy link

Link copied!

Right, but is there a way to get the records sorted as in inventsizeid is a number not a string

What I Need is the lowest and the highest InventSizeid. So I runthis Code twice. first with orderby asc and second with desc.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,140 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Well, is it guaranteed that all InventSizeIds for all items can be converted to numbers? If not, you seem to be doing a wrong thing.

For instance, you might have sizes like XXL.

If all really can be converted to numbers, then you can create a view and add a computed column, which will contain InventSizeId coverted to a float number. Then you can sort by this field.

If you have a mix of values, you may want to adjust numeric strings to be sortable in the way you want (e.g. 07 instead of 7).

Was this reply helpful? Yes No
CRSW 627 on at

Like (0)

Report
Copy link

Link copied!

Yes in our case it is guaranteed

Was this reply helpful? Yes No
Blue Wang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi CRSW,

Sorting string fields is usually done，the first thing you need to make sure is that the format, length, etc. are the same. for example set 1 to 01.

You can add new fields to the table and keep the numeric columns sorted.

Was this reply helpful? Yes No