On Tue, Feb 3, 2009 at 5:40 PM, Jan Pazdziora <jpazdzi...@redhat.com> wrote:
> On Tue, Feb 03, 2009 at 04:21:29PM +0530, Gurjeet Singh wrote:
> > >
> > > _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
> > > FROM rhnSatelliteCert
> > > WHERE label = :label
> > > ORDER BY version DESC NULLS LAST
> > > """)
> > >
> > > So rhnSatelliteCert needs some mappings, I think the TO_CHAR looks
> > > usable in PostgreSQL, is it just the nvl that needs to change here?
> > >
> > > You can see quite a few other queries in the file, I don't know how
> > > many of them are going to end up getting called but this is the first
> > > one I hit.
> >
> > As Jan said, nvl() is provided by Orafce. But I'd recommend using ANSI
> > standard COALESCE() for the purpose as that is available in both Oracle
> and
> > Postgres.
> >
> > BTW, I am more worried about the NULLS LAST part of the query. As I have
> > mentioned in the
> > Worklog<https://fedorahosted.org/spacewalk/wiki/PostgresWorklog>,
> > this feature is available in Postgres only since 8.3. And I have no
> freakin'
> > clue how to get the same behaviour in PG < 8.3!
>
> 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.
And again, I'd ask for CASE expression instead of DECODE.
ORDER BY
CASE WHEN version IS NULL
THEN -1 /*this should be less than any possible value of version*/
ELSE version
END
DESC;
Or if we are too paranoid, we can replace -1 with
(select min(version)-1 from table)
;)
Best regards,
--
gurjeet[.sin...@enterprisedb.com
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
