|
hi there Little SQL annoyance in SA12.0.1.x. Reduced to clarify the problem. Consider this: 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? |
|
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
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: 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... |
