SAP SQL Anywhere

Question

I need to be able to select into a temporary table (which is really a copy of the base table, not really 'temporary'). The problem I'm running into is that the base table uses GUIDs for the Primary Key, and upon attempting to run a:

SELECT * INTO HOLD_TABLE FROM ORIGINAL_TABLE WHERE CRITERIA IS NULL;

I keep getting a Syntax Error Near '(' on Line 1)

The only thing I can come up with is because the table has a GUID for the Primary Key.

Has anybody ran into this before, or have any tips on circumventing the issue?

Thanks, Calvin

EDIT:

Table Structure:

CREATE TABLE "DBA"."Consumer_Plan_Section_Services" (
    "CP_Assessment_Service_ID" uniqueidentifier NOT NULL,
    "Consumer_Plan_ID" uniqueidentifier NOT NULL,
    "Template_Section_ID" uniqueidentifier NOT NULL,
    "Service_Needed" long varchar NULL,
    "Service_ID" double NULL,
    "Service_Type_ID" double NULL,
    "Frequency" integer NULL,
    "Duration" char(1) NULL,
    "Vendor_ID" double NULL,
    "Type_of_Provider" varchar(10) NULL,
    "Type_ID" double NULL,
    "User_Id" varchar(30) NULL,
    "Last_Update" timestamp NULL,
    "Remote_Location" integer NULL,
    "Funding_Source_ID" double NULL,
    "Effective_Start_Date" date NULL,
    "Effective_End_Date" date NULL,
    "Staff_Training_Required" char(1) NULL DEFAULT 'N',
    "Service_Levels" varchar(256) NULL,
    "Order_Number" integer NOT NULL DEFAULT 0,
    "Freq_Duration_Desc" varchar(100) NULL,
    PRIMARY KEY ( "CP_Assessment_Service_ID" ASC )
);

Select Statement:

SELECT * INTO Invalid_CP_Section_Services FROM Consumer_Plan_Section_Services WHERE Vendor_ID IS NULL;

Accepted Answer

5

The problem has been found and will be fixed in 10.0.1.4088. Later versions (11.*, etc.) already contained the fix. The problem did not affect SELECT ... INTO when creating temporary tables, and happened when the source query's SELECT list contained UNIQUEIDENTIFIER columns.

permanent link

answered 22 Jun '10, 13:45

Bruce Hay
2.6k●1●5●10
accept rate: 48%

edited 25 Jun '10, 13:09

I need the result table to be persistent.

(22 Jun '10, 14:30) Calvin Allen

@Calvin: Would it be sufficient to create the table Invalid_CP_Section_Services explicitely (i.e. by CREATE TABLE) and then use "INSERT Invalid_CP_Section_Services SELECT * FROM Invalid_CP_Section_Services" ?

(22 Jun '10, 18:51) Volker Barth

@Volker: That is an option I considered, and will go with if there is no alternative.

(22 Jun '10, 20:31) Calvin Allen

For the record, SELECT ... INTO can be used to create permanent tables starting with version 11. See the conditions in the documentation (mainly that the SELECT list must contain more than one item, or if * is used then the table name must be given as owner.name). Doesn't help you with 10.0.1 though.

(23 Jun '10, 13:42) Bruce Hay

@Bruce - The statements work as expected in 10.0.1 unless the table has a GUID primary key.

(23 Jun '10, 22:24) Calvin Allen

3

Okay, the problem has been found and will be fixed in 10.0.1.4088. Later versions (11.*, etc.) already contained the fix. The problem did not affect SELECT ... INTO when creating temporary tables. The problem happened when the source query contained UNIQUEIDENTIFIER columns, not just as the primary key. My earlier statement about SELECT ... INTO creating permanent tables starting in 11 was wrong; it's supported and documented in 10 as well.

(24 Jun '10, 13:41) Bruce Hay

Comment Text Removed

Awesome! Thank you! If you revise your answer for that, I'll mark it as accepted so you can get some brownie points!

(24 Jun '10, 14:47) Calvin Allen

@Calvin: A bounty for a bugfix - that's a fair deal, methinks:)

(24 Jun '10, 15:57) Volker Barth

@Calvin: Why don't you modify Bruce's answer for him, and give him the bounty?

(25 Jun '10, 10:37) Breck Carter

@Breck - I evidently don't have enough rep to edit answers.

(25 Jun '10, 11:07) Calvin Allen

@Calvin: And now even less:) - @Breck: FYI: I can't edit someone else's answers, either.

(25 Jun '10, 14:27) Volker Barth

More comments hidden

showing 5 of 11 show all flat view

Answer 2

0

Can't you use

INSERT INTO Invalid_CP_Section_Services  
SELECT *  
FROM Consumer_Plan_Section_Services  
WHERE Vendor_ID IS NULL;

permanent link

answered 23 Jun '10, 10:23

Thomas Dueme...
2.7k●29●39●65
accept rate: 17%

The table does not exist prior to running these statements. The idea is to have it created on the fly by our upgrade script (which is ran by our IT staff on our clients servers), and then persist if someone needs to review the data.

(23 Jun '10, 17:47) Calvin Allen

flat view

SAP SQL Anywhere

Selecting Into Temp Table From Table With GUIDs

Follow this question

Related questions