Sql command with union clause

[Former Member]

Hello here is my statement:

SELECT Global_Code.name as Descrip, Patient.patient_id, Global_Code.category

FROM Global_Code INNER JOIN

Patient ON Global_Code.code = Patient.race

WHERE (Global_Code.category = 'R')

union all

SELECT Global_Code.name, Patient.patient_id, Global_Code.category

FROM Global_Code INNER JOIN

Patient ON Global_Code.code = Patient.marital_status

WHERE (Global_Code.category = 'MS')

union all

SELECT Global_Code.name, Patient.patient_id, Global_Code.category

FROM Global_Code INNER JOIN

Patient ON Global_Code.code = Patient.living_arrangement_1

WHERE (Global_Code.category = 'LA')

I am trying to add these fields to a report. Example Race, Marital Status by using their description instead of just S for single and so on. The report just runs then stops responding. Can anyone help me with this?

[Former Member · Accepted solution]

Is the patient table already in your report? Is it in a table or a command? Mixing tables and commands can be done, but it's not very efficient because Crystal usually ends up processing the joins locally instead of having the database server do the work. This can dramatically slow down your report.

If the patient table is in the report as a table., there's a MUCH easier way to do this.

1. Add the Global_Code table to your report.

2. Right-click on the table name and select 'Rename'.

3. Set the name to something like "Global_Code_Race".

Repeat steps 1, 2, and 3 for each field where you want to display the description.

Now, in the Select Expert, add something like the following:

{Global_Code_Race.Category} = 'R' and

{Global_Code_MaritalStatus.Category} = "MS" and

...

Do this same for every instance of the Global_Code table that you've added to the report.

-Dell

[Former Member · Accepted solution]

Dell, This is great!!! i didn't know that you could rename the tables when you add them to crystal! This helps so much! THANKS!!!

[former_member11696]

Moved to the report Design Forum