Create view minus specific fields

  1. 7 weeks ago

    Dave S

    Jun 30 San Diego, California USA

    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

    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...

  2. Jay M

    Jun 30 Pre-Release Testers, Xojo Pro NC, USA

    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.

  3. Tanner L

    is not verified Jun 30 Pre-Release Testers Toronto, Canada

    Saw this tidbit on SO.

    /* Get the data into a temp table */
    SELECT * INTO #TempTable
    FROM YourTable
    /* 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
  4. Dave S

    Jun 30 Answer San Diego, California USA

    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...

or Sign Up to reply!