It does make sense - it just feels (if you are not already aware that is the
way it works) that there is something wrong somewhere. I felt that about 5
years ago when I saw that on 7.1 and was wondering why it was showing me
multiple choices  when I had selected just one column to return. It took me
a few to figure it must have been looking at the number of commas to
generate that menu.

 

I do not remember ever having a problem with the SQL set field functionality
not working correctly otherwise. I preferred to use it over creating a view
form to set fields from as it less involved timewise.

 

Once however if I recall correctly, I did run out of characters as the
column that saves the SQL statement did not accommodate my SQL statement.
I'm pretty sure it was limited to the number of characters that could be
used in the SQL statement.

 

Joe

 

  _____  

From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Mueller, Doug
Sent: Thursday, September 04, 2014 2:38 PM
To: [email protected]
Subject: Re: *Ancient* bug with Set Field from a SQL transaction still
present...

 

Everyone,

 

Charlie is right about the functionality.

 

There is actually no way to determine the exact number of rows returned.
What if the SQL is a stored

procedure or uses an * or whatever it does.  The only thing the menu at the
end is to try and help you by

giving a menu of field references like we do with non-direct SQL cases and
we give you some aids.

 

That is all the menu is.

 

You can enter ANY value you want.  If that is a column returned, we will use
that value.  If not, it will be a

NULL value.  Either way, the operation will be performed with the
appropriate substitution.

 

 

So, this is not a bug, but just the way things work.  The only alternative
would be to give you no menu help

and require that you type the substitution in correctly.  That way there
would never be a mismatch of the

number of items and the substitution parameters.  But, we feel that giving
you the most likely set of

choices and at least you can see the right syntax so you could drop and then
just change the number was

more useful than just abandoning you to your own devices.

 

I hope this explains things.

 

Doug Mueller

 

From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Charlie Lotridge
Sent: Thursday, September 04, 2014 11:20 AM
To: [email protected]
Subject: Re: *Ancient* bug with Set Field from a SQL transaction still
present...

 

** 

FYI this "comma counting" only affects what appears in the list of "field"
choices.  There's nothing stopping you from hand typing, say, $10$ or $100$
into a Set Fields value, even if the drop-down shows only (say) 5 choices.
Of course, your SQL had then better have at least that many columns of data
for this to make sense.

 

An example reason for this is a "SELECT * FROM..." sql statement.  Without a
very complex analysis of the DB's data dictionary, or somehow trying out the
SQL, there's no way for the Dev Studio to know how many columns might be
returned.  So, the value expression parser needs to allow you to include
whatever $<integer>$ values you wish to include.

 

For fun I just tried including a $200$ in a value.  Worked fine.  And,
without any SQL, the list of field choices seems to default to showing $1$
through $10$.

 

-c

 

On Thu, Sep 4, 2014 at 10:43 AM, Joe D'Souza <[email protected]> wrote:

** 

I saw it in 7.1 when I did a nested SQL statement that had many commas and
had noticed that it was number of commas + 1 that the Admin Tool back then
estimated the $ variables. Same as now. So that logic fails when you use
functions that require more than one parameters separated by a comma.

 

I did hear that was the case pre-7.1 too..

 

It must be tricky when you are returning more than 1 column yeah?
Fortunately for me both back then as well as now I needed only a single
column returned by the SQL so the only value I had any interest in was $1$.

 

Joe

 

  _____  

From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Grooms, Frederick W
Sent: Thursday, September 04, 2014 8:49 AM
To: [email protected]
Subject: Re: *Ancient* bug with Set Field from a SQL transaction still
present...

 

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_ _ARSlist:
"Where the Answers Are" and have been for 20 years_

 

_ARSlist: "Where the Answers Are" and have been for 20 years_ 

_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"

Reply via email to