Hi

I need to know whether it is possible in Baan SQL to get a result of a calculation between two fields into a variable and also sort based on the variable

E.G.
table t1
table fields a, b

temp variable var

select ((t1.a/t1.b)*100):var
from t1
order by v desc

Compiler is giving me error for '(' in the select part..

(this is easily possible in SQL of oracle, informix, ms-sql) :rolleyes:

Baan does indeed not allow this. You should write

table t1
table fields a, b

temp variable var

select t1.a, t1.b
from t1
order by v desc
selectdo
var = t1.a/t1.b*100


P.S. Just note when you are dividing two longs, the result will be a long too, even though there is a remainder.

I.e.

long lng1, lng2
dbl dbl1
lng1 = 10
lng2 = 4
dbl1 = lng1 / lng2
dbl1 will contain now 2.0, since dividing was done on two doubles. If you want dbl1 to contain the correct 2.5, then you should either convert your long-values to variables of type double (it is enough if lng2 will get a double format), or you could write:
dbl1 = lng1 / val(str$(lng2))


Regards,
Eli Nager

Dear Eli,

That's not what I am looking for. Actually I need the data in descending form of the calculate field. var which has been assigned the value of (t1.a/t1.b)*100

i.e. E.G.
table t1
table fields a, b

temp variable var

select ((t1.a/t1.b)*100):var
from t1
order by var desc

earlier i misstyped 'var' as 'v' in the order by segment

I need to perform some actions on records in descending order of the calculated field. Based on the condition I might not perform action on all records but only few records..

Can Embedded SQL or Dynamic SQL be used to achieve this in any way ?

May be need to create an array and store the calculated result in the array and then sort the array and do the transactions ??

But what are the limitations in terms of size of an Array ?

Oh ok, I didn't notice you wanted to have the calculated result in descending order. May I ask what it is for? If for a report, than you can sort it easily on report level...

In a program script you might be able to do it using dynamic sql, have no time to test it for you, but have done already some "crazy" things with dynamic sql which the regular Baan-queries would never allow...

Another option is to work with arrays and sort them using the qss.* sorting functions. How many records will you retrieve maximum...?

Regards,
Eli Nager

ok..i did analyze how to use array but it again remains unsolved..

pls see below example

string item.code(16)
long quantity
long item.cft (item cft would be defferent for differnet items)
long percent.cov

percent.cov = quantity/item.cft

Hence I would need an array to store item.code which is string
and second array which would store percent.cov

I want to sort in descending order of percent.cov. Hence if I sort percent.cov then the item.code array should also get sort or there should be a relation.

If I put a serial no as the 2nd dimension (which should be numeric) in percent.cov array then how do i incorporate serial no in the item.code array which is of type string ?

You can easily achive that using the qss-functions. Below is an example from the Baan manual:

Example 1

| You want to sort the following (string) array.
| The number is stored as a long on positions 8 to 11 by means of
| the function store.long(number, string_array(8,X))


string string_array(11,5)
long sort_def(2,4), search_def(1,4), ret

qss.start(sort_def, 1, 8)
qss.way(sort_def, 1, QSS.UP)
qss.type(sort_def, 1, DB.LONG)

qss.start(sort_def, 2, 1)
qss.way(sort_def, 2, QSS.DOWN)
qss.type(sort_def, 2, DB.STRING)
qss.length(sort_def, 2, 5)
| The function executes an ascending sort on position 8 of the

| string. The sort must be carried out as if this is a long value.
| In case of equal values, a descending sort is performed on the
| first five characters of the string.

ret = qss.sort(string_array, sort_def)

| After sorting the array will look as follows:

| If you want to know in which element the character combination
| 'iiii' is located and be sure that the argument to be searched
| is unique and cannot occur in the last two elements, the
| following search action must be started.

| It is assumed that the array is sorted.

qss.start(search_def, 1, 2) | search from position 2
qss.type(search_def, 1, DB.STRING)
qss.length(search_def, 1, 4)

ret = qss.search(QSS.SRC.IS.SORTED + QSS.EQUAL, "iiii",
string_array, search_def, 4)
| ret will now contain the value 2
| if the search argument is not unique, the following search
| action may be started:
ret = qss.search(QSS.SRC.IS.SORTED + QSS.EQUAL +
QSS.SRC.DUPL.ALLOWED, "iiii",

string_array, search_def, 4)

Example 2

string key(50)
string bank(50,10)
long country(2,4) | Key on country code and bank number
long sort_def(1,4)
long index

| 12345678901234567890123456789012345678901234567890
bank(1,1) = "10010ING Utrecht NL "
bank(1,2) = "10020ABN / AMRO Amsterdam NL "
bank(1,3) = "10030Rabobank Ede NL "
bank(1,4) = "20020National Westminster Bank London GB "
bank(1,5) = "30010Sparkasse Munchen D "

bank(1,6) = "40010Credit Lyonnais Paris F "
bank(1,7) = "90010Citi Bank New York USA"
bank(1,8) = "90020NBD Bank Grand RapidsUSA"

qss.start (country, 1, 48)
qss.type (country, 1, DB.STRING)
qss.length(country, 1, 3)
qss.start (country, 2, 1)
qss.type (country, 2, DB.STRING)
qss.length(country, 2, 5)

| WITHOUT LOOKUP.FOR.STRUCT
key = "NL"
index = qss.search(QSS.GTEQ, key, bank, country)
while index > 0 and bank(48,index;3) = "NL "

| process struct
key = bank(48,index;3) & bank(1,index;5)
index = qss.search(QSS.GT, key, bank, country )
endwhile

| WITH LOOKUP.FOR.STRUCT
key = " NL "
index = qss.search(QSS.LOOKUP.FOR.STRUCT+QSS.GTEQ,
key, bank, country)
while index > 0 and bank(48,index;3) = "NL "
| process struct
index = qss.search(QSS.LOOKUP.FOR.STRUCT+QSS.GT,
bank(1,index), bank, country)
endwhile

| Sorting bank table on bank description

qss.start (sort_def, 1, 6)
qss.type (sort_def, 1, DB.STRING)
qss.length(sort_def, 1, 2)


index = qss.sort( bank, sort_def)



I have used those functions a few times, once for very complicated and complexed sorting, and it worked well. The sample program actually speaks for itself, however let us know if you need more specific help.

Good luck!


Regards,
Eli Nager

Here is the WIKI link (http://www.baanboard.com/programmers_manual_baanerp_help_functions_searching_sorting_data_overview_and_synopsis) to the sample programs from the Baan manual..

Hi,

This performance dll (http://www.baanboard.com/baanboard/showthread.php?t=28569) can be of use to u if ur memory requirements fit within the array limits of 5 MB . This dll uses the dynamic arrays and qss functions .