SQL Command help

[JoeCastor]

I am trying to use SQL command to create a new table that is like an existing table except to add a new key, and to include data for records meeting a certain criteria (NotActive =TRUE). I try to execute the command first without an AS and I receive an error saying an AS is required. I add the AS and the error says “unexpected token AS.” Am I misunderstanding how this works?

CREATE TABLE "RetiredMembers" (ID  INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1)  PRIMARY KEY, LIKE "MemberMaster" INCLUDING DEFAULTS EXCLUDING IDENTITY)  AS  (SELECT * FROM "MemberMaster" WHERE "NotActive"  = TRUE)  WITH DATA

[Ratslinger]

Having tried variations of you statement and comparing to manual, don’t see a way for that combination to work. I did have success with the following:

DROP TABLE "RetiredMembers";

CREATE TABLE "RetiredMembers" 
AS
(SELECT * FROM "MemberMaster" WHERE "NotActive" = TRUE)
WITH DATA;

ALTER TABLE "RetiredMembers" ADD ID   INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1);

ALTER TABLE "RetiredMembers" ADD PRIMARY KEY (ID);

The first statement drops the existing "RetiredMembers’ table (needed many times in tests). The second statement (as in the manual) creates the new table with the specific data wanted. The third table adds the new field (at end of fields) and inserts the auto-increment number in each record. The final statement make the new field the primary key.

Edit:

Here is another method - create DB then insert wanted records. This will put ID field as first field:

DROP TABLE "RetiredMembers";

CREATE TABLE "RetiredMembers" 
(
  ID   INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) PRIMARY KEY,
  LIKE "MemberMaster" INCLUDING DEFAULTS EXCLUDING IDENTITY
);

INSERT INTO "RetiredMembers" ( "MemberID", "FirstName", "AlternateFirstName","LastName",
"Address", "City","StateOrProvince","PostalCode", "SecondaryAddress","SecondaryCity",
"SecondaryStateProvince", "SecondaryPostalCode", "MemberType", "MemberThruDate", 
"ALLeagueMember","DateJoined", "LastRenewalDate", "NwsltrByMail", "PrimaryID",
 "DateLeft", "ReasonLeft", "NotActive", "Family", "CommentsLeft", "Reflect", "Address2",
 "AltAddress2", "OfficerTitle", "EmailAddress")
 SELECT  * FROM "MemberMaster" WHERE "NotActive"  = TRUE;

The only way (at least at this point) for the insert to work was with individual fields. Tried with DEFAULT for the auto-increment ID field but kept getting errors. I believe this is also part of the problem with your original statement.

[Ratslinger]

BTW - Where a field is located in a table makes no difference. Many people complain and I believe with some obscure process you can re-position the field.

[JoeCastor]

Thank you very much. I like your first solution. Now I can just rename a few of the queries and reports for MemberMaster and only change the table name inside. I ran it and it failed with an error “table RetiredMembers doesn’t exist,” so I created a dummy table, reran the code and it Worked great. Now to finish this retired member job; flag PhoneNumbers & PaymentHistory tables from/if MemberMaster is inactive and create tables using above. Don’t understand how to do it yet. Again, thank you

[JoeCastor]

When trying to run your second option, I get the following error:1: user lacks privilege or object not found: MemberID

[Ratslinger]

The first solution has four statements in it. The first being to drop (delete) the existing table. If starting out without a table this first statement is not needed. That is why you got the ‘doesn’t exist’ error. There was no need to create a dummy table, just eliminate the Drop statement.

In the second error, that is usually caused because of a mismatch in field names. I can duplicate the error by renaming ‘MemberID’ to MembrID’ for example.

[Ratslinger]

FYI - If running the first set (of four) statements in SQL Workbench/J, a message would have appeared stating the table didn’t exist and asking to ignore the error (thus continuing the process). An answer of ‘Ignore this error’ completes the remaining statements again eliminating the need to create the dummy table.

[Ratslinger]

Don’t know if you are still having a problem with deletes. I have no problem using this statement:

DELETE FROM "RetiredMembers" WHERE "MemberID" = 0

[JoeCastor]

Using the TOOLS > SQL from the Base main page (still not up to speed on Workbench/J yet), When running the first set without the Drop, I get the error 1: user lacks privilege or object not found: PUBLIC.RetiredMembers That’s why I had to create the initial dummy table. I recopied an reran the second set and all ran successfully. Question? Where or how can these be stored so they can be run in the future? Are the created as a fake query?

[Ratslinger]

When running from Tools->SQL you must run each statement separately or you will get that error. Just ran test & individual statements ran without problem.

As far as storage of these statements, there is none in Base. You either save them in a text file, create a DB to save them in or, as I prefer, save them in Workbench/J (can create different work spaces for various reasons).