How to combine the field values for multiple records?

[beacon5]

Hi everybody,

[Crystal Report 2008] [Crystal SQL Designer]

Many months ago I posted a similar question, but I had to abandon it for a while due to other work priorities. Now that the dust has settled I'm tackling this once again. Here's the link from the original thread:

http://tek-tips.com/viewthread.cfm?qid=1636234

Just to clarify the previous post, I'm going to post an example of the table I'm working with.

ID  Pat  Referral#  Appointment#  Consultation#  Review#
1   A    100                                            
2   A               100                                 
3   A                             100                   
4   A                                            100    
5   B    200                                            
6   B               200

In the above, the Referral# is a number data type, but the other three fields are string data type.

Instead of creating the Add Command, as suggested in the previous thread, I wanted to compile the data in a query just as a way to review the data prior to using it in a command in my report. However, I've run into an issue that I thought had to do with the data types, but apparently is the result of something else.

Here's my sample union query that should take everything from Pat A and make it show up as one record and everything from Pat B and make it show up as one record:

SELECT 
  REF_CONSULTATION."PATID", 
  To_Char(REF_CONSULTATION."ref_order_number") As OrderNumber
FROM 
  "SYSTEM"."REF_CONSULTATION" REF_CONSULTATION

Union

SELECT 
  REF_CONSULTATION."PATID", 
  REF_CONSULTATION."app_order_number" As OrderNumber
FROM 
  "SYSTEM"."REF_CONSULTATION" REF_CONSULTATION

Union

SELECT 
  REF_CONSULTATION."PATID", 
  REF_CONSULTATION."con_order_number" As OrderNumber
FROM 
  "SYSTEM"."REF_CONSULTATION" REF_CONSULTATION

Union

SELECT 
  REF_CONSULTATION."PATID", 
  REF_CONSULTATION."md_order_number" As OrderNumber
FROM 
  "SYSTEM"."REF_CONSULTATION" REF_CONSULTATION

When I run this query, this is the output:

ID  Pat  OrderNumber
1   A    100
2   A    
3   B    200
4   B

There is an extra record for each patient with a blank order number. I tried removing To_Char from the first SELECT statement and adding To_Number to each of the remaining SELECT statements, but it didn't make a difference...the data type changed, but blank was replaced with a zero.

I have a number of other fields that I want to include in this statement, but I've eliminated as much as I can to combine the record and I haven't been successful. I'm probably missing something simple, but I'm just not seeing it and would appreciate someone taking a fresh look at it.

Thanks,
beacon

 

[lbass]

I think you should add a where clause to EACH part of the union like this:

SELECT REF_CONSULTATION."PATID", To_Char(REF_CONSULTATION."ref_order_number") As OrderNumber
FROM "SYSTEM"."REF_CONSULTATION" REF_CONSULTATION
WHERE REF_CONSULTATION."ref_order_number" is not null

Union

//etc

-LB