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"
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') ... 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 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
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 < LEFT JOIN rhn_method_types MT < ON MT.recid = CM.method_type_id --- > DECODE( MT.method_type_name, > 'Email', CM.email_address, > 'Pager', CM.pager_email) method_target > FROM rhn_contact_methods CM, > web_contact WC, > rhn_method_types MT 39a38,39 > AND CM.contact_id (+) = WC.id > AND CM.method_type_id = MT.recid (+) diff ./CustomInfo_queries.xml /root/CustomInfo_queries.xml.orig 30,32c30,31 < FROM rhnCustomDataKey CDK < LEFT JOIN rhnServerCustomDataValue SCDV < ON CDK.id = SCDV.key_id --- > FROM rhnServerCustomDataValue SCDV, > rhnCustomDataKey CDK 33a33 > AND CDK.id = SCDV.key_id (+) diff SystemGroup_queries.xml /root/SystemGroup_queries.xml.orig 37,52c37,42 < (SELECT CASE MAX(CASE PS.state < WHEN 'OK' THEN 1 < WHEN 'PENDING' THEN 2 < WHEN 'UNKNOWN' THEN 3 < WHEN 'WARNING' THEN 4 < WHEN 'CRITICAL' THEN 5 END) < WHEN 1 THEN 'OK' < WHEN 2 THEN 'PENDING' < WHEN 3 THEN 'UNKNOWN' < WHEN 4 THEN 'WARNING' < WHEN 5 THEN 'CRITICAL' END ST < FROM rhn_probe_state PS, < rhnServerGroupMembership SGM < LEFT JOIN rhn_check_probe CP < ON SGM.server_id = CP.host_id < WHERE PS.probe_id = CP.probe_id --- > (SELECT DECODE( > MAX(DECODE(PS.state, 'OK', 1, 'PENDING', 2, 'UNKNOWN', 3, > 'WARNING', 4, 'CRITICAL', 5)), > 1, 'OK', 2, 'PENDING', 3, 'UNKNOWN', 4, 'WARNING', 5, > 'CRITICAL') ST > FROM rhn_check_probe CP, rhn_probe_state PS, > rhnServerGroupMembership SGM > WHERE PS.probe_id = CP.probe_id > AND SGM.server_id = CP.host_id(+) 83,97c73,76 < (SELECT CASE MAX(CASE PS.state < WHEN 'OK' THEN 1 < WHEN 'PENDING' THEN 2 < WHEN 'UNKNOWN' THEN 3 < WHEN 'WARNING' THEN 4 < WHEN 'CRITICAL' THEN 5 END) < WHEN 1 THEN 'OK' < WHEN 2 THEN 'PENDING' < WHEN 3 THEN 'UNKNOWN' < WHEN 4 THEN 'WARNING' < WHEN 5 THEN 'CRITICAL' END ST < FROM rhn_probe_state PS, < rhnServerGroupMembership SGM < LEFT JOIN rhn_check_probe CP < ON SGM.server_id = CP.host_id --- > (SELECT DECODE( > MAX(DECODE(PS.state, 'OK', 1, 'PENDING', 2, 'UNKNOWN', 3, > 'WARNING', 4, 'CRITICAL', 5)), > 1, 'OK', 2, 'PENDING', 3, 'UNKNOWN', 4, 'WARNING', 5, > 'CRITICAL') ST > FROM rhn_check_probe CP, rhn_probe_state PS, > rhnServerGroupMembership SGM 98a78 > AND SGM.server_id = CP.host_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
_______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
