Can't make CTE work in a function
in [DB2]
|
From: Tonkuma on 18 May 2010 08:19 > ..... The CTE-based function I was > trying to develop "just worked" in both PostgreSQL and SQL Server. Of > course Oracle, as always, has it's own unique way to do these things > ..... Many logics could be written without using CTE, sometimes more simple ways. Using Nested-table-expressions and/or SQL control statements would be usual ways. Occasionally, eliminating unnecessary SET to variable statements, by combining multiple statenments into one statement. For example: SET v = (WITH r ( c ) AS (SELECT c FROM t) SELECT c FROM r); could be replaced with(by using NTE)... SET v = (SELECT c FROM (SELECT c FROM t) r( c )); I want to know your examples of CTE-based functions which are worked in both PostgreSQL and SQL Server, but difficult to convert to DB2.
From: Serge Rielau on 20 May 2010 05:22 Peter, Consistently, eh? May I dare guess that DB2 is the server you use least? So could it be you natively gravitate to what works in SQL Server and then of course only run into DB2 limitations because you aren't even aware of those of the other system? Does SQL Server now support VALUES everywhere? Does it support multi row INSERTs? What about subqueries in the SET clause of an UPDATE? Ever tried to create a BEFORE trigger in SQL Server? Not even sure if SQL Server supports FOR EACH ROW triggers. I won't comment on PostgreSQL since I simply do not know it well enough. DB2 was the first server that introduced WITH and IBM pushed it into the standard. We do take a long term view when we design language and with properly designed language what you get is a leap-frog game where different vendors color out the language at different speeds. You can thank IBM for the very fact that there is such a thing as a compatible WITH syntax to begin with. Cheers Serge PS: While you're at it, care to explain TSQL GROUP BY to me? What a mess! -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Peter Headland on 21 May 2010 19:11 On May 20, 2:22 am, Serge Rielau <srie...(a)ca.ibm.com> wrote: > May I dare guess that DB2 is the server you use least? You'd be wrong. I don't use any of them more than the other, nor do I have any significant history with any of them. In fact, I haven't done much serious db work for years before this project which, as I said, must be able to use any of the four to do exactly the same thing. That implies I do exactly the same (functionally) in each of them. For this project, I have had to do a self-taught crash-course on four databases in parallel; yours (in which I include the documentation) has given me the most difficulty. I'm sorry you find that statement so unpalatable that you seek to discredit me. It's precisely the fact that I was largely ignorant about these four products when I began which should make my comments on stuff like documentation and ease-of- use valuable. I'm certainly not saying the other three dbs are perfect. Each has made me curse repeatedly. DB2 is simply the one with the highest curse count so far. FWIW, it's possible part of my difficulty has been that DB2 seems to be far more of a closed community than the other dbs - it's easy to find stacks of discussion of the others on the web; DB2 seems less heavily covered. When I went to a good local technical bookstore, they had near-zero on DB2; when I asked why, the owner told me that he used to stock some titles, but sent them back because he had zero sales. Sales of titles on the other three were steady. I don't know why this is; maybe everyone who uses DB2 works for IBM these days? :-) > So could it be you natively gravitate to what works in SQL Server I have no idea where you get the idea I am a SQL Server expert, much less fan-boy. When coding something new for the four dbs, I actually tend to code for PostgreSQL first and SQL Server last, for operational reasons that do not imply a value judgment; DB2 and Oracle slot in in the middle in random order. > I won't comment on PostgreSQL since I simply do not know it well enough. You should take a look at it as a reference point - it's better than all the commercial offerings in many ways, though not without its own little annoyances. Infinitely cleaner than MySQL (but what isn't?) Of the four, it has the lowest curse-count by a long way so far. -- Peter Headland
From: Tonkuma on 21 May 2010 23:39 >> FWIW, it's possible part of my difficulty has been that DB2 seems to be far more of a closed community than the other dbs - ..... << http://www.dbforums.com/ In the dBforums, number of viewing peoples are usually ... Oracle > DB2 > Microsoft SQL Server > PostgreSQL
From: Serge Rielau on 21 May 2010 23:40
Peter I had no intention of discrediting you and apologize it I came acroos as such. I simply did try to understand. I do get a lot of "DB2 is is bad" because it isn't "the same" as wath ever people are used to. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab |