Comparing Data from Multiple SQL Expressions

[Awat]

Hi,
I have 10 sql expressions that return 10 different numbers: 139, 141, 143, 145,...,157. Each number associates with data from a field in a table. In CR report, each record could have more than one number.

ID[tab]exp1[tab]exp2[tab]exp3[tab]exp4[tab]exp5[tab]exp6[tab]exp7[tab]exp8[tab]exp9[tab]exp10
001[tab]139[tab]141
002[tab][tab][tab][tab][tab][tab]143[tab][tab][tab][tab]147
003[tab][tab][tab][tab][tab][tab][tab][tab][tab]145

I need to get the lowest number that each record has and then write a formula to get data from a table that associates with that lowest number.

For example, ID 001 will have order code #2 (139 is the lowest#) while ID 002 with have order code #4 (143 is the lowest#).
Table
No Data
139 order code #2
141 order code #3
143 order code #4
Note: I use sql expressions to get the numbers because they are related to another field that is not mentioned here.

My question is how do I compare values from different sql expression to get the lowest number. Please advise. Thanks.

 

[lbass]

I think you could use a formula like this:

minimum([{%exp1},{%exp2},{%exp3}, etc.])

Then you could use this formula to link to a subreport (also linked on ID and placed in the ID section) to grab the related field in the order code table.

-LB

 

[Awat]

Hi LB,
Thanks for the reply.
I tried minimum([{%exp1},{%exp2},{%exp3},...]) but the formula returned 0 instead of the 139, 141,.... Any other advice would be appreciated.
Awat

 

[lbass]

I guess you would have to create formulas like this:

if isnull({%exp1}) or
{%exp1} = 0 then
999 else
{%exp1}

Then substitute these in the minimum formula.

-LB