I have a database with 6 tables, each table has various fields, but all have 3 date fields in common (entryDate, modDate and delDate)
Does anyone know if there is a syntax where a VIEW can be created, leaving OUT those fields… but WITHOUT specifiy which fields to keep
the view is to contain only “active” records , i.e. those with a NULL delDate field
I would like to just pass the name of the data table to create the view
right now I have
SQL="CREATE VIEW view"+useTABLE+" As Select * FROM "+useTABLE+" WHERE delDATE Is NULL"
but obviously that keeps all the fields including the 3 common ones I don’t need in the view
Not in a single statement. But you can easily build the SQL statement programmatically, either in a SQL procedure or a Xojo method, by getting the field names of the table and looping through them, skipping those you don’t want.
Saw this tidbit on SO.
/* Get the data into a temp table */
SELECT * INTO #TempTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable
Thanks… I knew about those methods… Since this is a VIEW and not a derived table, it would have had to been a single statement… So I’ll just carry a bit extra baggage… its not a big deal…