[Spacewalk-list] Modification of PGSQL queries

[Pierre Casenove]

I have a doubt on one commit:
On git f6d4609327644d01a522465cd33022a052ff664c there is a closing
parenthesis on line 48, after END keyword.
I think it is a mistake: if you count, there are now two opening parenthesis
and three closing before the FROM keyword.

32 SELECT   SECURITY_ERRATA, BUG_ERRATA, ENHANCEMENT_ERRATA,
33         GO.GROUP_ID AS ID, GROUP_NAME, GROUP_ADMINS, SERVER_COUNT,
34         NOTE_COUNT, GO.MODIFIED, GO.MAX_MEMBERS,
35         'unknown' AS ALL_ERRATA,
36         (SELECT  CASE MAX(CASE PS.state
37                           WHEN 'OK' THEN  1
38                           WHEN 'PENDING' THEN 2
39                           WHEN 'UNKNOWN' THEN 3
40                           WHEN 'WARNING' THEN 4
41                           WHEN 'CRITICAL' THEN 5
42                           END)
43                  WHEN 1 THEN 'OK'
44                  WHEN 2 THEN 'PENDING'
45                  WHEN 3 THEN 'UNKNOWN'
46                  WHEN 4 THEN 'WARNING'
47                  WHEN 5 THEN 'CRITICAL'
48                  END) ST
49           FROM rhn_probe_state PS, rhnServerGroupMembership SGM
50           LEFT JOIN rhn_check_probe CP
51             ON SGM.server_id = CP.host_id
52          WHERE  PS.probe_id = CP.probe_id
53            AND  SGM.group_id = UMSG.server_group_id
54         GROUP BY SGM.group_id) MONITORING_STATUS
55  FROM   rhnVisServerGroupOverviewLite GO, rhnUserManagedServerGroups UMSG
56 WHERE   GO.ORG_ID = :org_id
57   AND   UMSG.user_id = :user_id
58   AND   UMSG.server_group_id IN (%s)
59   AND   GO.GROUP_ID = UMSG.server_group_id
60 ORDER BY UPPER(GROUP_NAME), GO.GROUP_ID

2011/8/10 Michael Mraka <michael.mraka at redhat.com>

> Pierre Casenove wrote:
> % Hello,
> % Could you please review my modified queries?
> %
> % I also ran into another error 500 pgsql on page
> % /network/software/packages/target_system_list.pxt
> % I've modified query "potential_systems_for_package" in file
> % /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource/xml/System_queries.xml
> % and replace the NVL keyword by coalesce:
> % SELECT  S.id
> %   FROM  rhnPackage P, rhnChannelPackage CP, rhnServerChannel SC,
> rhnServer S
> %  WHERE  S.org_id = :org_id
> %    AND  SC.server_id = S.id
> %    AND  SC.channel_id = CP.channel_id
> %    AND  CP.package_id = :pid
> %    AND  CP.package_id = P.id
> %    AND  COALESCE((SELECT MAX(PE.evr)
> %            FROM rhnServerPackage SP, rhnPackageEvr PE
> %           WHERE SP.name_id = P.name_id
> %             AND SP.server_id = S.id
> %             AND SP.evr_id = PE.id), (EVR_T(NULL, '0', '0'))
> %             )
> %         <
> %         (SELECT EVR FROM rhnPackageEVR PE WHERE PE.id = P.evr_id)
> %    AND    EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE USP.user_id
> =
> % :user_id AND USP.server_id = S.id)
> % ORDER BY  UPPER(NVL(S.NAME, '(none)')), S.ID
>
> Pierre please send the changes as patches (diffs). From the outout above
> it's hard to guess what the actual change is.
>
> I've commited NVL->COALESCE change to master. New package
> (spacewalk-base-1.6.11-1) should be available in a moment.
>
> % Thanks in advance for the review.
> %
> % Pierre
>
> Regards,
>
> --
> Michael Mráka
> Satellite Engineering, Red Hat
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110810/320212f4/attachment.htm>