Agreed. The Dev Studio (and Admin tool before it) is not in the business of parsing complex SQL, so they punt on the issue and just count commas (and add one). They trust that you, the developer who wrote the SQL, will know how many valid results there actually are.
And now on to the subject of using the DB to perform this (kind of) operation...it's very expensive! I know that sometimes it's the only way to get something done, but should be avoided wherever possible. I sure hope you're not doing this from active link logic, I cringe at the number of times this would need to hit the wire (6). -charlie On Thu, Sep 4, 2014 at 5:49 AM, Grooms, Frederick W < [email protected]> wrote: > ** > > I’m not sure if it would be the API or just the code that is used in > Developer Studio (Possibly the same code that was used in the Admin tool). > > > > When you do an SQL set fields DS does not actually run it to determine the > number of columns in the output so it just makes the best guess it can on > how many there will be. I have seen this same scenario since at least > version 5 with the Admin Tool. > > > > Fred > > > > *From:* Action Request System discussion list(ARSList) [mailto: > [email protected]] *On Behalf Of *Joe D'Souza > *Sent:* Thursday, September 04, 2014 6:10 AM > *To:* [email protected] > *Subject:* *Ancient* bug with Set Field from a SQL transaction still > present... > > > > ** > > I had found this bug a really long time ago on version 7.1. > > > > It still exists. > > > > I was trying Set Fields through SQL to capitalize the first letter of a > word after the 6th character so this should have returned only $1$ as a > possible choice of results. > > > > select substr('$FieldName$', 1, 6) || initcap(substr('$FieldName$', 7)) > from dual > > > > However, I get $1$, $2$, $3$ and $4$ to choose from. It appears like the > ARSystem API looks at every comma between select and from and thinks that > there are that many column values to return, which would explain the 4 > dollar columns in the result list to choose from. > > > > Fortunately using $1$ (which should have been the only choice to choose > from) gets me the right results. > > > > Has anybody else reported this to BMC Support? I had reported this years > ago but it appears like it has not yet got fixed. Fortunately, back then > too, I needed just the first column so I did not have any real problem. > > > > I have not tested to see if I need more than one column, if the $ > variables would return the right results. Also I have not tested what $2$, > $3$ and $4$ holds. Probably nulls. Or may return an error at run time if > you select them. > > > > Cheers > > > > Joe > > > > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
