Thank you for all who answered my questions. I have found out that
the problems is relateded to sorting a columns which are not in the
table of SELECT FROM clause. i.e. I am try to sort A.NAME which is
not in table HERBAL that I am selecting from.

I solve the query by arranging the order of table in the select from:
SELECT H.REFNO
, A.NAME
, A.STROKE
, A.S_CODE
, H.DOSAGE
, H.CHARACTERISTIC
, H.SYSTEM
, H.FUNC
, H.USES
, H.REMARK
FROM ALL_HERBAL A
RIGHT JOIN HERBAL_WITH_CAT HC
ON HC.H_REFNO = A.REFNO
JOIN HERBAL H
ON H.REFNO = A.REFNO
WHERE HC.HC_REFNO in (some values);

Now sorting on A.NAME, A.STROKE and A.S_CODE work correctly.
However this query proforms less effective than the previous one.
Any ideas to optimise this query?

Thanks once again!

regards,
Kenneth

> >SELECT H.REFNO
> > , A.NAME
> > , A.STROKE
> > , A.S_CODE
> > , H.DOSAGE
> > , H.CHARACTERISTIC
> > , H.SYSTEM
> > , H.FUNC
> > , H.USES
> > , H.REMARK
> >FROM HERBAL H
> >RIGHT JOIN HERBAL_WITH_CAT HC
> >ON HC.H_REFNO = H.REFNO
> >JOIN ALL_HERBAL A
> >ON A.REFNO = H.REFNO
> >
> >The Keylinks are HERBAL.REFNO and ALL_HERBAL.NAME which is the
> >primary key for table HERBAL and table ALL_HERBAL respectively.
>
> this doesn't make sense. Using RIGHT [OUTER] JOIN returns all

records in

> the HERBAL_WITH_CAT table regardless of whether they match HERBAL

or not,

> and then you don't select anything from that table? If you had

returned

> anything from that table, I'd say your KeyLinks were wrong

(KeyLinks shall

> uniquely identify a row within the result set, and even though

NULLs are

> different from any value, I don't think they make valid KeyLinks).

I guess

> what you want is an [INNER] JOIN. Remove RIGHT from your query and

you will

> get all records in HERBAL that match in HERBAL_WITH_CAT and

ALL_HERBAL.

> This also makes your KeyLinks sensible.
>
> HTH,
> Set