Merging Data from Multiple Tables

[Former Member]

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 · Accepted solution]

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)

[Former Member · Accepted solution]

UNIONs and JOINs aren't the same thing. A UNION appends one table to another. It doesn't join them.

Jason

[Former Member · Accepted solution]

Thanks to both of you. The union actually solved my problem, the two tables have the same basic data, so it merged it into one set, which is what I was trying to do. Here is what I ended up with.

SELECT FN_GET_WEEKDAYS('RENO',GPH.G6_ASGN_DD,SD_APP_DD)

, B1.B1_ALT_ID

, B1.B1_FILE_DD

, b1.b1_appl_status

, GPH.SD_PRO_DES

, GPH.SD_APP_DES

, GPH.B1_DUE_DD

, GPH.SD_APP_DD

, GPH.G6_ASGN_DD

, GPH.G6_ISS_FNAME

, GPH.G6_ISS_LNAME

, GPH.ASGN_FNAME

, GPH.ASGN_LNAME

FROM B1PERMIT B1

INNER JOIN GPROCESS_HISTORY GPH

ON B1.B1_PER_ID1 = GPH.B1_PER_ID1

AND B1.B1_PER_ID2 = GPH.B1_PER_ID2

AND B1.B1_PER_ID3 = GPH.B1_PER_ID3

WHERE B1.B1_ALT_ID = 'BLD09-00299'

AND (GPH.SD_PRO_DES LIKE '%Review%'

OR GPH.SD_PRO_DES LIKE '%Improvement%')

AND ((GPH.G6_ISS_FNAME) IS NOT NULL

OR (GPH.ASGN_FNAME) IS NOT NULL )

AND B1.B1_FILE_DD <= GPH.G6_ASGN_DD

AND ((GPH.SD_CHK_LV1 = 'Y'

AND GPH.SD_CHK_LV2 = 'N')

OR (GPH.SD_CHK_LV1 = 'N'

AND GPH.SD_CHK_LV2 = 'Y'))

AND FN_GET_WEEKDAYS('RENO',GPH.G6_ASGN_DD,GPH.SD_APP_DD) > 0

UNION

SELECT FN_GET_WEEKDAYS('RENO',GP.G6_ASGN_DD,GP.G6_STAT_DD)

, B1.B1_ALT_ID

, B1.B1_FILE_DD

, b1.b1_appl_status

, GP.SD_PRO_DES

, GP.SD_APP_DES

, GP.B1_DUE_DD

, GP.G6_STAT_DD

, GP.G6_ASGN_DD

, GP.GA_FNAME

, GP.GA_LNAME

, GP.ASGN_FNAME

, GP.ASGN_LNAME

FROM B1PERMIT B1

INNER JOIN GPROCESS GP

ON B1.B1_PER_ID1 = GP.B1_PER_ID1

AND B1.B1_PER_ID2 = GP.B1_PER_ID2

AND B1.B1_PER_ID3 = GP.B1_PER_ID3

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

[Former Member]

James,

Where to start??? The beginning I suppose...

1) You shouldn't have a problem linking 3 tables... or 50 tables. The key is that each of the tables can be linked via common fields on another table.

2) The fact that you have ended up with with 56 rows (14 * 4), means that you have created a cartesian join (very rarely is that the desired result)... The problem is with the join... or lack there of. In your case, you are missing the WHERE bi.FieldID = gp.FieldID part.

3) Creating joins in the WHERE clause... Yes you CAN do it, but it's not a habit you want to get into. Especially if you are just starting to learn SQL. Get in the habit of using the JOIN clause. ie: INNER JOIN, LEFT OUTER JOIN ect. You will get more control and better performance.

Fix your joins and you'll be in business.

Jason

[Former Member]

Could you help me with the join part of the statement? Right now, both queries are using the same join:

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

When I merge the two queries into one, I change the alias on gprocess_history to gph, like in this statement:

