Which entry in a df file defines the position of a field in

Posted by Stefan Marquardt on 09-Oct-2019 15:44

I added a new field and want to have it at a specific position for the SQL queries (the same as displayed in the Database structure in the PDS)
POSITION AND ORDER are fine, DataDictionary shows it at the desired position - but SQL clients using "select *" in the last column

All Replies

Posted by steve pittman on 09-Oct-2019 16:46

Hi Stefan,

For "select *"   field order ,in the query result set,  OE sql always uses the "physical order" of the fields defined in the table queried.

The physical order is the order in which the fields were defined. When a field is added to the set of fields in a table, the new field is always last in physical order.

OE sql does not use   ORDER or POSITION. I believe that POSITION in the .df corresponds to _Field._field-rpos in the db schema.

Enabling sql to use ORDER or POSITION for "select *" would be a feature enhancement, for our Product Management folks to consider.

hope this helps,          ....steve pittman  [OE sql software architect]

Posted by Stefan Marquardt on 10-Oct-2019 07:24

Hi Steve,

thank you for the fast reply.

Since today i didn't care so much about df files,  thought POSITION in df is r-pos and expecting a longer df load too.

This means I have no possibility to define the field order for SQL :-(

As we are using SQL more and more for data checks I started to defined the order by the main/foreign index. It's really useful to have the main field key values at the beginning to build WHERE statement in JDBC tools with a mouse (set the range to define where queries) to find records in related tables

Now the key needed to be enhanced and I can't get it at the end of the current key fields as it is possible in MS SQL Server (of course)

Means now i have to D&L the table to change the physical order, binary or text? I guess text :-)

This thread is closed