SELECT in Firebird SQL: Can you Specify Columns to omit?
lazarus / 16-Databases

----------------------------------------------------------------------
cov · 2013-05-03 04:44

I'm a bit worried that the user of my program may need to select all
but one fields of a database comprising many fields.

The programatic creation of the database doesn't restrict the number
of columns, so there are potentially hundreds of fields.

That isn't a problem if all the fields are selected: Code:
[Select]SELECT * FROM FIELDS;
However, if the user wants to hide ther third column: Code:
[Select]SELECT FIELD1, FIELD2, FIELD4,......,FIELD101 FROM FIELDS;
Is there a way to say something like?:
Code: [Select]SELECT NOT FIELD3 FROM FIELDS
Alternatively is there a way of condensing the query so that I don't
hit any limit on the size of the query (which, Google tells me is
64kB, so I probably don't need to worry  ).

----------------------------------------------------------------------
BigChimp · 2013-05-03 06:43

1. Code: [Select]SELECT NOT FIELD3 FROM FIELDS No.
2. You can use a view that selects all but one field of the query.
This view behaves as a query and you can Code: [Select]SELECT * FROM
THEVIEW to select all but one field of the original table.

Hundreds of fields is not a good idea, but well. Don't know whether
Firebird has a limit on the number... probably they do.

----------------------------------------------------------------------
Groffy · 2013-05-03 06:48

Normaly I don't like it if I ask for help and I get an answer "think
about your design". Sorry, but I have to ask for what you need so much
database fields that you might run into the limitation of 64kb maximum
row size length??

----------------------------------------------------------------------
cov · 2013-05-03 07:21

As I said in my OP, the database is created programmatically.

It's extremely unlikely that the number of fields is that extreme, but
there's an outside chance that, under certain circumstances, it could
get pretty large.

In any case, suppose there are twenty fields (or even ten) and all but
one are shown.

Is there not a way to list the single field which is not been shown,
rather than all nineteen that are?

It's not big deal (considering the size limit of the SQL query string
allowable), but it might be useful.

----------------------------------------------------------------------
BigChimp · 2013-05-03 07:30

Hellooo, did you read my post?

Edit: never mind, this is going to be interminable again... I'll leave
you to it.

----------------------------------------------------------------------
Groffy · 2013-05-03 08:23

Whether it makes sense or not, one way would be to create the select
statement dynamically. You can inquire a comma separated list of table
fieldnames :

select List(RDB$FIELD_NAME) from RDB$RELATION_FIELDS where
RDB$RELATION_NAME = 'JOB' and RDB$FIELD_NAME <> 'ID'

In this example the tablename is "JOB" and the field you don't want to
see inside the select statement is "ID". With this list you can build
the select statement on the fly

select_statement = 'select ' + field_list + '  from JOB'

----------------------------------------------------------------------
ludob · 2013-05-04 05:24

If you are concerned about not showing a particular column and you are
using sqldb then simply hide your column with a
Code: [Select]SQLQuery.FieldByName('FieldToHide').Visible:=false;

----------------------------------------------------------------------
cov · 2013-05-06 07:05

Hi Big Chimp,

Your suggestion sound very good; I'll look into it.

I'm currently in an area without decent internet coverage and I
overlooked your response when replying to Groffy. Sorry.