Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Merging Data from Multiple Tables

Thanks in advance for any help. I am using CR XI Developer against an Oracle 9i database. I am trying to create a report that pulls data from three tables: b1permit, gprocess, and gprocess_history. Records start in the gprocess table, then when they are assigned through the front end app, they are moved to gprocess_history. b1permit is the case info that these records are tied to.

The problem I am running into is that gprocess and gprocess_history both contain the same basic fields, and I need both. I can't seem to write a query that will pull the records correctly. For example, I am expecting 14 rows from gprocess and 4 from gprocess_history. Instead I get 56 (14 rows * 4 rows). Here are the queries that pull the correct records from the tables separately. BLD09-00299 is the case number, I need to parameterize this part of the query in the end.

select fn_get_weekdays('RENO',gp.G6_ASGN_DD, sd_app_dd) "Total Days"

, b1.b1_alt_id "Permit Number", gp.sd_pro_des "Task Name", gp.sd_app_des "Status"

, b1.B1_FILE_DD "Received Date", gp.b1_due_dd "Due Date"--, gp.g6_stat_dd "Date Completed"

, gp.sd_app_dd "Date Completed", gp.G6_ASGN_DD "Date Routed", gp.g6_iss_fname||gp.g6_iss_lname "Reviewer"

, gp.ASGN_FNAME||gp.ASGN_LNAME "Assigned"

from b1permit b1, gprocess_history gp

where b1.b1_alt_id = 'BLD09-00299'

and (gp.SD_PRO_DES like '%Review%' or gp.SD_PRO_DES like '%Improvement%')

and ((gp.g6_iss_fname)is not null or (gp.ASGN_FNAME)is not null)

and b1.b1_file_dd < gp.g6_asgn_dd

and ((gp.sd_chk_lv1 = 'Y' and gp.sd_chk_lv2 = 'N') or (gp.sd_chk_lv1 = 'N' and gp.sd_chk_lv2 = 'Y'))

and fn_get_weekdays('RENO', gp.G6_ASGN_DD, gp.sd_app_dd) >0-->5

and b1.B1_PER_ID1||b1.B1_PER_ID2||b1.B1_PER_ID3 = gp.B1_PER_ID1||gp.B1_PER_ID2||gp.B1_PER_ID3

order by gp.sd_pro_des;

select fn_get_weekdays('RENO',gp.G6_ASGN_DD, gp.G6_STAT_DD) "Total Days"

, b1.b1_alt_id "Permit Number", gp.sd_pro_des "Task Name", gp.sd_app_des "Status"

, b1.B1_FILE_DD "Received Date", gp.b1_due_dd "Due Date", gp.g6_stat_dd "Date Completed"

, gp.G6_ASGN_DD "Date Routed", gp.GA_FNAME||gp.GA_LNAME "Reviewer", gp.ASGN_FNAME||gp.ASGN_LNAME "Assigned"

from b1permit b1, gprocess gp

where b1.b1_alt_id = 'BLD09-00299'

and (gp.SD_PRO_DES like '%Review%' or gp.SD_PRO_DES like '%Improvement%')

and ((gp.GA_FNAME)is not null or (gp.ASGN_FNAME)is not null)

and b1.b1_file_dd < gp.g6_asgn_dd

and gp.g6_stat_dd is null

and ((gp.sd_chk_lv1 = 'Y' and gp.sd_chk_lv2 = 'N') or (gp.sd_chk_lv1 = 'N' and gp.sd_chk_lv2 = 'Y'))

and fn_get_weekdays('RENO', gp.G6_ASGN_DD, gp.G6_STAT_DD) >0

and b1.B1_PER_ID1||b1.B1_PER_ID2||b1.B1_PER_ID3 = gp.B1_PER_ID1||gp.B1_PER_ID2||gp.B1_PER_ID3

order by gp.sd_pro_des

As you can see, the two queries are basically the same. I am fairly new to SQL and Crystal, and am pretty much self taught, so I have some gaps in my knowledge.

Thanks again,

James

Former Member
Former Member replied

Hi James,

I've been working with some similar issues, and I came up with a different kind of solution.

I went into the oracle back end, and defined a view that does what I want to do.

so, more or less:

select

blah blah blah

from <my first table>

where whatever

UNION

select

the same --- blah blah blah

from <my second table>

where whatever

to turn my two tables into one table with the expected fields.

You can then just use the view as you would a single table in the crystal report. I find this works a whole lot better than messing around with complex select statements, or attempting to do join gymnastics. (and it's not TOO horribly inefficient)

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question