Good point, and I agree with you. CTE would be a potentially very useful enhancement. I just used one to solve a problem in TSQL for a customer.
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Boydell, Stuart Sent: Thursday, April 08, 2010 11:44 PM To: U2 Users List Subject: Re: [U2] Common Table Expressions It could be; although by-exp is not recursive. Probably with a set of WITHIN claused statements it may do the trick - I haven't given it much thought. But it's only one of the things that CTE would give you access to. The point though was not 'how would you do this with the current set of U2 tools'. The provision of CTEs in SQL didn't give SQL the ability to do things you couldn't do before - it just made it a whole lot simpler and more efficient. The point is what advantages would the provision of CTEs give to U2. If the idea is good in SQL - and it is - it's probably worthy of consideration by the folks who can appropriately assess each technology and see how it might be applied to advantage within the U2 environment. Cheers, Stuart Boydell -----Original Message----- I'd have to think about it a bit, but wouldn't your scenario be a good case for a BY-EXP clause? I haven't used a BY-EXP in years! :o -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Boydell, Stuart Sent: Thursday, April 08, 2010 5:27 PM To: U2 Users List Subject: Re: [U2] Common Table Expressions One scenario off the top of my head would be to allow recursive selects. This would be similar to a qselect, where you select a list of MVs from a record, but would also allow you to include the parent id and any child ids and grandchild ids, etc as a result of the CTE select. Eg. A master customer AC has child accounts which may also have child ACs - you would use a CTE to select the customer and return the parent and list of children and grandchildren etc in a single optimised select. To do this in U2 now probably requires a recursive basic program. Stuart Boydell -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of phil walker Sent: Friday, 9 April 2010 06:30 To: U2 Users List Subject: Re: [U2] Common Table Expressions Hi Symeon, I have been trying to think how you would use CTE (or equivalents) in u2 SQL. Are you willing to enlighten us? Regards Phil > -----Original Message----- > From: [email protected] [mailto:u2-users- > [email protected]] On Behalf Of Symeon Breen > Sent: Thursday, 8 April 2010 8:16 p.m. > To: 'U2 Users List' > Subject: Re: [U2] Common Table Expressions > > It could certainly work in u2 SQL - not sure how you would do this in > English (MVQuery or whatever) tho > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Boydell, > Stuart > Sent: 08 April 2010 06:55 > To: U2 Users List > Subject: [U2] Common Table Expressions > > Just working on a SQL project which uses lots of CTEs (Common Table > Expressions) for in-memory caching of table data. > Really useful things in terms of speed and not having to create > temporary > tables for manipulating data. Wondering if anyone has thought about if > or > how they might work in U2... > Just a thought for better and better (which is down at the moment.) > Cheers, > Stuart Boydell > > > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
