IDS Forum

RE: Modify returned values from select statement

Posted By: Kruse, Robert
Date: Thursday, 21 May 2015, at 1:15 p.m.

In Response To: Re: Modify returned values from select statement (Art Kagel)

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.

Messages In This Thread

• Modify returned values from select statement
  BOB KRUSE -- Thursday, 21 May 2015, at 10:49 a.m.
  • Re: Modify returned values from select statement
    Marco Greco -- Thursday, 21 May 2015, at 11:03 a.m.
    • RE: Modify returned values from select statement
      Kruse, Robert -- Thursday, 21 May 2015, at 11:10 a.m.
  • Re: Modify returned values from select statement
    Art Kagel -- Thursday, 21 May 2015, at 11:29 a.m.
    • RE: Modify returned values from select statement
      Kruse, Robert -- Thursday, 21 May 2015, at 11:41 a.m.
      • Re: Modify returned values from select statement
        Art Kagel -- Thursday, 21 May 2015, at 12:52 p.m.
        • RE: Modify returned values from select statement
          Kruse, Robert -- Thursday, 21 May 2015, at 1:15 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.