I was wondering if the following was possible

We use a table called SIZESLIST which has a OFFSET field and ten other fields named
NAME_0, NAME_1 ... NAME_9

Can I select the correct one per row based on the offset using SQL.

Example:

SELECT  #EVALUATE("NAME_"+OFFSET")#, PRODUCT_ID, OFFSET FROM PRODUCT
INNER JOIN SIZESLIST ON (SIZESLIST.SIZE_ID = PRODUCT.SIZE_ID)

Would return the correct name field for a given offset.

Would I need to write a custom function for this?

Thanks Stuart

Stuart


You could use IF or CASE for it eg

IF(OFFSET= 0 THEN NAME_0 ELSE IF(OFFSET = 1 THEN NAME_1)) etc etc


Roy Lambert

Stuart,

<< Can I select the correct one per row based on the offset using SQL.

Example:

SELECT  #EVALUATE("NAME_"+OFFSET")#, PRODUCT_ID, OFFSET FROM PRODUCT
INNER JOIN SIZESLIST ON (SIZESLIST.SIZE_ID = PRODUCT.SIZE_ID)

Would return the correct name field for a given offset. >>

Use the CASE operator:

SELECT  CASE OFFSET
WHEN 1 THEN NAME_1
WHEN 2 THEN NAME_2

etc.

AS OFFSET, PRODUCT_ID, OFFSET FROM PRODUCT
INNER JOIN SIZESLIST ON (SIZESLIST.SIZE_ID = PRODUCT.SIZE_ID)

--
Tim Young
Elevate Software
www.elevatesoft.com

<< Use the CASE operator: >>

Thanks you both, this really helped.

Stuart ;o)