SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
COLUMNS macro question

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
COLUMNS macro question
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post COLUMNS macro question Reply with quote
I created a snippet that should join two tables
Code:

SELECT
    c.$COLUMNS(vertical)$
FROM
    $OBJECT(ins_qualname ,TABLE)$  AS c
INNER JOIN
    CHANGETABLE(CHANGES $OBJECT(ins_qualname ,TABLE)$ ,0) AS ct
    ON  ct.$COLUMNS(vertical,keys)$ = c.$COLUMNS(vertical,keys)$
;

Now that generates a code (see example below) that has commas as separator for columns and that is good for the SELECT part but does not work for JOIN part, which requires logical operators instead of commas.
Code:

SELECT
    vonal_id
   ,varos_id
   ,vonal_nev
   ,aktiv_e
   ,datum_tol
   ,datum_ig
FROM
    [DATA].dbo.vonal AS c
INNER JOIN
    CHANGETABLE(CHANGES [DATA].dbo.vonal ,0) AS ct
    ON  ct.vonal_id = c.vonal_id
   ,ct.datum_tol = c.datum_tol
;


I remember asking this once if there's a way to change commas to ANDs or ORs but I don't recall what it was and couldn't find a working example. Could you lend some help, please?
Mon Apr 18, 2016 3:25 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
I believe the last line should be changed to

Code:
"AND ct."$COLUMNS(vertical,keys)$" = c."$COLUMNS(vertical,keys)$


Tip. When you get a chance, please take a look at snippets on the Code Generator tab in the Options, specifically the code of Save template.
Mon Apr 18, 2016 12:33 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post Reply with quote
It works, thank you very much. I fiddled around with the double quotes for a while but couldn't find a working solution. I guess there's some pretty complicated logic about how this works behind the scene. Thanks for the tip as well. Might come in handy.
Tue Apr 19, 2016 4:17 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.