| Discoverer 4.1 - data display question [message #136181] |
Tue, 06 September 2005 09:30  |
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
|
Junior Member |
|
|
The setup:
2 sets of tables - 1 set of tables containing data for somebody (name table, address table, other information table, the other set of tables are for their dependents (name table, other information table) that may or may not be on the tables - (they may not have a dependent.) Each of the tables has a unique number connecting them together. One of the other information tables for each of the sets can contain more than 1 record and I have conditions figuring out which one to pull the information from.
The problem:
I am getting the sets of records that have dependents, not the records that have no dependents. I would like to display the first set of table information and have the dependent information for these records to be blank.
I think this is because of the Layout tab, because it shows the dependent information on it.
Is there a way to show all the information for both kinds of sets of data?
Thanks!
|
|
|
|
| Re: Discoverer 4.1 - data display question [message #137454 is a reply to message #136181] |
Thu, 15 September 2005 05:23  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi,
Did you use an outer join in the EUL for those sets of table? (edit join, properties, outer join on detail). If so, you should be able to have blank cells for those rows that have no dependent data.
Regards,
Sabine
|
|
|
|
| Re: Discoverer 4.1 - data display question [message #137620 is a reply to message #137454] |
Thu, 15 September 2005 14:06 |
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
|
Junior Member |
|
|
I tried what you said to do, but I'm still only getting ones that have dependent information, too. The enrollees that have information for themselves only are still being left out. Unless I have my joins wrong.
The tables I have are and enrollee table, address table and insurance coverage table (Has lots of entries and have conditions listed to pick out the rows I want). There is a Dependent table, and dependent coverages information.
I've joined a unique account number between the following:
Enrollee and address - one to many, always on master (Enrollee)
enrollee and insurance coverage - one to many, always on master
enrollee and dependent - outer join, one to many always on master
dependent and dependent insurance coverage outer join - one to many, always on master
Any ideas??
Thanks,
Old Dog
|
|
|
|
| Re: Discoverer 4.1 - data display question [message #137679 is a reply to message #137620] |
Fri, 16 September 2005 01:57 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Old Dog,
The joins look okay to me. I assume you checked that the master and detail folders (tables) are correctly defined?
One thing that puzzles me though: is there a separate table for dependent coverage? Because it would be more "traditional" to have one coverage table to hold both the enrollee-coverage and the dependent-coverage. If the coverage table is in fact for both of them, that could be the cause of the problem.
If you just select enrollee, address and dependents, does it work then? (if so, than the problem is in the coverage indeed).
Regards,
Sabine
|
|
|
|
| Re: Discoverer 4.1 - data display question [message #137738 is a reply to message #137679] |
Fri, 16 September 2005 06:59 |
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
|
Junior Member |
|
|
Yes, when I just go after the enrollee, address and dependent only, it comes up just fine.
I think the tables are defined ok. They've been used for quite a while. This is just a new report using existing information.
The dependents have their own table containing coverage information separate from the participant since stop and start dates for coverage can be different from the participants.
If you come up with any more ideas, I'm open to just about anything.
I really appreciate your help with this.
Thanks a lot,
Old Dog
|
|
|
|
| Re: Discoverer 4.1 - data display question [message #137835 is a reply to message #137738] |
Sat, 17 September 2005 06:32 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi,
Okay, then the problem is in (the joins with) the coverage tables...
Do you know how to read "plain SQL"? If so, you should experiment with various combinations of the tables and everytime check the SQL statement Discoverer has made of it (while the worksheet is open, in the menu choose view, SQL inspector).
These statements can be quite hard to read, but look for the outerjoin signs (+), it should be on the side that you expect the nulls to show up.
So, it should be something like
...
where
...
and <column in enrollee table> = <column in dependent table> (+)
and <column in dependent table> = <column in dependent coverage table> (+)
...
If the (+) signs are missing or in the wrong place, something is wrong with the joins.
Regards,
Sabine
PS Discoverer has the habit of coding everything like i12345 etc., so maybe you have to do quite some "decoding" to find the actual column names it's joining.
|
|
|
|
| Re: Discoverer 4.1 - data display question [message #138164 is a reply to message #137835] |
Tue, 20 September 2005 09:20 |
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
|
Junior Member |
|
|
Great News, I'm one step closer.
I was able to display ALL the enrollees and their current coverage information and the dependents for the enrollees that have them, and ALL their coverage information. I'm SO close now!! Thanks!!!
The last problem I have is I need to pick out only the most current coverages information for the dependents and that is determined by checking the values of about a half dozen fields on each of the rows in that table. I don't want to display ALL of the coverage information rows, just the CURRENT information rows.
When I put in a condition checking these fields for that I normally would, the report loses the enrollee information that has no dependents again. Is there someway to check these fields to pull only the current information WITHOUT losing the single coverage enrollees again?
I really, Really appreciate your help, Skooman!
Old Dog
|
|
|
|
| Re: Discoverer 4.1 - data display question [message #138586 is a reply to message #138164] |
Thu, 22 September 2005 09:59 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Glad I can help...
Now for the last piece, the condition on current. The reason you loose the "single enrollees" (I mean the ones without any dependent data) when you add a condition is this:
- you want data X and find data Y that comes with, if any
- now you create a condition "where data Y = Z"
Since data Y can not be null ("if any") and equal to Z at the same time, therefor the nulls disappear...
So, what we actually want is a condition like "where data Y = Z or data Y is null". Discoverer does that for you and in SQL that looks like:
...
where <Y> (+) = <something-that-makes-the-data-current>
...
And again, experiment with some data and check the SQL statement everytime, verify that the (+) sign is on the correct side of the statement.
Regards,
Sabine
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
