hi there

Little SQL annoyance in SA12.0.1.x. Reduced to clarify the problem.

Consider this:

insert into PROGRAMS 
  with auto name
select PROGRAM_GUID, 
       PROGRAM_VERSION + 1 as PROGRAM_VERSION,
       .....  
  from PROGRAMS 
 where PROGRAM_GUID = '....' 
   and PROGRAM_VERSION = 2

I'd like to use this to copy a row in the same table. The 2 fields shown are part of the PK. This does not work because the aliasing of PROGRAM_VERSION + 1 as PROGRAM_VERSION generate a where clause always saying x = x+1 whic of course is never true, so the statement fails. I tried alias names but that breaks AUTO NAME ... unfortunately. And there is no option DISCARD SUPERFLUOUS ..

Any idea how I can do it nevertheless elegantly in one statement?

asked 13 Jun '14, 10:44

Tinu8805's gravatar image

Tinu8805
41226
accept rate: 0%

edited 13 Jun '14, 10:46


Try using "and PROGRAMS.PROGRAM_VERSION = 2".

CREATE TABLE PROGRAMS (
   PROGRAM_GUID     UNIQUEIDENTIFIER NOT NULL,
   PROGRAM_VERSION  INTEGER NOT NULL,
   PRIMARY KEY ( PROGRAM_GUID, PROGRAM_VERSION ) );

INSERT PROGRAMS VALUES ( 0x15ff08574671425fa02aab2cc84a25e5, 2 );

insert into PROGRAMS 
  with auto name
select PROGRAM_GUID, 
       PROGRAM_VERSION + 1 as PROGRAM_VERSION
  from PROGRAMS 
 where PROGRAM_GUID = 0x15ff08574671425fa02aab2cc84a25e5 
   and PROGRAMS.PROGRAM_VERSION = 2;

SELECT * FROM PROGRAMS ORDER BY PROGRAM_GUID, PROGRAM_VERSION;

PROGRAM_GUID,PROGRAM_VERSION
0x15ff08574671425fa02aab2cc84a25e5,2
0x15ff08574671425fa02aab2cc84a25e5,3


permanent link

answered 13 Jun '14, 14:08

Breck%20Carter's gravatar image

Breck Carter
27.4k425586838
accept rate: 21%

Hi Breck

Well, if the Chef (german for Boss) tells me that it's that simple, it certainly must be ...

Arrg, I seem to got rusty during the last few years ... never had to work with SA for years now ... you catch me embarrassed ... :-)

Regards, Martin

(13 Jun '14, 14:14) Tinu8805
Replies hidden

The "scope of names in SQL Anywhere" is certainly not simple... it is far more intricate than in, say, application programming languages. AFAIK the scope rules aren't documented anywhere, but such documentation would probably be impossible to remember... it is sufficient to remember that the rules ARE complex, and when faced with a situation like yours, "try things"... like qualifying with the table name if you don't like the alias name.

Personally, I was only 50% sure PROGRAMS.PROGRAM_VERSION would work :)

If anyone thinks the rules ARE NOT complex, consider this: "PROGRAM_VERSION + 1 as PROGRAM_VERSION" refers to two different PROGRAM_VERSION names, and the first one is the same as PROGRAMS.PROGRAM_VERSION without the need for the table name. HOWEVER, elsewhere (like line 2 below) omitting the table name gives the alias name...

select PROGRAM_VERSION + 1 as PROGRAM_VERSION,
       PROGRAM_VERSION,
       PROGRAMS.PROGRAM_VERSION
  from PROGRAMS 
 where PROGRAM_GUID = 0x15ff08574671425fa02aab2cc84a25e5 
   and PROGRAMS.PROGRAM_VERSION = 2;

PROGRAM_VERSION,PROGRAM_VERSION,PROGRAM_VERSION
3,3,2

Local variables and FOR loop alias names add further complexity, as do CREATE VARIABLE names, nested BEGIN blocks and parameter names.

This complexity is THE ONLY REASON that I append @ to all variable names, including FOR loop alias names which are really local variables IMO... over the years that convention has avoided many problems (at least, I think it has :)

(13 Jun '14, 15:10) Breck Carter

While I would prefer Breck's solution here, I'd like to point to a different approach:

You can use a derived table when you need an alias in the select list and do not want to use that alias in the original query because of scope issues/irritations, such as:

insert into PROGRAMS 
  with auto name
select PROGRAM_GUID, 
       PROGRAM_VERSION + 1 as PROGRAM_VERSION
  from
(select PROGRAM_GUID, 
        PROGRAM_VERSION
   from PROGRAMS 
  where PROGRAM_GUID = 0x15ff08574671425fa02aab2cc84a25e5 
    and PROGRAM_VERSION = 2) dt;

As you can see, the PROGRAM_VERSION column in the WHERE condition does not have to be qualified here as it is not already aliased in the inner query block...

permanent link

answered 16 Jun '14, 03:20

Volker%20Barth's gravatar image

Volker Barth
30.0k295449654
accept rate: 32%

edited 16 Jun '14, 04:01

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×23

question asked: 13 Jun '14, 10:44

question was seen: 330 times

last updated: 16 Jun '14, 04:01