| Author |
Message |
davecove
Newbie
Joined: 18 Apr 2008
Location: United States
Online Status: Offline
Posts: 2
|
  
Topic: Showing records with null fields
Posted: 18 Apr 2008 at 10:25am |
|
I have a simple report here that joins about 5 tables. One of the tables is a details table that may or may not have an entry for every record in the other 4. The report only shows records that have an entry in all 5 tables. That is, it will not show a row that has no detail record in the 5th table.
How do I get CR to show a row even if one of the fields will be blank? It seems to show only fully populated rows.
Dave
|
 IP Logged |
|
|
|
fusion
Groupie
Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
|
Posted: 18 Apr 2008 at 11:25am |
You can use a left outer join to join the table which have null values.
You can also use the report option under FILE - Report Options- Convert Database Null Values to default. Edited by fusion - 18 Apr 2008 at 11:25am
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 18 Apr 2008 at 1:31pm |
Converting Null Values to default only works on the data actually being displayed, it does nothing to the SQL that's generated to pull data for the report.
So, you'll have to use an outer join. To do this, go to the Database Expert and look at the links between your tables. Select the link to the Details table, right-click on it and select "Link Options". "Left Outer Join" should be one of the available options.
-Dell
|
|
|
| IP Logged |
|
davecove
Newbie
Joined: 18 Apr 2008
Location: United States
Online Status: Offline
Posts: 2
|
Posted: 21 Apr 2008 at 11:02am |
Ok... I have done both the 'convert nulls' and the left outer join to the table that may not have an entry for every row in the other joins. When I run the report, I get 82 rows when I include only fields from the 'complete' joined tables. When I drop on a field from an outer left join table, 11 records drop off the report.
A bit more detail:
This is a customer db. Tables hold customer details, product rates details, customer x product matchup details, and product rate over-rides. All customer will have a customer record and a customer x product matchup record, but not always a product rate override record.
What I want is a report that shows fields from all 4 tables. I can get one with detail from 3 of the tables (all customers have records in all 3 tables), but when I drop in a field from that 4th table, only customers with an entry in all 4 tables shows up. Those without an over-ride disappear from the report. I have tried the left outer join on the override table to no effect.
I am pretty sure the records are coming back becuase the appear in the report with the 4th table joined, they just disappear when I drop a field from the 4th table onto the report.
Dave
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 21 Apr 2008 at 1:40pm |
Can you read SQL?
If it were me, I would go to the Database menu and select "Show SQL Query". Compare what's there under both conditions - with and without the field from the 4th table to see where the issue is.
If you need some help with this, post both SQL's and I'll take a look at them.
-Dell
|
|
|
| IP Logged |
|
schr8r154
Newbie
Joined: 10 Jul 2008
Online Status: Offline
Posts: 2
|
Posted: 10 Jul 2008 at 7:22am |
|
Hi I am having the same exact issue. When I add an additional field, in this case Decision Tree Complete it chops out a bunch of records. Nearly 300 out of 2400 are taken away. I would like to see those fields where the decision tree is incomplete but is not an option and does not show up null. Here is my SQL when I do not add the field.
SELECT "PR"."ID", "TW_V_FACILITATING_ENTITY"."S_VALUE", "TW_V_INITIATED_DATE"."DATE_VALUE", "PR"."IS_CLOSED", "PROJECT"."NAME"
FROM ("CH_ADMIN"."TW_V_FACILITATING_ENTITY" "TW_V_FACILITATING_ENTITY" LEFT OUTER JOIN ("CH_ADMIN"."TW_V_INITIATED_DATE" "TW_V_INITIATED_DATE" LEFT OUTER JOIN "CH_ADMIN"."PR" "PR" ON "TW_V_INITIATED_DATE"."PR_ID"="PR"."ID") ON "TW_V_FACILITATING_ENTITY"."PR_ID"="PR"."ID") INNER JOIN "CH_ADMIN"."PROJECT" "PROJECT" ON "PR"."PROJECT_ID"="PROJECT"."ID"
WHERE "TW_V_INITIATED_DATE"."DATE_VALUE">={ts '2005-08-31 16:06:20'} AND ("TW_V_FACILITATING_ENTITY"."S_VALUE"='2930' OR "TW_V_FACILITATING_ENTITY"."S_VALUE"='2950') AND "PR"."IS_CLOSED"<>1 AND "PROJECT"."NAME"='Complaint'
ORDER BY "TW_V_INITIATED_DATE"."DATE_VALUE"
and here is the SQL with the field added
SELECT "PR"."ID", "TW_V_FACILITATING_ENTITY"."S_VALUE", "TW_V_INITIATED_DATE"."DATE_VALUE", "PR"."IS_CLOSED", "PROJECT"."NAME", "TW_V_DECISION_TREE_COMPLETE"."S_VALUE"
FROM ("CH_ADMIN"."TW_V_FACILITATING_ENTITY" "TW_V_FACILITATING_ENTITY" LEFT OUTER JOIN ("CH_ADMIN"."TW_V_DECISION_TREE_COMPLETE" "TW_V_DECISION_TREE_COMPLETE" LEFT OUTER JOIN ("CH_ADMIN"."TW_V_INITIATED_DATE" "TW_V_INITIATED_DATE" LEFT OUTER JOIN "CH_ADMIN"."PR" "PR" ON "TW_V_INITIATED_DATE"."PR_ID"="PR"."ID") ON "TW_V_DECISION_TREE_COMPLETE"."PR_ID"="PR"."ID") ON "TW_V_FACILITATING_ENTITY"."PR_ID"="PR"."ID") INNER JOIN "CH_ADMIN"."PROJECT" "PROJECT" ON "PR"."PROJECT_ID"="PROJECT"."ID"
WHERE "TW_V_INITIATED_DATE"."DATE_VALUE">={ts '2005-08-31 16:06:20'} AND ("TW_V_FACILITATING_ENTITY"."S_VALUE"='2930' OR "TW_V_FACILITATING_ENTITY"."S_VALUE"='2950') AND "PR"."IS_CLOSED"<>1 AND "PROJECT"."NAME"='Complaint'
ORDER BY "TW_V_INITIATED_DATE"."DATE_VALUE"
Once the field is deleted it returns to the full records.
Any idea on how to get it to show the missing records?
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 10 Jul 2008 at 7:40am |
I think the problem may be in your SQL. I would change it like this:
FROM "CH_ADMIN"."PR" "PR"
LEFT OUTER JOIN
"CH_ADMIN"."TW_V_FACILITATING_ENTITY" "TW_V_FACILITATING_ENTITY" ON "TW_V_FACILITATING_ENTITY"."PR_ID"="PR"."ID"
LEFT OUTER JOIN "CH_ADMIN"."TW_V_DECISION_TREE_COMPLETE" "TW_V_DECISION_TREE_COMPLETE" ON "TW_V_DECISION_TREE_COMPLETE"."PR_ID"="PR"."ID"
LEFT OUTER JOIN "CH_ADMIN"."TW_V_INITIATED_DATE" "TW_V_INITIATED_DATE" ON "TW_V_INITIATED_DATE"."PR_ID"="PR"."ID"
INNER JOIN "CH_ADMIN"."PROJECT" "PROJECT" ON "PROJECT"."PROJECT_ID"="PR"."ID"
-Dell
|
|
|
| IP Logged |
|
schr8r154
Newbie
Joined: 10 Jul 2008
Online Status: Offline
Posts: 2
|
Posted: 10 Jul 2008 at 8:26am |
|
How would I go about doing this? Do I just copy and paste that formula somewhere? Thanks so much for helping out.
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 10 Jul 2008 at 9:55am |
Ahhh...I assumed you were using a command rather than linking tables. Here's how you might be able to fix this.
Delete all of the links between the tables. Put the PR table first. Link FROM it to TW_V_FACILITATING_ENTITY, TW_V_DECISION_TREE_COMPLETE, TW_V_INITIATED_DATE and PROJECT. Make the first three links left outer joins.
I suspect that currently you're linking from TW_V_FACILITATING_ENTITY to PR and from there to the other tables.
-Dell
|
|
|
| IP Logged |
|
|
|
