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:[email protected]] On Behalf Of Joe D'Souza Sent: 13 September 2009 22:09 To: [email protected] 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:[email protected]]on Behalf Of LJ Longwing Sent: Friday, September 11, 2009 7:44 PM To: [email protected] 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:[email protected]] On Behalf Of Joe DeSouza Sent: Friday, September 11, 2009 4:47 PM To: [email protected] 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:[email protected] 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:[email protected]]<B>On Behalf Of = </B>LJ=20 Longwing<BR><B>Sent:</B> Friday, September 11, 2009 7:44 = PM<BR><B>To:</B>=20 [email protected]<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:[email protected]] <B>On Behalf Of </B>Joe=20 DeSouza<BR><B>Sent:</B> Friday, September 11, 2009 4:47 PM<BR><B>To:</B> = [email protected]<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: [email protected] ARSlist: "Where the Answers Are"_ ------=_NextPart_000_0000_01CA3494.F4DCDDC0-- _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
