I do all my views on the ARS views exclusively. These can be quite complex and include SQL functions, queries, processing of those queries, unions, sub views, etc.
I think it's a lot better than basing in on the Txxx (& Cxx) fields. The simple fact that your source SQL uses terms like T1982 and C91818165 makes it very ugly to maintain. A view on a view (especially the one that ARS creates) is NO problem and NO trouble. If there is any performance issues with a complex view there are lots of database things you could do (like instantiated views as a worst case, simple indexes as needed on the ARS tables through the ARS developer tool). That being said, you could make a complicated view where you derive the T number from arschema and the C number from arfield etc. But why bother? Again, the SQL source will be more complex and harder to maintain. The ARS view is one to one with the underlying T table. There is no performance penalty for using it and the large benefit of an understandable SQL source. Cheers Ben www.softwaretoolhouse.com From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Kemes, Lisa Sent: January-25-11 17:26 To: [email protected] Subject: Re: View of a View? ** Thanks everyone for your input. The 3 columns were just to show what a view of the view looked like. I actually need the view to have about 20 columns. We are creating a DB Link so that this data can be imported into a Data Warehouse for heavy duty reporting capabilities. We usually don't like our DB links to go straight to the "T" tables, so we are creating a view. The reason why we are not using the AR SYSTEM created view is because we need to normalize some of the data and create new fields etc (that I don't want/need in an actual form). I guess I could still create a self-join form, but I think I like creating views better. But I will not be creating a view on top of a view. Sounds like trouble.. If we do a migration (which we are actually going to be doing in the next couple of months - we are getting a new production server), I'll just have to copy and recreate the views if the T # for the tables change correct? Thanks everyone! Lisa From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Thad Esser Sent: Monday, January 24, 2011 8:29 PM To: [email protected] Subject: Re: View of a View? ** Also with regards to maintainability, if you are migrating code through various environments to get to production, the T# for that form isn't guaranteed to be the same. It most likely is, but it depends on how tortured your migration process is (or was, before you started working somewhere *sigh*). The schema id doesn't come along in a *.def file. Have you considered keeping it all in Remedy, and just creating a self-join form with only those three fields showing? You'll end up with an Oracle view anyway if you need it for outside integrations or whatever, and you don't have to worry about maintaining stuff at the DB level. Thad On Mon, Jan 24, 2011 at 2:44 PM, Axton <[email protected]> wrote: ** It's measurably more maintainable to create the view against the table. Any time you modify a form, the views that ARS creates on top of the tables is dropped and recreated. This is because there is no way in the db to alter the view. When the view is dropped and recreated it may invalidate the view you have that is built on top of that view, esp if the owner of the view is not the ars db account. I've found that it's best not to create or alter objects in the ars schema. They tend to turn into something that resembles space debris after a while. On Mon, Jan 24, 2011 at 4:22 PM, Roberts, Chas <[email protected]> wrote: Sort of depends if you want to wait for the view to be processed before the view of the view. It's probably measurably more efficient to create the view(let) from the original table. Chas -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Kemes, Lisa Sent: Monday, January 24, 2011 2:07 PM To: [email protected] Subject: Re: View of a View? So is it recommended? Lisa -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Grooms, Frederick W Sent: Monday, January 24, 2011 5:05 PM To: [email protected] Subject: Re: View of a View? On Oracle, the optimizer will convert the view (through the other view) down to the actual table when it executes the query. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Kemes, Lisa Sent: Monday, January 24, 2011 3:50 PM To: [email protected] Subject: View of a View? ** I have a table T204 and the AR SYSTEM automatically creates a view on top of it called "TEIS_SURVEY_TEXT" I need to create a view and need to know the ramifications of creating a view of a view. I could create my view like this: CREATE OR REPLACE VIEW "ARADMIN"."TYCO_ADR_SURVEY_TYPE_V" ("REQUEST_ID", "STATUS", "LANGUAGE") AS SELECT REQUEST_ID , STATUS, LANGUAGE FROM TEIS_SURVEY_TEXT; Which would be a view of a view. Or should I create it like this: CREATE OR REPLACE VIEW "ARADMIN"."TYCO_ADR_TEIS_TYPE_V" ("REQUEST_ID", "STATUS", "LANGUAGE") AS SELECT T204.C1,C8,C536870918 FROM T204; What are everyone's thoughts? Lisa Kemes AR System Developer Tyco Electronics 717-810-2408 <tel:+17178102408> tel 717-602-9460 <tel:+17176029460> cell [email protected] ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" This email is confidential and intended solely for the use of the individual(s) to whom it is addressed. The information contained in this message may be privileged and confidential and protected from disclosure. If you are not the author's intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please delete all copies, both electronic and printed, and contact the author immediately. ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
