Guys, You don't need anything of the sort. Just issue your select and use the $1$ etc as you would if the Admin tool had figured out the number of columns correctly.
If your select returns two columns, you can use $1$ and $2$ and the values will be right. The admin tool does nothing with your select except pass it to the server. The server does the substitutions and passes it to the DB. The DB decides how many columns to return. I am sure if you use $3$ on a two column select the server will generate an error. Cheers Ben www.softwaretoolhouse.com -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of David Morgan Sent: September 14, 2009 12:43 AM To: arslist@ARSLIST.ORG Subject: Re: Setting fields from a direct SQL... Hi Joe, all Whilst this may be a work-around it may be quicker than waiting for a fix... Can't you write a SQL function/procedure/package which would perform all the jiggery-pokery within the SQL body before returning just the column you wanted? Regards Dave Morgan -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe D'Souza Sent: 13 September 2009 22:09 To: arslist@ARSLIST.ORG Subject: Re: Setting fields from a direct SQL... This is a multi-part message in MIME format. ------=_NextPart_000_0000_01CA3494.F4DCDDC0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I'm thinking of doing that because if you need to set values from two columns, and both use these kind of functions, then while you may get $1$ correctly, I'm wondering what it would set for $2$. I might test that on Monday before opening a ticket. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of LJ Longwing Sent: Friday, September 11, 2009 7:44 PM To: arslist@ARSLIST.ORG Subject: Re: Setting fields from a direct SQL... ** Joe, I haven't tested, but your hypothesis sounds correct. I would open a defect with BMC regarding it :) ------------------------------------------------------------------------ ---- ---- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza Sent: Friday, September 11, 2009 4:47 PM To: arslist@ARSLIST.ORG Subject: Setting fields from a direct SQL... ** Something that I had seen in the past when you use a nested function to return a value or values where the developer client thinks it has been asked to query for more than the requested number of columns.. I had to use one that returns a single column but the ARS somehow thinks its more than a column.. fortunately for me using $1$ works as it returns the value I want.. why does my dev studio client see 4 columns returned by: select '$ztmpRequestID$'||substr(lpad(nextid,$ztmpRequestIDLength$,'0'),length( '$zt mpRequestID$')+1) from arschema where schemaid in (select schemaid from arschema where name = '$SCHEMA$') It looks like everytime it sees a comma, it assumes that it is another column queries whereas that comma could be used in a function like lpad or length like I did above.. The above is a simple select statement where $ztmpRequestID$ holds the default value (prefix) of a Request ID returned from: select defaultvalue from field where schemaid in (select schemaid from arschema where name = '$SCHEMA$') and fieldid = 1 It uses that prefix and concatinates it to the left padded value of nextid after removing the first 3 characters so as to construct the Request ID as it may appear after the submit transaction. Setting my field to $1$ on that set field operation gives me the correct value - but why do I see $2$, $3$ and $4$ as possible values I can set to my result? I haven't really checked to see what values these hold but I'm willing to bet they hold nothing (NULL). Joe ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" ------=_NextPart_000_0000_01CA3494.F4DCDDC0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable ** <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Dus-ascii" = http-equiv=3DContent-Type> <META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18812"></HEAD> <BODY> <DIV><SPAN class=3D984390421-13092009><FONT size=3D2>I'm thinking of = doing that=20 because if you need to set values from two columns, and both use these = kind of=20 functions, then while you may get $1$ correctly, I'm wondering what it = would set=20 for $2$. I might test that on Monday before opening a=20 ticket.</FONT></SPAN></DIV> <DIV><SPAN class=3D984390421-13092009><FONT = size=3D2></FONT></SPAN> </DIV> <DIV><SPAN class=3D984390421-13092009><FONT = size=3D2>Joe</FONT></SPAN></DIV> <DIV><SPAN class=3D984390421-13092009><FONT = size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr class=3DOutlookMessageHeader align=3Dleft><FONT size=3D2=20 face=3DTahoma>-----Original Message-----<BR><B>From:</B> Action Request = System=20 discussion list(ARSList) [mailto:arsl...@arslist.org]<B>On Behalf Of = </B>LJ=20 Longwing<BR><B>Sent:</B> Friday, September 11, 2009 7:44 = PM<BR><B>To:</B>=20 arslist@ARSLIST.ORG<BR><B>Subject:</B> Re: Setting fields from a direct=20 SQL...<BR><BR></FONT></DIV> <DIV>**=20 <META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18812"></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D111034423-11092009><FONT = color=3D#0000ff=20 size=3D2 face=3DArial>Joe,</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D111034423-11092009><FONT = color=3D#0000ff=20 size=3D2 face=3DArial>I haven't tested, but your hypothesis sounds = correct. I=20 would open a defect with BMC regarding it :)</FONT></SPAN></DIV> <DIV><BR></DIV> <DIV dir=3Dltr lang=3Den-us class=3DOutlookMessageHeader align=3Dleft> <HR tabIndex=3D-1> <FONT size=3D2 face=3DTahoma><B>From:</B> Action Request System = discussion=20 list(ARSList) [mailto:arsl...@arslist.org] <B>On Behalf Of </B>Joe=20 DeSouza<BR><B>Sent:</B> Friday, September 11, 2009 4:47 PM<BR><B>To:</B> = arslist@ARSLIST.ORG<BR><B>Subject:</B> Setting fields from a direct=20 SQL...<BR></FONT><BR></DIV> <DIV></DIV> <DIV>**=20 <STYLE type=3Dtext/css>DIV { MARGIN: 0px } </STYLE> </DIV> <DIV=20 style=3D"FONT-FAMILY: times new roman, new york, times, serif; COLOR: = #000000; FONT-SIZE: 10pt"> <DIV></DIV> <DIV>Something that I had seen in the past when you use a nested = function=20 to return a value or values where the developer client thinks it has = been asked=20 to query for more than the requested number of columns..<BR><SPAN=20 class=3D984390421-13092009><FONT color=3D#0000ff = face=3DArial></FONT></SPAN></DIV> <DIV>I had to use one that returns a single column but the ARS somehow = thinks=20 its more than a column.. fortunately for me using $1$ works as it = returns the=20 value I want.. why does my dev studio client see 4 columns returned = by:</DIV><FONT color=3D#0000ff face=3DArial></FONT><FONT color=3D#0000ff = face=3DArial></FONT> <DIV><BR>select=20 <STRONG>'$ztmpRequestID$'||substr(lpad(nextid,$ztmpRequestIDLength$,'0') ,= length('$ztmpRequestID$')+1)</STRONG>=20 from arschema where schemaid in (select schemaid from arschema where = name =3D=20 '$SCHEMA$')<BR><SPAN class=3D984390421-13092009><FONT color=3D#0000ff=20 face=3DArial></FONT></SPAN></DIV> <DIV>It looks like everytime it sees a comma, it assumes that it is = another=20 column queries whereas that comma could be used in a function like lpad = or=20 length like I did above..<BR><SPAN class=3D984390421-13092009><FONT = color=3D#0000ff=20 face=3DArial></FONT></SPAN></DIV> <DIV>The above is a simple select statement where $ztmpRequestID$ holds = the=20 default value (prefix) of a Request ID returned from:<BR><SPAN=20 class=3D984390421-13092009><FONT color=3D#0000ff = face=3DArial></FONT></SPAN></DIV> <DIV>select defaultvalue from field where schemaid in (select schemaid = from=20 arschema where name =3D '$SCHEMA$') and fieldid =3D 1<BR><SPAN=20 class=3D984390421-13092009><FONT color=3D#0000ff = face=3DArial></FONT></SPAN></DIV> <DIV>It uses that prefix and concatinates it to the left padded value of = nextid=20 after removing the first 3 characters so as to construct the Request ID = as it=20 may appear after the submit transaction. Setting my field to $1$ on that = set=20 field operation gives me the correct value - but why do I see $2$, $3$ = and $4$=20 as possible values I can set to my result?<BR><SPAN=20 class=3D984390421-13092009><FONT color=3D#0000ff = face=3DArial></FONT></SPAN></DIV> <DIV>I haven't really checked to see what values these hold but I'm = willing to=20 bet they hold nothing (NULL).<BR><SPAN class=3D984390421-13092009><FONT=20 color=3D#0000ff face=3DArial></FONT></SPAN></DIV> <DIV>Joe</DIV></DIV></BODY></HTML> _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ ------=_NextPart_000_0000_01CA3494.F4DCDDC0-- ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
