| Author |
Message |
Minco
Groupie
Joined: 28 Nov 2012
Location: United States
Online Status: Offline
Posts: 62
|
  
Topic: The more fields I add, the more records I loose
Posted: 23 Aug 2013 at 10:27am |
I have a table of Opportunities OPPOR and it lists potential new business, call dates, the activity code, activity description, and a note field [among many others] I also have a miscellaneous table ATEXTRA that holds the activity code and the description I need. The OPPOR has 15 fields STE_0, STE_1, STE_2, STE_3 etc.... which link to the activity code in ATEXTRA, so I have brought in ATEXTRA 6 times [so far] and renamed the tables ATEXTRA_0, ATEXTRA_1, ATEXTRA_2, etc, to link on each STE field. These are all left outer join links. From the OPPOR table, I've put the opportunity number, the name, the rep involved, the estimated annual income and the date it opened. Now I am listing each STE [step] that has been completed and the description from the ATEXTRA table.
I started out great... all of my opportunities were being listed on the report, along with the first step and the description of the first step. As I add the descriptions from the 2nd, 3rd, 4th, and 5th tables, I start to loose opportunities that do not have those steps accomplished yet. I think it's a NULL problem? Not sure...
After adding the table ATEXTRA 6 times and linking to the STE_6 field in OPPOR - my results are only those opportunties in which at least 6 steps have been completed in OPPOR.
I stopped there - until I figure this out because I have 9 more steps to list.
Any help?
Minco
|
|
Be kind to those less fortunate.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Aug 2013 at 11:21am |
are you using any select statement?
Sometimes the way that is written can alter a outer join into havingt he same result as an inner join.
|
| IP Logged |
|
Minco
Groupie
Joined: 28 Nov 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 23 Aug 2013 at 11:29am |
My select statement [for each ATEXTRA field] lists the miscellaneous table number 400, the language, etc., in order to reach the correct misc table.
{ATEXTRA_0.CODFIC_0} = "ATABDIV" and
{ATEXTRA_0.ZONE_0} = "LNGDES" and
{ATEXTRA_0.IDENT1_0} = "400" and
{ATEXTRA_0.LANGUE_0} = "ENG"
I have the report grouped by rep, and then by OPPOR number so all the steps list under the correct number - these fields are in the group header and the detail is suppressed.
|
|
Be kind to those less fortunate.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Aug 2013 at 11:38am |
that will do it.
the select statment is applied after the join not during.
Can you write a command object or use a view or stored proc for your data source. YOu can move your where clause into the join then so it is applied at the smae time the join is not after.
|
| IP Logged |
|
Minco
Groupie
Joined: 28 Nov 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 25 Aug 2013 at 8:13am |
|
I'll talk to IT and see if he can fix it. Will reply and let you know our results. THanks much for the tip!
|
|
Be kind to those less fortunate.
|
| IP Logged |
|
Minco
Groupie
Joined: 28 Nov 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 26 Aug 2013 at 8:15am |
Well, IT built a view for me which contains the opportunity number, the step codes and descriptions so I bring in the view once, and not the ATEXTRA file 6 times. I'm still getting results where only the opportunities with 6 or more steps in them, appear on the report. I need to see the ones that may only have 1 or 2 steps in them as well. Any other suggestions?
|
|
Be kind to those less fortunate.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 26 Aug 2013 at 8:26am |
How did they build it?
just putting a where clause at the end of the whole view or stored proc does the same thing crystal would have done. They have to write it to put the condition on the joins.
Can you post the query they wrote for you?
Edited by DBlank - 26 Aug 2013 at 8:29am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 26 Aug 2013 at 8:30am |
To test the general theory of my finding your issue out , in your original report, take your select statment out and see if the lef tjoins work to get all of your rows. I know it will have abunch of extra stuff too, but just want to make sure that the data you want starts to appear (with all the ones you are trying to filter out).
|
| IP Logged |
|
Minco
Groupie
Joined: 28 Nov 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 26 Aug 2013 at 8:35am |
|
With no select statement, I have 98 records [opportuntities] - as I add the step fields and their descriptions, I start to loose opportunities. Now, at step 6, I am down to 13, but I'm only 1/2 way through adding steps to display... soon I'll have no records at all.
|
|
Be kind to those less fortunate.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 26 Aug 2013 at 8:50am |
|
can you post your SQL from the crystal report using your original tables?
|
| IP Logged |
|
|
|
