-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 3 Feb 2009 19:11:39 +0530 Gurjeet Singh <[email protected]> wrote:
> On Tue, Feb 3, 2009 at 6:34 PM, Jan Pazdziora <[email protected]> > wrote: > > > On Tue, Feb 03, 2009 at 06:26:19PM +0530, Gurjeet Singh wrote: > > > > > > > > How about > > > > > > > > order by decode(version, null, 1, 0), version > > > > > > Yup... that'll work, but will mess up the ordering if more than > > > one of version's values is non-null. > > > > > > SQL> select * from t order by decode( a, null, 1, 0 ); > > > > > > A > > > ---------- > > > 1 > > > 2 > > > 4 > > > 3 > > > > > > > > > > > > 6 rows selected. > > > > That's why my proposed solution was > > > > order by decode(version, null, 1, 0), version > > > > not just > > > > order by decode(version, null, 1, 0) > > > > > Right.. I missed that last reference to version! > Couldn't get decode to work initially but the case seemed ok and I ended up with: diff --git a/backend/satellite_tools/satCerts.py b/backend/satellite_tools/satCerts.py index 290b987..692a39c 100644 - --- a/backend/satellite_tools/satCerts.py +++ b/backend/satellite_tools/satCerts.py @@ -311,12 +311,17 @@ _query_update_dates = rhnSQL.Statement(""" """) _query_latest_version = rhnSQL.Statement(""" - - SELECT nvl(version, 0) version, version orig_version, cert, - - TO_CHAR(issued, 'YYYY-MM-DD HH24:MI:SS') issued, - - TO_CHAR(expires, 'YYYY-MM-DD HH24:MI:SS') expires + SELECT COALESCE(version, 0) as version, version as orig_version, cert, + TO_CHAR(issued, 'YYYY-MM-DD HH24:MI:SS') as issued, + TO_CHAR(expires, 'YYYY-MM-DD HH24:MI:SS') as expires FROM rhnSatelliteCert WHERE label = :label - - ORDER BY version DESC NULLS LAST + ORDER BY + CASE WHEN version IS NULL + THEN -1 + ELSE version + END + DESC Holler if you spot any problems. Cheers, Devan - -- Devan Goodwin <[email protected]> Software Engineer Spacewalk / RHN Satellite Halifax, Canada 650.567.9039x79267 -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (GNU/Linux) iEYEARECAAYFAkmIkXQACgkQAyHWaPV9my7dEACfS10NMN0d8VtNyufpVUD1dkfZ E5MAnRvl1+R7d1Cve65TyQbS4R4+15As =o65X -----END PGP SIGNATURE----- _______________________________________________ Spacewalk-devel mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-devel
