SQL using 1 table from 4 databases to generate a complete report

[Former Member]

Hi

I fixing a report someone made and i'm running into an issue where items are missing.

Is it possible to generate 1 complete report using a product table from 4 different databases on the same server. 90% of this table is mirrored across the 4 databases, but unique records have been added  to each.

Currently the report is setup using 1 FROM database03

There are about 22 left outer joins in this report that seem to be handling the information correctly for the records it is suppling.

Question is : Can this be done and will it be handled via FROM or LEFT OUTER JOIN. I can supply the current code if this is possible.

Thanks in advance

Norm

[former_member292966 · Accepted solution]

Hi Norm,

In a word OOOUUUCCCHHH!!!!!  22 outer joins!

Can it be done, chances are not very well.  That's a lot of joins between a set of tables.  Because each table is coming from a different database, if you did it, I'm pretty sure it would take about 20 hours for a simple report to run.  That's a conservative estimate and a good chance it won't work still.

An alternative is to take the querying out of Crystal and do it in a stored procedure.  Depending on the database you are using, you can use linked or remote databases and create a query that will do this.  I'd recommend instead of querying all 4 tables at once, creating a temp table or cursor and drop each table into it.  Then you will get all the information you need from each table and send it off to Crystal.

Crystal doesn't handle linking cross databases very well because it wants to generate a single query.  When you cross databases, it requires querying each of the databases.  So Crystal works around this by querying the first database and then grabs all the records from each of the other databases and processes the records locally.  So instead of the database doing the work Crystal does the work on your machine, potentially downloading a huge amount of records to your machine.

If this is going to be something that happens regularly and you have a large amount of data, look into developing a data warehouse that will centralize the information from all the databases into a central location you can report on.

Good luck,

Brian

[Former Member · Accepted solution]

I was hoping it could be as simple as right outer joining the other 3 databse product tables to the FROM primary table.

I thought if i changed the AS {database}_prod_id and {database}_prodline_id from each of database tables to ALL_prod_id and ALL_prodline_id

"prod_warehouse_PQ"."pw_prodline_id" as PQ_Prodline_ID, change PQ to ALL

"prod_warehouse_PQ"."pw_prod_id" as PQ_Prod_ID, change PQ to ALL

SO if i did this for all 4 databases and made their Prodline_id and Prod_id into the ALL_ i could end up with a complete listing off all items from every database.

Prod ID and Prodline ID are my 2 groups in the report everything else works with lining up the proper fields with the proper database

Here is the command :

SELECT
"v_products"."pr_prod_id",
"v_products"."pr_prodline_id",
"v_products"."pr_sls1_stdlistprice",
"v_products"."pr_stocktype_flg",
"v_products"."pr_status_flg",
"v_products"."pr_catalogue_page",
"v_products"."pr_2_desc",
"v_products"."pr_1_desc",
"V_products"."pr_subgroup_id",
"V_products"."pr_prim_supplier_id" As PrimSupplier,
"v_products"."pr_create_date" as Tor_create_date,
"V_products"."pr_subcategory_id",
"v_products"."pr_prodgroup_id",
"V_products"."pr_warranty_period",

"prod_lines"."pl_short_desc",

"prod_subcat"."prsc_subcat_id",
"prod_subcat"."prsc_desc",

"prod_groups"."prgp_prodgroup_id",
"prod_groups"."prgp_short_desc",

"prod_warehouse_Tor"."pw_onhand_qty",
"prod_warehouse_Tor"."pw_commit_qty",
"prod_warehouse_Tor"."pw_whs_id",
"prod_warehouse_tor"."pw_acc_avgcost_amt",

"prod_supplier"."ps_fob_amt" as TOR_Fob_amt,
"prod_supplier"."ps_landedcost_amt" as TOR_Landed,

"prod_supplier_INS"."ps_fob_amt" as INS_FOB_amt,
"prod_supplier_INS"."ps_landedcost_amt" as INS_landed,

"prod_warehouse_PQ"."pw_whs_id" as PQ_whs_ID,
"prod_warehouse_PQ"."pw_onhand_qty" as PQ_onhand_qty,
"prod_warehouse_PQ"."pw_commit_qty" as PQ_commit_qty,
"prod_warehouse_PQ"."pw_prodline_id" as PQ_Prodline_ID,
"prod_warehouse_PQ"."pw_prod_id" as PQ_Prod_ID,
"prod_warehouse_PQ"."pw_acc_avgcost_amt" as PQ_Avcost,

