Modify returned values from select statement
Bob wanted a SELECT to return a computed/masked column value depending on the connected user, without changing application code or replacing the table with a view. He found SELECT triggers only expose the OLD correlation values and SPL won't let you assign a new returned value. Art Kagel suggested a CASE expression in the query, or renaming the table and substituting a view (optionally calling a stored procedure), or having unprivileged users connect to a separate database of views. No trigger-based solution was found; Bob said he'd investigate the separate-database/view idea, so no confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is it possible to use a Select trigger to modify the returned value for a column being retrieved in a Select statement? A select trigger restricts you to reference only the old or pre value. In SPL, it does not allow an assignment of a new value to a referenced old value. Is there another method for modifying a specific column value in a select statement using a trigger or means other than a view? I am looking for a solution that does not involve using a view to replace the current table being referenced. Thanks, Bob
On 21/05/15 15:49, BOB KRUSE wrote: > Is it possible to use a Select trigger to modify the returned value for a > column being retrieved in a Select statement? A select trigger restricts you > to reference only the old or pre value. In SPL, it does not allow an > assignment of a new value to a referenced old value. > > Is there another method for modifying a specific column value in a select > statement using a trigger or means other than a view? I am looking for a > solution that does not involve using a view to replace the current table being > referenced. > > Thanks, > > Bob Is what you want to achieve to effectively have the select return a different value depending on some computation on the old value (which is how I am reading this), or is it to have the select update the table? Also, is this for the benefit of a client application, or is it all internal to the engine? -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Hi Marco, Yes, I would like to evaluate the user executing the query (based on the user id) and returned a different computed value. I would like to run a stored procedure to generate the new returned value so I was thinking of a select trigger. The trick is to substitute the computed value. The intent is not to update the table. Thanks, Bob -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Marco Greco Sent: Thursday, May 21, 2015 9:03 AM To: ids@iiug.org Subject: Re: Modify returned values from select statement [35156] On 21/05/15 15:49, BOB KRUSE wrote: > Is it possible to use a Select trigger to modify the returned value > for a column being retrieved in a Select statement? A select trigger > restricts you to reference only the old or pre value. In SPL, it does > not allow an assignment of a new value to a referenced old value. > > Is there another method for modifying a specific column value in a > select statement using a trigger or means other than a view? I am > looking for a solution that does not involve using a view to replace > the current table being > referenced. > > Thanks, > > Bob Is what you want to achieve to effectively have the select return a different value depending on some computation on the old value (which is how I am reading this), or is it to have the select update the table? Also, is this for the benefit of a client application, or is it all internal to the engine? -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
Add a CASE expression to the query itself? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 21, 2015 at 10:49 AM, BOB KRUSE < bob.kruse@galaxyhotelsystems.com> wrote: > Is it possible to use a Select trigger to modify the returned value for a > column being retrieved in a Select statement? A select trigger restricts > you > to reference only the old or pre value. In SPL, it does not allow an > assignment of a new value to a referenced old value. > > Is there another method for modifying a specific column value in a select > statement using a trigger or means other than a view? I am looking for a > solution that does not involve using a view to replace the current table > being > referenced. > > Thanks, > > Bob > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113321ee76c577051699353b
That would be an option but I was hoping for a solution that would not require a code change. I tried using the SPL referencing correlation clause to modify and return a computed value for a specific column in a table. But there is a restriction in a select trigger to only reference the old value and this prevents calculating a new return value in SPL. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, May 21, 2015 9:30 AM To: ids@iiug.org Subject: Re: Modify returned values from select statement [35159] Add a CASE expression to the query itself? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 21, 2015 at 10:49 AM, BOB KRUSE < bob.kruse@galaxyhotelsystems.com> wrote: > Is it possible to use a Select trigger to modify the returned value > for a column being retrieved in a Select statement? A select trigger > restricts you to reference only the old or pre value. In SPL, it does > not allow an assignment of a new value to a referenced old value. > > Is there another method for modifying a specific column value in a > select statement using a trigger or means other than a view? I am > looking for a solution that does not involve using a view to replace > the current table being referenced. > > Thanks, > > Bob > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113321ee76c577051699353b ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
So if you don't want to modify the code, why not substitute a VIEW that returns data using a CASE or, if more complex transformations or detections are needed, the results of a stored procedure call? Renaming the table and replacing it with a view sounds like an ideal solution. Another would be to have the unprivileged users connecting to a different database. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 21, 2015 at 11:41 AM, Kruse, Robert < bob.kruse@starwoodhotels.com> wrote: > That would be an option but I was hoping for a solution that would not > require > a code change. I tried using the SPL referencing correlation clause to > modify > and return a computed value for a specific column in a table. But there is > a > restriction in a select trigger to only reference the old value and this > prevents calculating a new return value in SPL. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, May 21, 2015 9:30 AM > To: ids@iiug.org > Subject: Re: Modify returned values from select statement [35159] > > Add a CASE expression to the query itself? > > Art > > Art S. Kagel, President and Principal Consultant ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Thu, May 21, 2015 at 10:49 AM, BOB KRUSE < > bob.kruse@galaxyhotelsystems.com> wrote: > > > Is it possible to use a Select trigger to modify the returned value > > for a column being retrieved in a Select statement? A select trigger > > restricts you to reference only the old or pre value. In SPL, it does > > not allow an assignment of a new value to a referenced old value. > > > > Is there another method for modifying a specific column value in a > > select statement using a trigger or means other than a view? I am > > looking for a solution that does not involve using a view to replace > > the current table being referenced. > > > > Thanks, > > > > Bob > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a113321ee76c577051699353b > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > This electronic message transmission contains information from the Company > that may be proprietary, confidential and/or privileged. The information is > intended only for the use of the individual(s) or entity named above. If > you > are not the intended recipient, be aware that any disclosure, copying or > distribution or use of the contents of this information is prohibited. If > you > have received this electronic transmission in error, please notify the > sender > immediately by replying to the address listed in the "From:" field. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01182f724f982b05169a5ef8
Hi Art, If I were designing this from scratch, I think a view would be a great idea. But I'm reluctant to change my schema to replace tables with views with "instead of" triggers in my existing production environment. I'm researching this for specific application functionality where the developers feel they should not have to change their code. And I agree. I can create internal user accounts for this application functionality and associate a default role with those user accounts that would enable them to retrieve the processed data. I just can't get the select trigger functionality to work they way I would like. Your idea to create a new database with views is a very interesting one. I will look into that option. Thanks, Bob -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, May 21, 2015 10:53 AM To: ids@iiug.org Subject: Re: Modify returned values from select statement [35163] So if you don't want to modify the code, why not substitute a VIEW that returns data using a CASE or, if more complex transformations or detections are needed, the results of a stored procedure call? Renaming the table and replacing it with a view sounds like an ideal solution. Another would be to have the unprivileged users connecting to a different database. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 21, 2015 at 11:41 AM, Kruse, Robert < bob.kruse@starwoodhotels.com> wrote: > That would be an option but I was hoping for a solution that would not > require a code change. I tried using the SPL referencing correlation > clause to modify and return a computed value for a specific column in > a table. But there is a restriction in a select trigger to only > reference the old value and this prevents calculating a new return > value in SPL. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Thursday, May 21, 2015 9:30 AM > To: ids@iiug.org > Subject: Re: Modify returned values from select statement [35159] > > Add a CASE expression to the query itself? > > Art > > Art S. Kagel, President and Principal Consultant ASK Database > Management www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on the IIUG, nor any other organization > with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, May 21, 2015 at 10:49 AM, BOB KRUSE < > bob.kruse@galaxyhotelsystems.com> wrote: > > > Is it possible to use a Select trigger to modify the returned value > > for a column being retrieved in a Select statement? A select trigger > > restricts you to reference only the old or pre value. In SPL, it > > does not allow an assignment of a new value to a referenced old value. > > > > Is there another method for modifying a specific column value in a > > select statement using a trigger or means other than a view? I am > > looking for a solution that does not involve using a view to replace > > the current table being referenced. > > > > Thanks, > > > > Bob > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a113321ee76c577051699353b > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > This electronic message transmission contains information from the > Company that may be proprietary, confidential and/or privileged. The > information is intended only for the use of the individual(s) or > entity named above. If you are not the intended recipient, be aware > that any disclosure, copying or distribution or use of the contents of > this information is prohibited. If you have received this electronic > transmission in error, please notify the sender immediately by > replying to the address listed in the "From:" field. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01182f724f982b05169a5ef8 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.