Vijayalakshmi, The situation you are dealing with here is where we have to make a call with what to do. There are syntax issues and requirements of SQL and we are trying to help the right thing happen.
Most commonly, the substitution into a command is of a value. You can substitute entire clauses like you have done here, but most commonly, it is a value. So, you have a case like Select name from table where column = '$value to substitute$' Now, if the value to substitute contains a quote, if we left it as it is, it would generate invalid syntax. So, we know that most of the time it is a value substituion so we double the quote to satisfy the rule of SQL for doubling a quote within a value to represent a quote in that value. Now, we know that there are exceptions to this idea of just substituting a value. Sometimes you are creating the entire SQL command or a portion of it dynamically. So, if the ENTIRE value of the SQL command is a field reference, we assume that it is your command, it cannot be a value because it is the entire thing, and we don't do any assistance with the data. So, if you made a minor change to your logic. 1) Build the Where clause as you do today (I am going to say you build it in a field called SQLTemp) 2) Add an action that is a set fields to the field SQLTemp that adds the rest of the command SQLTemp = "Select col1, col2 from table1 where " + $SQL Temp$ 3) In your action where there is the SQL command, just specify $SQLTemp$ as the value This means that you have built the entire SQL command and are just telling us to run the SQL you have constructed and we will not massage it in any way. This should be a minor adjustment to your existing logic -- one extra action and a small change to one action -- and you will get exactly what you are looking for. I hope this helps, Doug Mueller ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Vijayalakshmi Paulraj Sent: Thursday, April 02, 2009 10:54 PM To: [email protected] Subject: External Condition in Active Link Set Field - SQL Action ** All, Our requirement is to arrive at a result set based on the Where qualification which is externally built. We used the Active Link -> Set Field Action Using SQL Query as below: Select col1, col2 from table1 where $Where Qualification$ The Where Qualification is externally built at Run time. For E.g., col3 = '45' or col3 = '56' When Remedy parses this SQL, it adds one more Single Quotes for the values as shown here: - col3 = ' '45' ' or col3 = ' '56' ' So the Query becomes, Select col1, col2 from table1 where col3 = ' '45' ' or col3 = ' '56' ' Oracle throws error *** ERROR *** ORA-00933: SQL command not properly ended Is there any way to drive this Where Qualification externally with proper syntaxing? Your help much appreciated. Thanks & Regards, Vijayalakshmi Paulraj --------------------------------------------------------------------------------------------- This message, including any attachments, contains confidential information intended for a specific individual and purpose, and is intended for the addressee only. Any unauthorized disclosure, use, dissemination, copying, or distribution of this message or any of its attachments or the information contained in this e-mail, or the taking of any action based on it, is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail and delete this message. --------------------------------------------------------------------------------------------- __Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"
