Hi Ben, I bet you are right there. Since it does no validation for the accuracy of the syntax, etc., it is probably just looking at the selected column at face value, and assumes every comma between the SELECT and the FROM clause indicates a column separator...
As a result of this sort of a behavior, I did have a problem once though a very long time ago (early 7.1 Patch 5 days I think) on version 7.1 where I used a nested REPLACE for replacing any ALPHA character with a null string. Thankfully someone (I think my DBA) suggested I should try TRANSLATE instead of REPLACE which didn't need a nested function, so that got me through.. I do not remember what was the error I was getting but I was just unable to use any of the $ results - not even $1$. The same statement using REPLACE ran just fine on the SQL client SQLPlus. Joe ________________________________ From: Ben Chernys <[email protected]> To: [email protected] Sent: Monday, September 14, 2009 4:39:37 AM Subject: Re: Setting fields from a direct SQL... 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:[email protected]] On Behalf Of David Morgan Sent: September 14, 2009 12:43 AM To: [email protected] 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:[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... 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('$ztmpRequestID$')+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"
