[SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Hello, Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But, now, one request which was 'good' before, don't want to work any more now. It was : (pretty long) SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and aes.sect_id <> 9 and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 UNION SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and aes.sect_id <> 9 and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and aes.ent_id not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and sect_id <> 3 and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, e.nom Now, if I want that my request works well, I have to remove the order by statement. But, of course, it is not ordered any more. So how can I translate this request to one which can work with an order by statement ? Thanks. -- Richard NAGY Presenceweb
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Josh Berkus a écrit : Mr. Nagy, > Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. > But, > now, one request which was 'good' before, don't want to work any more > now. I'm not sure I understand your question. What do you mean "doesn't work"? Please give a detailed list of all steps taken, including any error messages received. > Now, if I want that my request works well, I have to remove the order > by > statement. But, of course, it is not ordered any more. Er, by "request" do you mean "query"? Using an ORDER BY statement as you appear to use it in that query is permitted and correct. I suspect that your problem is located somewhere else. For example, what interface tool are you using to send queries to the database? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco Hello Josh BERKUS, Thanks for your answer and sorry for my english. It was a query and not a request! Well, the query works well on postgresql 7.0.2 but when I upgraded the RDBS to 7.1.3, it did not work any more. The error was : Relation e does not exist. But, in the 'order by' statement, I removed the 'e.'. Now, the query works well without any error. It is ordered but I think it is certainly due to the fact that the table was already ordered on disk. So, I have no more errors but I'm not sure that it is completely good. PS : The interface tool that I have used to send queries to the database was psql. Regards -- Richard NAGY Presenceweb
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Josh Berkus a écrit : Richard, I'm curious now. What happens if you remove the table qualifications, e.g.: ORDER BY type, nom; -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco Hello Josh, Yes, it works! I don't know exactly why, but it works! Thanks a lot. -- Richard NAGY Presenceweb
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Andre Schnabel a écrit : Hello Richard, I did some testing and after all your query should be ordered right. The test's I have done: Test=# select t.foo1 from testtable t Test-# union Test-# select t.foo2 from testtable t Test-# order by t.foo1; ERROR: Relation 't' does not exist Same error as you get Test=# select t.foo1 from testtable t Test-# union Test-# select t.foo2 from testtable t Test-# order by foo1; foo1 -- abc cdef (2 rows) Ordered Ascending (maybe by chance?) --- Test=# select t.foo1 from testtable t Test-# union Test-# select t.foo2 from testtable t Test-# order by foo1 DESC; foo1 -- cdef abc (2 rows) Ordered descending (ordering works!) --- I guess, the table-alias is not known to the order-clause. Maybe the execution (or parsing) order of the UNION changed from 7.0 to 7.1. Hello Andre, Thanks very much for having tested. Yes, it works well. -- Richard NAGY Presenceweb
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Tom Lane a écrit : Richard NAGY <[EMAIL PROTECTED]> writes: > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > UNION > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > aes.ent_id > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > sect_id <> 3 > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > e.nom ORDER BY applied to the result of a UNION has to be on the output columns of the UNION. "e.type" etc are names of input-table columns. Try just "type" and "nom", which will refer to the second and third output columns of the UNION. Pre-7.1 got this wrong (and would sometimes produce wrong output ordering or even a backend crash, if the arms of the UNION didn't all yield the same datatype). regards, tom lane Hello Tom, Yes, thanks a lot. It works! Regards. -- Richard NAGY Presenceweb
[SQL] link toward pgsql-sql is missing on the left menu athttp://archives.postgresql.org/
Hello, I have noticed that the link toward pgsql-sql is missing on the left menu at http://archives.postgresql.org/. Is that normal? -- *** Richard NAGY Nameshield 46, rue Jean BODIN F-49000 Angers Tél : +33 2 41 18 28 28 *** ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
