Simple SQL look-up turns out hairy

Post #208,723 by tablizer 5/26/05 3:12:19 PM Reply	Simple SQL look-up turns out hairy If one wants to look-up and replace a code in one table based upon another, it seems one has to do goofy gyrations: [link\|http://www.techonthenet.com/sql/update.htm\|http://www.techonthe...om/sql/update.htm] \nUPDATE supplier \nSET supplier_name = ( SELECT customer.name \nFROM customers \nWHERE customers.customer_id = supplier.supplier_id) \nWHERE EXISTS \n ( SELECT customer.name\n FROM customers\n WHERE customers.customer_id = supplier.supplier_id); \n Is there a simpler way? One could argue that copying such is bad normalization, but I need to do "nightly batch joins" to make the interactive stuff run fast. ________________ oop.ismad.com
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
Post #208,726 by drewk 5/26/05 3:30:37 PM Reply	COALESCE UPDATE supplier \nSET supplier_name = COALESCE(\n ( SELECT customer.name \n FROM customers \n WHERE customers.customer_id = supplier.supplier_id) ,\n supplier_name ) === 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,728 by JayMehaffey 5/26/05 3:52:18 PM Reply	Re: Simple SQL look-up turns out hairy In MySQL you should be able to do this: \nUPDATE supplier JOIN customers ON customers.customer_id = supplier.supplier_id\nSET supplier_name = customer.name\n In MSSQL you can do the same thing by using an UPDATE table FROM joined tables. I don't know if either way of doing this is strictly SQL standard legal. Jay
Post #208,729 by ChrisR 5/26/05 3:57:11 PM 5/26/05 4:01:40 PM Reply	Simpler SQL \nUPDATE supplier\nSET supplier_name = cus.name\nFROM\n supplier sup\n INNER JOIN customers cus ON(\n (cus.customer_id = sup.supplier_id))\n or alternatively \nUPDATE supplier\nSET supplier_name = cus.name\nFROM\n supplier sup,\n customers cus\nWHERE (cus.customer_id = sup.supplier_id)\n Edited by ChrisR May 26, 2005, 04:01:40 PM EDT Expand All History
Post #208,879 by tablizer 5/27/05 9:06:38 PM Reply	My 1999 book implies UPDATE....FROM is not standard ________________ oop.ismad.com
Post #208,886 by ChrisR 5/27/05 9:57:05 PM Reply	Probably so... ...at least that's the same thing the [link\|http://developer.mimer.com/validator/\|Mimer Validator] says. Which is a shame, since it makes UPDATE and DELETE have the the same structure as the SELECT queries. That is, I typically want to know the rows effected before running the delete. Typical of my manual deletes, is to run the SELECT * on the operation just for a sanity check: \nUPDATE supplier\nSET supplier_name = cus.name\n-- SELECT *\nFROM\n supplier sup\n INNER JOIN customers cus ON(\n (cus.customer_id = sup.supplier_id))\n
Post #208,742 by pwhysall 5/26/05 5:23:28 PM Reply	And you're surprised because what? Peter [link\|http://www.ubuntulinux.org\|Ubuntu Linux] [link\|http://www.kuro5hin.org\|There is no K5 Cabal] [link\|http://guildenstern.dyndns.org\|Home] Use P2P for legitimate purposes!
Post #208,880 by tablizer 5/27/05 9:15:39 PM Reply	Another SQL Puzzle Does anybody know of a way to turn an integer into a zero or one value? If the column/expression is greater than zero, I want to calc it as one. Example: \nSELECT column_A FROM fooTable\n\n column_A\n --------\n 0\n 3\n 0\n 25\n etc...\n\nSELECT column_A, specialFunction(column_A) as bar FROM fooTable\n\n column_A bar\n -------- ---\n 0 0\n 3 1\n 0 0\n 25 1\n etc...\n I tried to use a Boolean expression, but some dialects don't seem to like it. By the way, thanks for the UPDATE suggestions, guys. Extra points if you can do such with multiple columns without a sub-select for each column. ________________ oop.ismad.com
Post #208,883 by ChrisR 5/27/05 9:38:21 PM 5/27/05 9:50:17 PM Reply	Re: Another SQL Puzzle SELECT\ncolumn_A,\n(CASE column_A WHEN 0 THEN 0 ELSE 1 END) as bar\nFROM fooTable or alternately: SELECT\ncolumn_A,\n(CASE WHEN (column_A = 0) THEN 0 ELSE 1 END) as bar\nFROM fooTable [edit note: forgot the when] Edited by ChrisR May 27, 2005, 09:50:17 PM EDT Expand All History
Post #209,124 by tablizer 5/31/05 1:18:06 AM Reply	Hmmm. Standard? ________________ oop.ismad.com
Post #209,154 by tuberculosis 5/31/05 10:35:29 AM 8/21/07 5:44:10 AM Reply	No such thing in SQL world - dialects everywhere "Whenever you find you are on the side of the majority, it is time to pause and reflect" --Mark Twain "The significant problems we face cannot be solved at the same level of thinking we were at when we created them." --Albert Einstein "This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses." --George W. Bush Edited by tuberculosis Aug. 21, 2007, 05:44:10 AM EDT Expand All History
Post #209,711 by tablizer 6/2/05 8:41:49 PM Reply	Good point. Standard != Common_Implementation (rant-brag) Now if the world went with my (draft) query language, missing features could be DBA-written because the language is 20x simpler than SQL. Everything, except possibly Boolean expressions, are function-like. Adding a new "function" is not the same as adding new syntax. It is equiv to adding a new library function, not changing the lang itself. (/rant-brag) ________________ oop.ismad.com
Post #209,717 by ben_tilly 6/2/05 9:24:29 PM Reply	Lemme guess... Your new query language is procedural and Turing complete, hence complex queries cannot be decently optimized, and therefore you lose the entire point of SQL. Furthermore you don't understand why any of this is a bad thing. Any guesses as to whether I am right? Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #209,728 by ChrisR 6/2/05 10:27:30 PM Reply	The best way to fix non-standard implementations.... ...is to go with a language that has no formal notion of operational, denotational or axiomatic semantics, and has no current implementation to fetter our optimism. Fiat will sort out the rest!
Post #209,739 by static 6/2/05 11:25:00 PM Reply	I had a look. You're correct. His writing shows he doesn't understand why SQL is non-procedural and not Turing-complete. His criticisms of SQL stem from exactly that lack. Wade. Save Fintlewoodlewix
Post #209,745 by ben_tilly 6/3/05 12:27:31 AM Reply	Fish in a barrel I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #211,826 by tablizer 6/20/05 8:55:05 PM Reply	Please be more specific ________________ oop.ismad.com
Post #211,827 by broomberg 6/20/05 9:09:47 PM Reply	Horse corpse - meet Mr. Stick
Post #211,828 by tablizer 6/20/05 9:20:14 PM Reply	IOW, guilty until proven innocent. IranWeThey Just point to the passage that allegedly exposed me as being declaratively ignorant. ________________ oop.ismad.com
Post #211,837 by static 6/20/05 9:44:01 PM Reply	Sorry, I've got real SQL work to do. Save Fintlewoodlewix
Post #211,876 by tablizer 6/21/05 2:16:46 AM 6/21/05 2:18:03 AM Reply	SQL sucks (as a relational language) I remember about 6 years ago I was in a huge debate with the Iwethey croud where I claimed cursor-oriented (semi-procedural) database manipulation was "better" because it closer fit the way (most) humans think. Others complained about such approach not being machine-optimizable because it loses its "mathability". My POV was that being human-friendly was more important than auto-optimization. (Many OO'ers even say they want to "wrap the persistence mechanism[1] and ugly SQL strings" to avoid having to deal with SQL.) After thinking long and hard, I decided that the "anti-imperative" people were generally right. However, what sold me was that I realized SQL sucks as a relational language. It is not relational algebra itself that is necessarily human-unfriendly, it is SQL's view of it. Thus, I set out to create what I feel would be a more human-friendly (and meta-friendly) relational language that allows one to better decompose big problems into many smaller ones and reference the parts by names instead of the run-on nesting of SQL. SQL is fine for small, simple queries, but gets ugly real fast as things get trickier. [1] RDBMS are far more than just about "persistence". ________________ oop.ismad.com Edited by tablizer June 21, 2005, 02:18:03 AM EDT Expand All History
Post #211,904 by ChrisR 6/21/05 10:39:45 AM Reply	Perhaps you need to get out of the design and... ...into the implementation phase. Either that, or convince Date & Darwin that your pet language is the way to go.
Post #211,954 by tablizer 6/21/05 3:06:20 PM Reply	Some convergence perhaps Re: Either that, or convince Date & Darwin that your pet language is the way to go. Date and I chose [infix] because we thought it was better for teaching purposes, but we have had cause to regret it and would probably switch to prefix notation if we could start again from scratch. -- Hugh Darwen Perhaps you need to get out of the design and...into the implementation phase I have started working on an XBase demo implementation of my proposed language, but keep dragging my feet when it comes to finishing it. ________________ oop.ismad.com
Post #211,955 by boxley 6/21/05 3:20:32 PM Reply	sounds like you would be happy doing MAS90 All tribal myths are true, for a given value of "true" Terry Pratchett [link\|http://boxleys.blogspot.com/\|http://boxleys.blogspot.com/] Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 49 years. meep questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org]
Post #211,963 by mmoffitt 6/21/05 4:35:39 PM Reply	Wow. That's harsh. ;0) bcnu, Mikem It would seem, therefore, that the three human impulses embodied in religion are fear, conceit, and hatred. The purpose of religion, one might say, is to give an air of respectibility to these passions. -- Bertrand Russell
Post #211,977 by boxley 6/21/05 6:03:18 PM Reply	its straight forward, lucrative and he enjoys the language All tribal myths are true, for a given value of "true" Terry Pratchett [link\|http://boxleys.blogspot.com/\|http://boxleys.blogspot.com/] Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 49 years. meep questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org]
Post #211,985 by broomberg 6/21/05 6:53:22 PM Reply	You are writing a language IN xBase? Of course you'll never finish it!
Post #211,990 by tablizer 6/21/05 8:04:39 PM Reply	I am sort of pushing off parsing issues to something else ________________ oop.ismad.com
Post #211,825 by tablizer 6/20/05 8:54:38 PM 6/20/05 9:16:28 PM Reply	WRONGO! Your new query language is procedural and Turing complete No. It is not procedural. You could call it functional though because it follows the "no side-effects" rule (at least for queries). It is pretty much just relational algebra in prefix notation. I don't know whether it is Turing Complete or not. I haven't given that much thought[1]. I don't know if any recursive behavior it presents. Take a look at the draft: [link\|http://www.c2.com/cgi/wiki?TqlRoadmap\|http://www.c2.com/cgi/wiki?TqlRoadmap] See the bottom of this link for a related discussion on "declarative": [link\|http://www.c2.com/cgi/wiki?TqlChainedJoin\|http://www.c2.com/cg...ki?TqlChainedJoin] [1] Does a query language have to be non-TC to be considered "relational"? ________________ oop.ismad.com Edited by tablizer June 20, 2005, 09:16:28 PM EDT Expand All History
Post #211,852 by JayMehaffey 6/20/05 10:19:39 PM Reply	Re: WRONGO! No. It is not procedural. You could call it functional though because it follows the "no side-effects" rule (at least for queries). It is pretty much just relational algebra in prefix notation. To be a functional language it has to be possible for a function call to return a function in one form or another. This is really the defining feature of functional languages. Something like this has to be possible. Fnew = FCreator(a,b,c) Result = Fnew(x) The Business System Twelve you mention has a functional like syntax, but is a procedural language. I don't know whether it is Turing Complete or not. I haven't given that much thought[1]. I don't know if any recursive behavior it presents. The material in the draft you present now is not Turing complete. You make no mention of looping or conditional structures at all, so I can't tell if your intentions are in that direction or not. Jay
Post #211,875 by tablizer 6/21/05 2:04:44 AM Reply	Prefix != "procedural" The Business System Twelve you mention has a functional like syntax, but is a procedural language. I don't think so. You seem to mistake anything with prefix notation as procedural. Boolean algebra can be done with prefix syntax, but that in itself does not make it procedural. To be a functional language it has to be possible for a function call to return a function in one form or another. I meant that it follows the "no side-effects" functional rule, not that it necessarily takes on all features of a "functional" language. I apologize for not being clear. ________________ oop.ismad.com
Post #212,206 by JayMehaffey 6/22/05 11:36:38 PM Reply	I wasn't talking aobut it's notation I don't have much to go on for either you language or Buisness System Twelve, but when I said they have functional like syntax I was talking about the way the code was laid out. X = func(param1, param2) Y = func2(x, param3) Z = func(func2(y, param4), param4) The code layout style, with every line being a function and the ability to use functions freely as parameters is typical of functional languages. But it isn't exclusive to them. On the other hand, the use of variables to pass values between lines of code is indicative of procedural code, as the organization of code into blocks that can be stored and called from other blocks. In fact, in a purely functional language there are no variables at all. The above code would have to be written. func( func2( func2( func(param1, param2), param3), param4), param4) Few langauges go to that extreme though. I meant that it follows the "no side-effects" functional rule, not that it necessarily takes on all features of a "functional" language. I apologize for not being clear. Few, if any, languages are perfectly side effect free. If it was truely side effect free then there would be no update or insert commands. For a language to be referentially transparent, that is to have no side effects at all, any call to a function with the same parameters must cause the function to return the same value. This means that selecting a specific field in a specific row in the database must always return the same value. Thus nothing can be allowed to change the tables. Pure functional languages can work despite this because they have no persistant store of data or interactive inputs. All information must be contained in the parameters passed to the function. Once again, few languages go that extreme. Which isn't to say that your language might no be fairly good in the end, though you still have a long way to go before you even have a complete description of it. It just won't be a functional language, unless you radically change the concept it will be a procedural language. Jay
Post #212,340 by tablizer 6/23/05 7:35:24 PM Reply	Variables can be semantic "references", not necess. storage the use of variables to pass values between lines of code is indicative of procedural code, as the organization of code into blocks that can be stored and called from other blocks....In fact, in a purely functional language there are no variables at all. The above code would have to be written. func( func2( func2( func(param1, param2), param3), param4), param4) This: func1(func2(func3(p3),p2),p1) can be represented as: a = func3(p3) b = func2(a, p2) func1(b, p1) It is just a matter of using names to reference parts instead of using nesting. The query compiler/interpreter can turn one into the other as needed. References are done as a matter of human convenience. Plus, references allow you to reference the same item twice, something that is difficult with nesting (SQL-like). The thing is, one can execute the above as procedural code, and that is one of the selling points of my proposed language: it can be easily implimented, or at least simulated, as native function calls. Now just like SQL INSERT, UPDATE, and DELETE, there are "data transformation" operations. Such indeed do violate pure functional, like you said. But outside of these, it is non-side-effect. ________________ oop.ismad.com
Post #209,174 by ChrisR 5/31/05 11:40:51 AM Reply	CASE is the ANSI/ISO-standard Don't mean that all DB's implement it though.
Post #208,884 by SpiceWare 5/27/05 9:47:22 PM Reply	Easy if you have decode SELECT column_A,\n decode(column_A,0,0,1) as bar\n FROM fooTable Darrell Spice, Jr. [link\|http://spiceware.org/gallery/ArtisticOverpass\|Artistic Overpass]\n[link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore
Post #214,331 by takicutie 7/12/05 12:07:06 AM Reply	Re: Another SQL Puzzle Well the have given their answers. One requires a functional answer, the decode answer the easiest solution if your using Oracle.. But if your using SQL server this simple SQL statement will do. (SELECT column_A, 1 AS bar FROM TBL WHERE column_A > 0) UNION ALL (SELECT column_A, 0 AS bar FROM TBL WHERE column_A = 0) Taki Cuevas
Post #214,428 by SpiceWare 7/12/05 10:30:23 AM Reply	hmm - decode won't work afterall upon rereading the puzzle, the decode solution wouldn't work. The requirement is > 0 turns into 1, which implies <= 0 turns to 0. The decode solution would have turned anything <> 0 into 1. It'll work if the data can only contain non-negative values, but we probably shouldn't make that assumption even though the sample doesn't include negative values. Based on that, your solution would work with a minor change of <= 0 in the 2nd select's where clause. Also welcome to the group! How'd you find us? Darrell Spice, Jr. [link\|http://spiceware.org/gallery/ArtisticOverpass\|Artistic Overpass]\n[link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore
Post #215,106 by tablizer 7/16/05 1:26:43 AM Reply	Nope, no negative in my case Life could be simpler if they only standardized an "iif" function. SELECT iif(b > 0, 1, 0) AS result FROM foo It has the parameters: iif([expression],[value if true],[value if false]) ________________ oop.ismad.com
Post #215,107 by ChrisR 7/16/05 2:30:11 AM Reply	It has been standardized. It's called [link\|http://z.iwethey.org/forums/render/content/show?contentid=208883\|CASE].

Welcome to IWETHEY!