"prod_warehouse_EDM"."pw_whs_id" as ED_Whs_ID,
"prod_warehouse_EDM"."pw_onhand_qty" as ED_onhand_qty,
"prod_warehouse_EDM"."pw_commit_qty" as ED_commit_qty,
"prod_warehouse_EDM"."pw_prodline_id" as ED_Prodline_ID,
"prod_warehouse_EDM"."pw_prod_id" as ED_Prod_ID,
"prod_warehouse_EDM"."pw_acc_avgcost_amt" as ED_Avcost,

"prod_warehouse_INSIZE"."pw_whs_id" as Insize_whs_id,
"prod_warehouse_INSIZE"."pw_onhand_qty" as Insize_onhand_qty,
"prod_warehouse_INSIZE"."pw_commit_qty" as Insize_commit_qty,
"prod_warehouse_INSIZE"."pw_prodline_id" as Insize_prodline_id,
"prod_warehouse_INSIZE"."pw_prod_id" as Insize_prod_id,
"prod_warehouse_Insize"."pw_acc_avgcost_amt" as Insize_Avcost,

"v_products_Insize"."pr_sls1_stdlistprice" as Insize_sls1_stdlistprice,
"v_products_Insize"."pr_create_date" as Insize_create_date,

"prod_images"."prim_image_filenames",
"prod_images"."prim_prod_id",
"prod_images"."prim_prodline_id",

"web_category_products"."wcap_prod_id",
"web_category_products"."wcap_prodline_id",
"web_category_products"."wcap_wct_wcm_id",

"web_category_master"."wcm_id",
"web_category_master"."wcm_code",
"web_category_master"."wcm_description",

"supp_udf_values"."sudv_supplier_id",
"supp_udf_values"."sudv_udf_id",
"supp_udf_values"."sudv_value",

"V_pr_lastsale_dates_PQ"."lastinvoice_date" as PQ_last_inv_date,

"V_pr_lastsale_dates_TOR"."lastinvoice_date" as TOR_last_inv_date,

"V_pr_lastsale_dates_EDM"."lastinvoice_date" as EDM_last_inv_date,

"V_pr_lastsale_dates_INS"."lastinvoice_date" as INS_last_inv_date

FROM   "sisl_data03"."dbo"."v_products" "v_products"
LEFT OUTER JOIN "sisl_data03"."dbo"."prod_supplier" "prod_supplier" ON
(("v_products"."pr_prod_id"="prod_supplier"."ps_prod_id")
AND ("v_products"."pr_prodline_id"="prod_supplier"."ps_prodline_id"))
AND("v_products"."pr_prim_supplier_id"="prod_supplier"."ps_supplier_id")

LEFT OUTER JOIN "sisl_data09"."dbo"."prod_supplier" "prod_supplier_INS" ON
(("v_products"."pr_prod_id"="prod_supplier_INS"."ps_prod_id")
AND ("v_products"."pr_prodline_id"="prod_supplier_INS"."ps_prodline_id"))
AND("v_products"."pr_prim_supplier_id"="prod_supplier_INS"."ps_supplier_id")

LEFT OUTER JOIN "sisl_data03"."dbo"."prod_lines" "prod_lines" ON
("v_products"."pr_prodline_id"="prod_lines"."pl_prodline_id")

LEFT OUTER JOIN "sisl_data03"."dbo"."prod_subcat" "prod_subcat" ON
("V_products"."pr_subcategory_id"="prsc_subcat_id")

LEFT OUTER JOIN "sisl_data03"."dbo"."prod_groups" "prod_groups"
ON ("V_products"."pr_prodgroup_id"="prgp_prodgroup_id")

LEFT OUTER JOIN "sisl_data02"."dbo"."V_pr_lastsale_dates" "V_pr_lastsale_dates_PQ"
ON ("v_products"."pr_prodline_id"="V_pr_lastsale_dates_PQ"."prodline_id")
AND ("v_products"."pr_prod_id"="V_pr_lastsale_dates_PQ"."prod_id")

