SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1.256 Pro] - $OBJECT$ + $COLUMN$

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1.256 Pro] - $OBJECT$ + $COLUMN$
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1401

Post [SA 9.1.256 Pro] - $OBJECT$ + $COLUMN$ Reply with quote
Given is the following table:
Code:

CREATE TABLE ext.foldhelyek
(
   foldhely_id INT NOT NULL
   ,foldhely_nev VARCHAR(50) NOT NULL
   ,hosszusag DECIMAL(11, 8) NULL
   ,szelesseg DECIMAL(11, 8) NULL
   ,torles_ideje DATETIME NULL
   ,id_arkategoria INT NULL
   ,CONSTRAINT pk_foldhelyek PRIMARY KEY CLUSTERED ( foldhely_id )
)
GO


and the following snippet:
Code:

UPDATE u
SET
    "u."$COLUMNS(vertical,updatable,nokeys)$" = "
   -- the key column(s) $COLUMNS(vertical,keys)$
FROM   
   $OBJECT(ins_schema, table, view)$.$OBJECT(ins_object, table, view)$ AS u|
WHERE   1 = 1
    AND "AND u."$COLUMNS(vertical,keys)$" = ";


The snippet is able to select either a table or one or more columns from a table. It works almost perfectly. Selecting a table (ext.foldhelyek in this case) will produce the following code:
Code:

SET
    u.foldhely_nev = ,
    u.hosszusag = ,
    u.szelesseg = ,
    u.torles_ideje = ,
    u.id_arkategoria = 
    -- the key column(s) foldhely_id
FROM   
    ext.foldhelyek AS u
WHERE   1 = 1
    AND  u.foldhely_id = ;

which is exactly what's expected (well, more or less at least, for the snippet has Code Formatting Style set, which is not applied).

Now selecting one (or more) columns produces a similar result. Picking only non-primary key columns hosszusag and szelesseg:
Code:

UPDATE u
SET
    u.hosszusag = ,
    u.szelesseg = 
    -- the key column(s) /* Cannot find key columns in foldhelyek */
FROM   
    ext.foldhelyek AS u
WHERE   1 = 1
    AND /* Cannot find key columns in foldhelyek */;


and picking primary key column foldhely_id besides the already mentioned two columns:
Code:

UPDATE u
SET
    u.hosszusag = ,
    u.szelesseg = 
    -- the key column(s) foldhely_id
FROM   
    ext.foldhelyek AS u
WHERE   1 = 1
    AND  u.foldhely_id = ;


First SA failed to identify the primary key columns for the chosen object (ext.foldhelyek), but in the second case, when the primary key column was picked as well, SA successfully ascertained the primary key.

Now you might say that this mixture of $COLUMNS(...)$ and $OBJECT(...)$ was not the intended usage in the first place, but it works pretty well (except the trifle of not retrieving the primary key). Could you fix this small glitch, please?
Fri Mar 03, 2017 8:28 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.