SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[9.0.162 Pro] $OBJECT$ Macro adding last selected column?

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[9.0.162 Pro] $OBJECT$ Macro adding last selected column?
Author Message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post [9.0.162 Pro] $OBJECT$ Macro adding last selected column? Reply with quote
the cfetch snippet:

Code:
DECLARE @$COLUMNS+TYPES$

DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT $COLUMNS$
FROM $OBJECT$

OPEN my_cursor|

FETCH FROM my_cursor INTO @$COLUMNS$

WHILE @@FETCH_STATUS = 0
BEGIN
   /*{ ... Cursor logic here ... }*/

   FETCH FROM my_cursor INTO @$COLUMNS$
END

CLOSE my_cursor
DEALLOCATE my_cursor


In this case, it lets you select columns and so on right?

Well the table name ends up with the LAST column selected even if I use $OBJECT(table)$

http://screencast.com/t/Hg7p4Y7ryPX1
Tue Oct 11, 2016 6:24 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
This is not the intended usage of the cftetch snippet. The snippet expects that you choose an object name., for example, a table name and not a subject of its column. Column selection returns results incompatible with the snippet code. If you want to it to work with a subset of columns, you will need to modify the snippet code.
Wed Oct 12, 2016 2:02 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
That's a piece of cake, all you have to do is to replace $OBJECT$ with an $$...$$ that analyzes the passed code for object given by $OBJECT$ and returns the actual object name accordingly. Also, it doesn't hurt to filter out stuff with $OBJECT(xyz)$ that wouldn't work as cursor material anyway.

Code:

DECLARE @$COLUMNS+TYPES$

DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT $COLUMNS$
FROM $$
DECLARE @object SYSNAME = '$OBJECT(table, view, mview, tblFunc)$';
SELECT
    CASE
        WHEN PARSENAME(@object ,3) != '' THEN PARSENAME(@object ,3) + '.' + PARSENAME(@object ,2)       
        ELSE PARSENAME(@object ,2) + '.' + PARSENAME(@object ,1)
    END;
$$

OPEN my_cursor|

FETCH FROM my_cursor INTO @$COLUMNS$

WHILE @@FETCH_STATUS = 0
BEGIN
   /*{ ... Cursor logic here ... }*/

   FETCH FROM my_cursor INTO @$COLUMNS$
END

CLOSE my_cursor
DEALLOCATE my_cursor


EDIT:Or a shorter version:
Code:

DECLARE @$COLUMNS+TYPES$

DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT $COLUMNS$
FROM $OBJECT(ins_schema, table, view, mview, tblFunc)$.$OBJECT(ins_object, table, view, mview, tblFunc)$

OPEN my_cursor|

FETCH FROM my_cursor INTO @$COLUMNS$

WHILE @@FETCH_STATUS = 0
BEGIN
   /*{ ... Cursor logic here ... }*/

   FETCH FROM my_cursor INTO @$COLUMNS$
END

CLOSE my_cursor
DEALLOCATE my_cursor

Wed Oct 12, 2016 3:35 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post Reply with quote
SysOp wrote:
This is not the intended usage of the cftetch snippet. The snippet expects that you choose an object name., for example, a table name and not a subject of its column. Column selection returns results incompatible with the snippet code. If you want to it to work with a subset of columns, you will need to modify the snippet code.


Shouldn't there be a way to tell that? I'm never writing cursors that use a whole set of columns. In fact I'm relatively sure nobody is. If it's not meant to use then maybe there should be a way to tell the snippet or object macro to disallow drilling down into the column picker. $Object(table)$ seems to get close as you can no longer pick out columns but you still can drill down.
Wed Oct 12, 2016 8:19 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.