Hi everyone!
I've come across the following issue:
Initially a have a table the looks like this:
Table1:
| key_field | field1 | field2 | field3 |
| key1 | value1 | value2 | value3 |
At some point of my load script I use the cross_table function on this table, so that the result is as follows:
Table2:
| key_field | attribute_field | value |
| key1 | field1 | value1 |
| key1 | field2 | value2 |
| key1 | field3 | value3 |
Later on in my script I want to use column names (field1, field2, field3) and their respective values for building another table, the main difficulty is that they ought to be sorted in the order, in which they come as field names in the very first table. When you apply the cross tab function, the order gets mixed up, so i tried to use the FieldNumber function in the following way:
FieldNumber(attribute_field, 'Table1') as field_number
So i basically want the function to go through all the values in the attribute_field of Table2, find the respective column names in Table1 and return their numbers, which would solve my sorting problem.
But the function always returns zero, as if the fields with names "field1, ...2, ...3" don't exist in Table1, which can't possibly be true.
Moreover, if I pass the first argument as a string:
FieldNumber('field1', 'Table1') as field_number
it works just fine.
Could you please give any hints about what I am doing wrong?
