Hmmmm. Thot I tried that

Post #118,745 by tablizer 9/24/03 6:21:24 PM Reply	Hmmmm. Thot I tried that That works now in my simple test, but it complained in the more complicated orginal. My collegue also tried that I thought because the same thing worked without the alias at the top. As soon as he put an alias in the top, but nowhere else, it failed. Odd, futher investigation is warrented..... Anyhow, thanx for the anti-sanity check. ________________ oop.ismad.com
Post #118,759 by ChrisR 9/24/03 9:26:56 PM Reply	The proviso being.... ...that the expression in the GROUP BY must exactly match the expression in the SELECT being grouped. Since some CASE and DECODE statements can get rather long-winded, it can be a pain to keep them in sync when you start tweaking the stuff.
Post #118,765 by ben_tilly 9/25/03 12:12:59 AM Reply	That is less of a pain if you build the query dynamically Then you define the chunk in a variable and interpolate it in twice. No more need to manually synchronize. Cheers, Ben "good ideas and bad code build communities, the other three combinations do not" - [link\|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html\|Stefano Mazzocchi]
Post #118,779 by CRConrad 9/25/03 4:55:01 AM Reply	Or, if you do it manually, good ol' copy-and-paste.
Post #118,797 by ChrisR 9/25/03 9:38:51 AM Reply	That would presume Dynamic SQL In PL/SQL and T-SQL, variables can not be used to construct SQL on the fly without the performance overhead of dynamic recompilation. I don't know if Tablizer is actually building these queries on the client or server, and whether he is using a programming language or some form based environment.
Post #118,801 by CRConrad 9/25/03 10:00:12 AM Reply	I think Ben meant, and Bryce would use, a programming... ...language (or IDE environment) on the client. In Ben's case, Perl (naturally!); in Bryce's, [ObCheapShot:] either "L" or "TOP", of course! :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #118,807 by ben_tilly 9/25/03 10:32:22 AM Reply	Right, that or... define some kind of "make world" where you build your views from templates and then load the output into the database. For the record I like maintaining definitions of things in the database outside of it in a filesystem where you can use your standard revision control mechanisms to track things. Cheers, Ben "good ideas and bad code build communities, the other three combinations do not" - [link\|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html\|Stefano Mazzocchi]
Post #118,830 by tablizer 9/25/03 11:51:28 AM Reply	Or, get rid of SQL and replace it with a BS12-like alternative where you can reference similar stuff by name. SQL was chosen by IBM over BS12 because they thought managers would relate to its more English-like grammar. IOW, SQL is the COBOL of relational languages. Anyhow, I still don't know why the simple one worked but the more complex one did not. If I get time, I will incrimentally strip the bigger one piece by piece until it works. So much for pair programming. (Or if I get too fat: pear programming.) ________________ oop.ismad.com
Post #119,080 by tablizer 9/26/03 1:48:39 PM Reply	update It appears we could repeat the calculation over and over again, but we also refer to the calculated part in several parts of the WHERE clause. Thus, it is actually less wordy and better factored to have a double select instead, which allows one to use the alias. ________________ oop.ismad.com
Post #119,084 by ChrisR 9/26/03 2:15:20 PM Reply	The other alternative is... ...to factor the calculation into a pl/sql function. Of course, you'd still have to have the call to the function, but it makes for more reusability in the long run.
Post #119,142 by tablizer 9/26/03 9:40:12 PM Reply	I asked for a view to be created, but was immed. rejected Asking for a new function is not likely to get a better reception. ________________ oop.ismad.com

Welcome to IWETHEY!