select b1.b1_alt_id "Permit Number" , b1.B1_FILE_DD "Received Date" , fn_get_weekdays('RENO',gph.G6_ASGN_DD, sd_app_dd) "Total Days -GPH" , gph.sd_pro_des "Task Name -GPH" , gph.sd_app_des "Status -GPH" , gph.b1_due_dd "Due Date -GPH" , gph.sd_app_dd "Date Completed -GPH" , gph.G6_ASGN_DD "Date Routed -GPH" , gph.g6_iss_fname||gph.g6_iss_lname "Reviewer Name -GPH" , gph.ASGN_FNAME||gph.ASGN_LNAME "Assigned -GPH" ,fn_get_weekdays('RENO',gp.G6_ASGN_DD, gp.G6_STAT_DD) "Total Days -GP" , gp.sd_pro_des "Task Name -GP" , gp.sd_app_des "Status -GP" , gp.b1_due_dd "Due Date -GP" , gp.g6_stat_dd "Date Completed -GP" , gp.G6_ASGN_DD "Date Routed -GP" , gp.GA_FNAME||gp.GA_LNAME "Reviewer Name -GP" , gp.ASGN_FNAME||gp.ASGN_LNAME "Assigned -GP" from b1permit b1, gprocess_history gph, gprocess gp where b1.b1_alt_id = 'BLD09-00299' and (gph.SD_PRO_DES like '%Review%' or gph.SD_PRO_DES like '%Improvement%') and ((gph.g6_iss_fname)is not null or (gph.ASGN_FNAME)is not null) and ((gph.sd_chk_lv1 = 'Y' and gph.sd_chk_lv2 = 'N') or (gph.sd_chk_lv1 = 'N' and gph.sd_chk_lv2 = 'Y')) and b1.b1_file_dd < gph.g6_asgn_dd and b1.B1_PER_ID1||b1.B1_PER_ID2||b1.B1_PER_ID3 = gph.B1_PER_ID1||gph.B1_PER_ID2||gph.B1_PER_ID3 and fn_get_weekdays('RENO', gph.G6_ASGN_DD, gph.sd_app_dd) >0 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.sd_chk_lv1 = 'Y' and gp.sd_chk_lv2 = 'N') or (gp.sd_chk_lv1 = 'N' and gp.sd_chk_lv2 = 'Y')) 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 and gp.g6_stat_dd is null and fn_get_weekdays('RENO', gp.G6_ASGN_DD, gp.G6_STAT_DD) >0

I am trying a new query now, but I only have this so far:

select gp.sd_pro_des, gph.sd_pro_des from gprocess gp full outer join gprocess_history gph on gp.b1_per_id1 = gph.b1_per_id1 and gp.b1_per_id2 = gph.b1_per_id2 and gp.b1_per_id3 = gph.b1_per_id3 where gp.sd_pro_des like '%Review%' and gph.sd_pro_des like '%Review%'

The b1_per_id1, b1_per_id2, b1_per_id3 are the primary key fields for these three tables.

Edited by: James Cody on Sep 12, 2008 5:16 PM

[Former Member]

James,

You should try something like this:

FROM b1permit b1

INNER JOIN gprocess_history gph ON b1.FieldName1 = gph.FieldName2

INNER JOIN gprocess gp ON b1.FieldName4 = gp.FieldName5

So relating it back to your last example:

SELECT 
gp.sd_pro_des, 
gph.sd_pro_des
FROM gprocess gp 
FULL OUTER JOIN gprocess_history gph
ON gp.b1_per_id1 = gph.b1_per_id1
and gp.b1_per_id2 = gph.b1_per_id2
and gp.b1_per_id3 = gph.b1_per_id3
FULL OUTER JOIN b1permit b1
ON gp.b1_per_id1 = b1.b1_per_id1
and gp.b1_per_id2 = b1.b1_per_id2
and gp.b1_per_id3 = b1.b1_per_id3
WHERE gp.sd_pro_des like '%Review%'
and gph.sd_pro_des like '%Review%'

Be aware of a couple of things here:

1) The FULL OUTER JOIN... This join type will bring back ALL records from ALL tables. Unless all 3 tables have the exact same records, you will have some NULLs in you data set.

The INNER JOIN is the most common. I only brings back records where there is a match between the two tables.

The LEFT OUTER JOIN would be the 2nd most common. It brings back all or the records from the "Left" table and only the matching records from "Right" table(s).

I just found this link. It gives a decent explanation [http://www.codinghorror.com/blog/archives/000976.html]

2) Linking on 3 fields. It's not unheard of to have a 3 way compound key and it shouldn't cause any problems if that is legitimately the case. I just don't think I've seen a case where a compound key was used like that across tables like that.

If you need more help, let me know.

Jason