Pierre Casenove wrote: % Hello, % After getting rid of hotmail, I'll try to send well formatted mail from % gmail... % So, I've continued to modify queries having (+) in them to get them PGSQL % compatible. % I attach the diff output. i've tested the queries from psql command line as % I didn't found where there were used in spacewalk. % Here are the modified queries: % contact_method_queries.xml : query "orgs_contact_method_tree" % CustomInfo_queries.xml : query "custom_info_keys_sans_value_for_system" % SystemGroup_queries.xml : query "visible_to_user_overview_fast" and % query "visible_groups_summary" % probe_queries.xml : query "system_probes"
Hi Pierre, I reviewed your patches and have a couple of comments: % Here are the queries I can't find how to modify: % config_queries.xml : query "configfiles_for_system" % The keywork NVL2 is used, but when I replace it by COALESCE keyword, I get: % ERROR: invalid input syntax for integer: "Y" % LINE 5: ...CFt WHERE CFT.latest_config_revision_id = CR.id), 'Y', 'N') ... COALESCE outputs first non-null parameter so it can't substitute NVL2 here. The CASE ... END is right way. See https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#TheDECODENVL2functionsproblem As for the 'invalid input syntax for integer: "Y"' - in PostgreSQL COALESCE expects that all parameters are of the same type which not the case here: 1 is integer while 'Y' is varchar. So it could be rewritten as COALESCE((select 'Y' from ...), 'N'). Moreover there is one more difference in Oracle's varchar2 and PostgreSQL varchar why I ended up with NVL((select 'Y' from ...), 'N'). For the explanation see https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#TheNVLfunctionproblem % Package_queries.xml : query "system_upgradable_package_list": % When I put LEFT JOIN in the query, it crash with error % ERROR: syntax error at or near "VERSION" % LINE 9: full_list.evr.version VERSION, % If I replace "VERSION" by "FOO", it crashes with error % ERROR: schema "full_list" does not exist Although the error message is different this is exactly https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#SELECTcolumnASalias issue. % I hope this helps. % Please let me know if the changes are commited too the master branch. % % Could you please help me on the last two queries I can't get to work? I % can't find anything in the postgresql porting guide. % % I hope my email will be readable. % % Pierre Please use 'diff -u' to create patches or even better checkout git repository and send output of 'git format-patch'. It's hard to see actual changes in the standard diff's output. % diff contact_method_queries.xml /root/contact_method_queries.xml.orig % 31,38c31,36 % < CASE MT.method_type_name % < WHEN 'Email' THEN CM.email_address % < WHEN 'Pager' THEN CM.pager_email END method_target % < FROM web_contact WC % < RIGHT JOIN rhn_contact_methods CM % < ON CM.contact_id = WC.id This RIGHT JOIN is wrong. You've been probably misleaded by the fact that (+) sign is on the opposite side of = than usual. Well, it's the order of tables in 'FROM X JOIN Y' not order of columns in 'ON X.column1 = Y.column2' what does matter. So LEFT JOIN means return all rows from the left table, even if there are no matches in the right table. And one more notice: you can almost forgot about RIGHT JOIN, it's very rearly needed. In 99.99% you can use LEFT JOIN which is more natural. % < LEFT JOIN rhn_method_types MT % < ON MT.recid = CM.method_type_id ... % diff probe_queries.xml /root/probe_queries.xml.orig % 23a24 % > rhn_probe P, % 25,27c26 % < rhn_probe P % < RIGHT JOIN rhn_probe_state PS % < ON PS.probe_id = P.recid % --- % > rhn_probe_state PS % 31a31 % > AND PS.probe_id(+) = P.recid Please be carefull about syntax; there have to be a comma after the join if it isn't the last in the list: FROM rhn_sat_cluster SC, rhn_command C, rhn_probe P LEFT JOIN rhn_probe_state PS ON PS.probe_id = P.recid, rhn_check_probe CP I've corrected and commited your changes in master. The fixed package is spacewalk-base-1.6.10-1. Thank you for your contribution. Regards, -- Michael Mráka Satellite Engineering, Red Hat _______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
