Is there a way to use "SELECT * FROM table", but EXCLUDE certain fields from the list?

[montypython1]

Greetings,

Is there a way to use "SELECT * FROM table", but EXCLUDE certain fields from the list?

Frequently, when working with tables having a very large number of fields, I need to EXCLUDE from the output (SELECT *) just a few of the fields. As an example, let's say my table has 100 fields, but I only want to output 90 fields. In the past, I have just had to manually type out each of the 90 fields within the SELECT statement.

Is there an easier and more efficient option with the SQL?

Thanks,
Dave Higgins

 

[Olaf Doschke]

Not in the form of some clause. But you can use AFIELDS and create the code (especially the field list) programmatically and then delete the unwanted fields.

Bye, Olaf.

 

[Tore Bleken]

Install the free utility called Thor, which contains SuperBrowse and a lot of other free tools. SuperBrowse has a field picker which generates the code for you.

http://vfpx.codeplex.com/wikipage?title=Thor&referringTitle=Home

 

[JRB-Bldr]

When I have to deal with a SQL Query that has many fields and I want to eliminate a few of them, I go ahead and have my query gather ALL of them - its easist that way.
And then I use ALTER TABLE to DROP only those select fields that did not want in the first place.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Dave, here's something you could do:

1. Call up the Local View Designer, by Selecting New View from either the project manager or the Database menu (when the database designer is open), or by typing [tt]CREATE VIEW[/tt] in the command window.

2. Select your table.

3. On the Fields page, click Select All.

4. From the Query menu, choose View SQL. You will see a code window showing (amongst lots of other stuff) a SELECT statement showing all the fields in the table.

5. Copy and paste the SELECT statement into your code editing window, then delete the fields you don't want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

And as there are always many ways, you might COPY TO (AddBS(GetEnv("TEMP"))+"resultname.dbf") FIELDS EXCEPT yourexcludedfieldlist FOR condition. As a query cursor also is just a dbf, you only will need to remove that table and this table will always be created on hdd and not in memory only.

It's not the preferred solution, as joins are harder to do (but also possible via RELATIONS), but it has exactly that option to exclude fields, no matter how the table "evolves" in the future.

And the future perspective of any fields you might or might not want to have in your result, if you extend your database, are reason for a rule of thumb of SQL to not use *, unless you're sure you want all fields, especially with such tables having many fields. Tables with repeated info are even reason to think about normalization of your table design.

Bye, Olaf.

 

[Olaf Doschke]

Another way: CREATE CURSOR with structure as wanted, then APPEND FROM dbtable.DBF. If the cursor definition skips fields, they will not be copied.

Bye, Olaf.

 

[montypython1]

Thank you all for your input!
Very clever ideas ... You are good at thinking outside of the box.
I will try to implement your ideas the next time I need to build a large SQL and exclude just a few fields.
Thanks,
Dave

 

[Mike Lewis]

Dave,

Be sure to let us know which (if any) fo these ideas you find works for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads