XML columns cannot be added in group by clause.

Swami
--------------------------------------

On Feb 24, 3:37 am, "Xh" <lukasz.bud...@komputery-internet.net> wrote:
> Hi All,
>
> I'm using DB2 v9 Express-C - I have problem when selecting XML
> columns.
>
> I have 2 columns in patients table:
> - id_patient
> - patient_info
>
> select * from patients;
> select id_patient, patient_info from patients;
>
> work fine - but that's easy
>
> I added new table examinations (1 patient can have many examinations)
> examination:
> id_examination
> id_patient
> examination_results
>
> and tried to do something like this, show me patients' ids and infos +
> number of examinations:
>
> SELECT P.ID_PATIENT, P.PATIENT_INFO, COUNT(E.ID_EXAMINATION) AS
> EXAMINATION#
> FROM PATEINTS as P, EXAMINATIONS as E
> WHERE P.ID_PATIENT = E.ID_PATIENT
> GROUP BY P.ID_PATIENT;
>
> but above query returns an error - P.PATIENT_INFO is not included in
> "group by", but when
> I added it, there was another error saying that XML cannot be used in
> grouping functions
>
> any idea what should I do to fetch pateint_info XML + number of
> examinations in one query?
> does DB2 supports such things?
>
> I have written query with subquery that produces me expected results
> - but I'm wondering whether it can be done faster and in more
> beautiful way
>
> thanks in advance for any tips
> best regards
> R