LEFT OUTER JOIN "sisl_data03"."dbo"."V_pr_lastsale_dates" "V_pr_lastsale_dates_TOR"
ON ("v_products"."pr_prodline_id"="V_pr_lastsale_dates_TOR"."prodline_id")
AND ("v_products"."pr_prod_id"="V_pr_lastsale_dates_TOR"."prod_id")

LEFT OUTER JOIN "sisl_data04"."dbo"."V_pr_lastsale_dates" "V_pr_lastsale_dates_EDM"
ON ("v_products"."pr_prodline_id"="V_pr_lastsale_dates_EDM"."prodline_id")
AND ("v_products"."pr_prod_id"="V_pr_lastsale_dates_EDM"."prod_id")

LEFT OUTER JOIN "sisl_data09"."dbo"."V_pr_lastsale_dates" "V_pr_lastsale_dates_INS"
ON ("v_products"."pr_prodline_id"="V_pr_lastsale_dates_INS"."prodline_id")
AND ("v_products"."pr_prod_id"="V_pr_lastsale_dates_INS"."prod_id")

LEFT OUTER JOIN "sisl_data03"."dbo"."prod_images" "prod_images"
ON ("v_products"."pr_prod_id"="prod_images"."prim_prod_id")
AND ("v_products"."pr_prodline_id"="prod_images"."prim_prodline_id")

LEFT OUTER JOIN "sisl_data03"."dbo"."prod_warehouse" "prod_warehouse_Tor"
ON  ("v_products"."pr_prod_id"="prod_warehouse_Tor"."pw_prod_id")
AND ("v_products"."pr_prodline_id"="prod_warehouse_Tor"."pw_prodline_id")
AND "prod_warehouse_Tor"."pw_whs_id"='01TOR'

LEFT OUTER JOIN "sisl_data02"."dbo"."prod_warehouse" "prod_warehouse_PQ"
ON "prod_warehouse_PQ"."pw_prod_id"=  v_products.pr_prod_id
AND "prod_warehouse_PQ"."pw_prodline_id"=  v_products.pr_prodline_id
AND "prod_warehouse_PQ"."pw_whs_id"='01MON'

LEFT OUTER JOIN "sisl_data04"."dbo"."prod_warehouse" "prod_warehouse_EDM"
ON  "prod_warehouse_EDM"."pw_prod_id"=  v_products.pr_prod_id
AND "prod_warehouse_EDM"."pw_prodline_id"=v_products.pr_prodline_id
AND "prod_warehouse_EDM"."pw_whs_id"='01EDM'

LEFT OUTER JOIN "sisl_data09"."dbo"."prod_warehouse" "prod_warehouse_INSIZE"
ON "prod_warehouse_INSIZE"."pw_prod_id"= v_products.pr_prod_id
AND "prod_warehouse_INSIZE"."pw_prodline_id"= v_products.pr_prodline_id
AND "prod_warehouse_INSIZE"."pw_whs_id"='01INS'

LEFT OUTER JOIN "sisl_data09"."dbo"."v_products" "v_products_Insize"
ON "v_products_Insize"."pr_prod_id"= v_products.pr_prod_id AND "v_products_Insize"."pr_prodline_id"= v_products.pr_prodline_id

LEFT OUTER JOIN "sisl_data03"."dbo"."web_category_products" "web_category_products"
ON ("v_products"."pr_prodline_id"="web_category_products"."wcap_prodline_id")
AND ("v_products"."pr_prod_id"="web_category_products"."wcap_prod_id")

LEFT OUTER JOIN  "sisl_data03"."dbo"."web_category_master" "web_category_master" ON "web_category_products"."wcap_wct_wcm_id"="web_category_master"."wcm_id"

LEFT OUTER JOIN "sisl_data03"."dbo"."supp_udf_values" "supp_udf_values" ON
"V_products"."pr_prim_supplier_id"="supp_udf_values"."sudv_supplier_id"

[former_member292966 · Accepted solution]

Hi Norm,

Crystal will retrieve all the records from the child tables and download them to your local machine.  Depending on the number of records, it could be a lot of data.  Once it's downloaded, then it will attempt to do the filtering based on your query and then any filtering you do in the report.

