SEO

#1 (**permalink**) 02-27-2008, 11:49 AM

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

#2 (**permalink**) 02-27-2008, 11:49 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

selecting XML columns