<html> <head> <style></style> </head> <body class='hmmessage'><div dir='ltr'> <div>Hello,</div>I've modified 4 queries in order to get this work... But I don't have an oracle set up to test my queries.<div>Moreover, I'm not sure the queries actually returns the legitimate results.</div><div><br></div><div>I insist on the fact that I'm not a DBA. I'm really not sure that the rewritten queries are correct. Please have a look on the diffs</div><div><br></div><div>Here are the diff on the three modifed files, in folder /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource/xml:</div><div><div>diff config_queries.xml /root/config_queries.xml.orig</div><div>85,90c85,90</div><div>< FROM rhnConfigFile CF</div><div>< LEFT OUTER JOIN rhnConfigRevision CR ON (CR.config_file_id = CF.id)</div><div>< LEFT OUTER JOIN rhnSnapshotConfigRevision SCR ON (SCR.config_revision_id = CR.id)</div><div>< LEFT OUTER JOIN rhnConfigFileName CFN ON (CFN.id = CF.config_file_name_id)</div><div>< LEFT OUTER JOIN rhnConfigContent CCon ON (CCon.id = CR.config_content_id)</div><div>< LEFT OUTER JOIN rhnChecksumView Csum ON (Csum.id = CCon.checksum_id)</div><div>---</div><div>> FROM rhnConfigContent CCon,</div><div>> rhnConfigFileName CFN,</div><div>> rhnConfigFile CF,</div><div>> rhnConfigRevision CR,</div><div>> rhnSnapshotConfigRevision SCR,</div><div>> rhnChecksumView Csum</div><div>91a92,96</div><div>> AND SCR.config_revision_id = CR.id</div><div>> AND CR.config_content_id = CCon.id (+)</div><div>> AND CR.config_file_id = CF.id</div><div>> AND CF.config_file_name_id = CFN.id</div><div>> AND CCon.checksum_id = Csum.id (+)</div></div><div><br></div><div><div>diff SystemGroup_queries.xml /root/SystemGroup_queries.xml.orig</div><div>16,22c16,25</div><div>< (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS</div><div>< FROM rhnSnapshot SN</div><div>< LEFT OUTER JOIN rhnSnapshotServerGroup SSG ON (SSG.snapshot_id = SN.id)</div><div>< LEFT OUTER JOIN rhnServerGroup SG ON (SG.id = SSG.server_group_id)</div><div>< LEFT OUTER JOIN rhnServerGroupType SGT ON (SGT.id = SG.group_type)</div><div>< WHERE SN.id = :ss_id</div><div>< AND SN.server_id = :sid</div><div>---</div><div>> (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS</div><div>> FROM rhnServerGroupType SGT,</div><div>> rhnServerGroup SG,</div><div>> rhnSnapshotServerGroup SSG,</div><div>> rhnSnapshot SN</div><div>> WHERE SN.id = :ss_id</div><div>> AND SN.server_id = :sid</div><div>> AND SN.id = SSG.snapshot_id</div><div>> AND SSG.server_group_id = SG.id</div><div>> AND SG.group_type = SGT.id (+)</div><div>90,101c93,107</div><div>< SELECT VSGM.group_id AS ID,</div><div>< SG.name AS GROUP_NAME,</div><div>< SGT.label AS GROUP_TYPE_LABEL,</div><div>< (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS</div><div>< FROM rhnServerGroup SG</div><div>< LEFT OUTER JOIN rhnVisServerGroupMembership VSGM ON (VSGM.group_id = SG.id)</div><div>< LEFT OUTER JOIN rhnServerGroupType SGT ON (SGT.id = CAST(VSGM.group_type as numeric) )</div><div>< LEFT OUTER JOIN rhnUserServerGroupPerms USGP ON (USGP.server_group_id = SG.id)</div><div>< WHERE VSGM.org_id = :org_id</div><div>< AND VSGM.server_id = :sid</div><div>< AND USGP.user_id = :user_id</div><div>< ORDER BY UPPER(SG.name)</div><div>---</div><div>> SELECT VSGM.group_id AS ID,</div><div>> SG.name AS GROUP_NAME,</div><div>> SGT.label AS GROUP_TYPE_LABEL,</div><div>> (SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id) AS USER_PERMITTED_ACCESS</div><div>> FROM rhnServerGroupType SGT,</div><div>> rhnServerGroup SG,</div><div>> rhnVisServerGroupMembership VSGM,</div><div>> rhnUserServerGroupPerms USGP</div><div>> WHERE VSGM.org_id = :org_id</div><div>> AND VSGM.server_id = :sid</div><div>> AND VSGM.group_id = SG.id</div><div>> AND VSGM.group_type = SGT.id (+)</div><div>> AND USGP.user_id = :user_id</div><div>> AND USGP.server_group_id = SG.id</div><div>> ORDER BY UPPER(SG.name)</div></div><div><br></div><div><div>diff Package_queries.xml /root/Package_queries.xml.orig</div><div>30,52c30,75</div><div>< PE.id AS EVR_ID,</div><div>< PN.name AS NAME,</div><div>< PE.version AS VERSION,</div><div>< PE.release AS RELEASE,</div><div>< PE.epoch AS EPOCH,</div><div>< PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,</div><div>< UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE</div><div>< FROM rhnPackageName PN</div><div>< LEFT OUTER JOIN rhnPackageNEVRA PNEVRA ON (PNEVRA.name_id = PN.id)</div><div>< LEFT OUTER JOIN rhnPackageEVR PE ON (PE.id = PNEVRA.evr_id)</div><div>< LEFT OUTER JOIN rhnSnapshotPackage SP ON (SP.nevra_id = PNEVRA.id)</div><div>< LEFT OUTER JOIN rhnSnapshot S ON (S.id = SP.snapshot_id)</div><div>< LEFT OUTER JOIN rhnPackage P ON (P.id = PN.id)</div><div>< LEFT OUTER JOIN rhnChannelPackage CP ON (CP.package_id = P.id)</div><div>< LEFT OUTER JOIN rhnSnapshotChannel SC ON (SC.channel_id = CP.channel_id)</div><div>< WHERE S.id = :ss_id</div><div>< AND S.server_id = :sid</div><div>< AND NOT EXISTS (SELECT 1 FROM rhnPackageSyncBlacklist PSB WHERE</div><div>< PSB.package_name_id = PN.id AND org_id is NULL OR org_id = :org_id)</div><div>< AND SC.snapshot_id = :ss_id</div><div>< AND NOT EXISTS (SELECT 1 FROM rhnServerPackage SEP WHERE</div><div>< SEP.server_id = :sid AND SEP.name_id = PN.id AND SEP.evr_id = PE.id)</div><div>< ORDER BY 8</div><div>---</div><div>> PE.id AS EVR_ID,</div><div>> PN.name AS NAME,</div><div>> PE.version AS VERSION,</div><div>> PE.release AS RELEASE,</div><div>> PE.epoch AS EPOCH,</div><div>> PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,</div><div>> UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE</div><div>> FROM rhnPackageName PN,</div><div>> rhnPackageEVR PE,</div><div>> rhnPackageNEVRA PNEVRA,</div><div>> rhnSnapshotPackage SP,</div><div>> rhnSnapshot S</div><div>> WHERE S.id = :ss_id</div><div>> AND S.server_id = :sid</div><div>> AND S.id = SP.snapshot_id</div><div>> AND SP.nevra_id = PNEVRA.id</div><div>> AND PNEVRA.name_id = PN.id</div><div>> AND NOT EXISTS (SELECT 1 FROM rhnPackageSyncBlacklist PSB WHERE</div><div>> PSB.package_name_id = PN.id AND org_id is NULL OR org_id = :org_id)</div><div>> AND PNEVRA.evr_id = PE.id</div><div>> AND NOT EXISTS (</div><div>> SELECT 1 FROM rhnServerPackage SP</div><div>> WHERE SP.server_id = :sid</div><div>> AND SP.name_id = PN.id</div><div>> AND SP.evr_id = PE.id</div><div>> )</div><div>> MINUS</div><div>> SELECT PN.id AS NAME_ID,</div><div>> PE.id AS EVR_ID,</div><div>> PN.name AS NAME,</div><div>> PE.version AS VERSION,</div><div>> PE.release AS RELEASE,</div><div>> PE.epoch AS EPOCH,</div><div>> PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,</div><div>> UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE</div><div>> FROM rhnPackageName PN,</div><div>> rhnPackageEVR PE,</div><div>> rhnPackage P,</div><div>> rhnChannelPackage CP,</div><div>> rhnSnapshotChannel SC</div><div>> WHERE SC.snapshot_id = :ss_id</div><div>> AND SC.channel_id = CP.channel_id</div><div>> AND CP.package_id = P.id</div><div>> AND P.name_id = PN.id</div><div>> AND P.evr_id = PE.id</div><div>> ORDER BY 8</div><div><br></div><div><br><br><div>> Date: Fri, 22 Jul 2011 19:44:02 +0200<br>> From: msuchy@redhat.com<br>> To: spacewalk-list@redhat.com<br>> Subject: Re: [Spacewalk-list] Error 500 on snapshots pages<br>> <br>> On 07/22/2011 08:15 AM, Pierre Casenove wrote:<br>> > Here is the SQL statement executed:<br>> > ERROR: syntax error at or near "MINUS" at character 869<br>> <br>> https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#MINUSkeyword<br>> <br>> Patches are welcome.<br>> <br>> -- <br>> Miroslav Such�<br>> Red Hat Satellite Engineering<br>> <br>> _______________________________________________<br>> Spacewalk-list mailing list<br>> Spacewalk-list@redhat.com<br>> https://www.redhat.com/mailman/listinfo/spacewalk-list<br></div></div></div> </div></body> </html>