What you are thinking of doing really will have an exponential hit on performance.  I had to use this method in the past because I wasn't given permissions to use a view.  As a result the report took almost an hour to run.  As a proof of concept to improve the performance, I got them to make a view for me and the same report ran in less than 30 seconds.

Crystal just wasn't designed to handle multi-threaded queries like this.  As a matter of principle, I do try to push as much of the processing on to the database because it can handle that with better efficiency than Crystal on your local machine.

Look into doing it through a view or stored procedure.  Your results really will be better.

Good luck,

Brian

[former_member183750 · Accepted solution]

Just a bit to add to Brian's answer. Note that a report built off of some x (x > 1) tables, cannot be pointed at a view or a stored proc and expected to work. E.g.; say x = 5. A view / SP is essentially seen as one table by the report engine and thus the report will fail to run.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[DellSC · Accepted solution]

Actually, if this is being done in a Command instead of with linked tables, it should work ok - provided the links between the databases have been configured.  If you were linking tables in Crystal, it would behave as Brian has outlined.  However, if you're using a Command, all of the SQL gets pushed to the database to be run there.

I would run the query in a query tool such as SQL Server Management Studio, Toad, etc. and make sure that it works correctly and then paste it into the Command editor.

NOTE:  If you're going to filter the data, DO NOT use the Select Expert!  Instead, use the Where clause and/or joins to filter the data.  Also, if you need to use parameters to filter the data, they MUST be created in the Command Editor in order to work.

-Dell

[Former Member · Accepted solution]

Thanks for all the replies.

There are a few records in each database (ballpark 95K in each). Because i didn't write the initial report and it already is a beast with the extra tables and record selection i think it's best for me to step back and start from scratch.

The pointers given above helped alot and there are no select expert variables so that issues won't apply to this report. Also with starting from the beginning i can try both as a view and also a non-view report.

I've been teaching myself crystal for close to 7 years now and this is only my 3rd attempt at a command type report. It's very helpful having this forum to get an understanding of what i can and can't do when approaching these issues.

Thanks again to all the replies and i'll let you know how it goes.

Norm

[Former Member]

Hi

I've been working on this, but i seem to be stumbling a lot so I started from scratch and found i can't get the unique items from the 2nd database to print in the report.

Am I missing something here or is the approach completely wrong?

1 attempt i thought of using a formula to move the prod_id and Prodline_id into a defined variable. This seemed to work okay, but it also generated a ton of blank lines.

This attempt will only generate a report for v_products and i can't get the unique/null items from the 2nd database to display

I did notice the expodential increase in records being read. v-products by itself was 94996 records. When i added these 2 joins it jumped to 1,800,000 records.

SELECT
v_products.pr_prod_id,
v_products.pr_prodline_id

FROM   sisl_data03.dbo.v_products

LEFT OUTER JOIN sisl_data03.dbo.prod_warehouse prod_warehouse_Tor
ON  v_products.pr_prod_id=prod_warehouse_Tor.pw_prod_id
AND v_products.pr_prodline_id=prod_warehouse_Tor.pw_prodline_id
AND prod_warehouse_Tor.pw_whs_id='01TOR'

LEFT OUTER JOIN sisl_data02.dbo.prod_warehouse prod_warehouse_PQ
ON  v_products.pr_prod_id=prod_warehouse_PQ.pw_prod_id
AND v_products.pr_prodline_id=prod_warehouse_PQ.pw_prodline_id
AND prod_warehouse_PQ.pw_whs_id='01MON'

[Former Member]

I found a solution to the original issue and i went from 92,000 records in data03 to 99,000 records from data02 + data034

SELECT "products"."pr_prod_id", "products"."pr_prodline_id"

FROM   "sisl_data03"."dbo"."products" "products"

UNION ALL

SELECT "products"."pr_prod_id", "products"."pr_prodline_id"

FROM   "sisl_data02"."dbo"."products" "products"

UNION ALL

SELECT "products"."pr_prod_id", "products"."pr_prodline_id"

FROM   "sisl_data04"."dbo"."products" "products"

UNION ALL

SELECT "products"."pr_prod_id", "products"."pr_prodline_id"

FROM   "sisl_data09"."dbo"."products" "products"