I awalys hated SQL based batch updates

Post #208,724 by broomberg 5/26/05 3:16:23 PM Reply	I awalys hated SQL based batch updates It's called Structured Query Language, not Structured Update Language.
Post #208,725 by tablizer 5/26/05 3:25:54 PM Reply	Let's bring back FoxPro! Just (half) joking. It seems we need some kind of collection-oriented language for such thing. Older cursor-centric collection-oriented tools such as XBase (FoxPro) might help supply some good ideas to the industry so that explicit loops don't have to be written. APL? How about an APL + XBase hybrid, eh? ________________ oop.ismad.com
Post #208,730 by broomberg 5/26/05 4:06:28 PM Reply	Silly person you Depending on the SQL environment, there are procedural language hooks for working through the data. Problem it, each is a kludge to get around the core design limitation of SQL. BUT: SQL handles 90% (random percentage pulled out of my ass, of course) of what the standard end-user wants to do VERY easily. And easily does lots of complex joins that truly SUCK in any X-base derivative. So, from a productivity perspective, for most users, it is far better to force the occasional programmer to suffer through a painful update, then almost all the users to suffer through X-base crap all the time.
Post #208,732 by drewk 5/26/05 4:33:17 PM Reply	Preach! === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #208,876 by tablizer 5/27/05 9:02:12 PM Reply	63.472% Disagree SQL handles [about] 90% ... of what the standard end-user wants to do VERY easily Not if you have poorly-normalized tables that have corporate cruft that's built up for decades. Further, as described, in this case I have to denormalize copies for speed purposes. Anything more than a 2-way join on slim tables tends to be too slow for their needs. SQL handles 90% (random percentage pulled out of my ass, of course) of what the standard end-user wants to do VERY easily. And easily does lots of complex joins that truly SUCK in any X-base derivative. Joins only semi-suck in X-Base. Further, most modern dialects let one use both SQL and X-Base as needed. Thus, you can get the best of both worlds. ________________ oop.ismad.com
Post #211,986 by broomberg 6/21/05 7:36:14 PM Reply	Real world example I needed to pull out people who took a certain amount of classes in the last 3 years. But there was not enough. So I wanted to pull people who took a certain amount of classes in the last 5 years, as long as they also took any class within the last 3 years. But there was not enough. So I wanted to fill out the list with people who took at least a single class within the last year, deduping against the 1st list, and maintaining a separate list so when I extracted the data I could nth out the secondary list to achieve my final quantity. Time to interactively develop this, including running a dozen times or so as I tuned my queries, about 10 minutes. This would SUCK in any other language I've ever dealt with, and take hours to develop. \n\ndrop table fin_primary;\n\ncreate table fin_primary as\n\tselect client_id id from res_aggr\n\twhere s_course in\n\t\t\t\t(\n\t\t\t\t'A021','A039','A057','A059','A061','A063','A071','A072',\n\t\t\t\t'A075','A090','A091','A097','A103','A105','A106','A113',\n\t\t\t\t'A119','A122','A135','A141','A142','A149','A150','A155',\n\t\t\t\t'A158','A159','A167','A168','A172','A176','A177','A178',\n\t\t\t\t'A179','A180','A183','A184','A185','A193','A194','A195',\n\t\t\t\t'A199','A200','A201','A202','O013','O023','O028','O033',\n\t\t\t\t'O057','O114','O125','O134','O135','O138','O140','A204',\n\t\t\t\t'A207','A209','A213','A217','A218','A232','A235','O149',\n\t\t\t\t'O150','O151','O153','O154','O155','O156'\n\t\t\t\t)\n\t\t\tand\n\t\t\t\tcourse_date > sysdate - (5 * 365)\n\t\t\tand\n\t\t\t\tattend = 'A'\n\t\t\tgroup by client_id\n\t\t\thaving count() > 2\n\tintersect\n\tselect unique(client_id) id from res_aggr\n\twhere course_date > sysdate - (3 365)\n\t\t\tand\n\t\t\t\tattend = 'A'\n;\n\nselect count() from fin_primary;\n\ndrop table fin_secondary;\n\ncreate table fin_secondary as\n\tselect unique (client_id) id from res_aggr\n\twhere s_course in\n\t\t\t\t(\n\t\t\t\t'A021','A039','A057','A059','A061','A063','A071','A072',\n\t\t\t\t'A075','A090','A091','A097','A103','A105','A106','A113',\n\t\t\t\t'A119','A122','A135','A141','A142','A149','A150','A155',\n\t\t\t\t'A158','A159','A167','A168','A172','A176','A177','A178',\n\t\t\t\t'A179','A180','A183','A184','A185','A193','A194','A195',\n\t\t\t\t'A199','A200','A201','A202','O013','O023','O028','O033',\n\t\t\t\t'O057','O114','O125','O134','O135','O138','O140','A204',\n\t\t\t\t'A207','A209','A213','A217','A218','A232','A235','O149',\n\t\t\t\t'O150','O151','O153','O154','O155','O156'\n\t\t\t\t)\n\t\t\tand\n\t\t\t\tcourse_date > sysdate - (1 365)\n\t\t\tand\n\t\t\t\tattend = 'A'\nminus\n\tselect id from fin_primary;\n\nselect count(*) from fin_secondary;\n
Post #211,989 by tablizer 6/21/05 8:02:51 PM Reply	I am not sure what your point is There are some things that SQL does well, and others that is does poorly. No tool is good at everything. The things that SQL tends to suck at, cursor-oriented techniques do fairly well at. Thus, if we combine the two, then we can cover more territory. But I would still like to replace SQL with a better relational language (for the non-cursor half). ________________ oop.ismad.com
Post #211,991 by broomberg 6/21/05 8:11:42 PM Reply	It's been done already "perldoc DBI" for more info!
Post #212,001 by admin 6/21/05 9:10:53 PM Reply	Or PL/SQL, or TransactSQL, or... Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #212,012 by broomberg 6/21/05 11:13:03 PM Reply	Nah Give me a real language that happens to talk to databases over a database language that pretends to be real.
Post #212,013 by admin 6/21/05 11:16:54 PM Reply	I didn't say they were the best. Just that the fusion had been done already. I mean, sheesh... you can even use Perl and Python as stored proc languages in Postgres. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #212,014 by broomberg 6/21/05 11:20:25 PM Reply	I know. I do. I use it to allow an ODBC connected Coldfusion application execute a stored procedure that makes directories on an FTP server.
Post #212,026 by tablizer 6/22/05 1:03:50 AM Reply	I find PL/SQL and Transact/SQL too verbose ________________ oop.ismad.com

Welcome to IWETHEY!