SELECT in Firebird SQL: Can you Specify Columns to omit?

[cov]

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]

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]

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]

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]

Hellooo, did you read my post?

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

[Groffy]

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]

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]

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.