Show all records some missing

[jurassickarp]

I have a relational database running under ingresii
When I select records with fields from various tables if one of the fields is empty it will not show that record at
all.
How do I get around this, just because one field is missing data it will not show that record.

Any help would be appreciated.
Maybe some formula to treat null fields as a "N/A" or something.

Guppy

 

[Naith]

You've hit the nail on the head. You need to create a formula which basically says "if isnull{field} then 'N/A' else {field}".

 

[synapsevampire]

I don't believe that a null field will cause the row not to display, it will cause the field not to display.

You can select convert null fields to default in report options which will eliminate it as a null, but not your problem.

I think that your problem may be in the join, if you have an =, and there isn't a relationship, then it will appropriately not show anything for that row, which is correct.

Try changing the join to a Left Outter.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jurassickarp]

Hmm Right it did not work

Will try next idea

Any ideas are appreciated.

 

[synapsevampire]

Which didn't work?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Jurassic - Are you joining on this field?

if so then that is the reason for your problem...you have something like:

Table 1 Table 2
======= =======
field1 ====> FIELD1
field2 FIELD2
field3
field4

where you want all of table 1 fields even if FIELD1 is null...is this your situation??

Hopefully it is and life is simple and there is no filtering criteria on the Table 2 fields.

All you have to do in this case is make the Join from field1 to FIELD1 a "Left Outter Join"

Simply go to Database|Visual Linking Expert....right click on the link and change the join.

It gets more complicated if you have filtering criteria on Table 2.

Jim Broadbent

 

[jurassickarp]

Better still here is the query
as soon as there is no data in consent status and prov_diag it will not show that record at all.


SELECT DISTINCT
patient."surname", patient."given_name_1", patient."birth_date", patient."ur_number", patient."sex",
location."ward_code", location."bed_no",
episode."primary_dr",
lk_diagnosis."description",
ep_consent."consent_status"
FROM
"hospro"."patient" patient INNER JOIN "hospro"."episode" episode ON
patient."last_adm_ep" = episode."episode_no" INNER JOIN "hospro"."location" location ON
patient."patient_id" = location."cur_pat" INNER JOIN "hospro"."ep_details" ep_details ON
episode."episode_no" = ep_details."episode_no" AND
episode."patient_id" = ep_details."patient_id" INNER JOIN "hospro"."ep_prov_diag" ep_prov_diag ON
ep_details."episode_no" = ep_prov_diag."episode_no" INNER JOIN "hospro"."ep_consent" ep_consent ON
ep_prov_diag."episode_no" = ep_consent."episode_no" INNER JOIN "hospro"."lk_diagnosis" lk_diagnosis ON
ep_prov_diag."code" = lk_diagnosis."code"
WHERE
location."ward_code" = 'W3'
ORDER BY
location."ward_code" ASC,
location."bed_no" ASC

 

[Naith]

Your query shows that the problem is 100% with your joins. Change the join to ep_consent to OUTER JOIN, as Ngolem pointed out.

 

[jurassickarp]

Well back to work today after the weekend.
I tried this and all I got is errors.

Can you give me an idea of what this line of code should read like.
Should it work with Ingres?

 

[Naith]

Jurassickarp,

Ingres supports outer joins. When you change your joins the code you supplied should read exactly the same, except a couple of INNER JOIN areas should now read OUTER JOIN or LEFT OUTER JOIN.

What kind of errors are you getting?

 

[jurassickarp]

How does this look

I put 2 outer joins in one for ep_consent and one for ep_prov_diag

Are these the 2 correct ones.

I am having trouble comming to grips with outer joins and left outer joins
Which one do I use.

Am frantically search the web for an answer on this.

SELECT DISTINCT
patient."surname", patient."given_name_1", patient."birth_date", patient."ur_number", patient."sex",
location."ward_code", location."bed_no",
episode."primary_dr",
lk_diagnosis."description",
ep_consent."consent_status"
FROM
"hospro"."patient" patient INNER JOIN "hospro"."episode" episode ON
patient."last_adm_ep" = episode."episode_no" INNER JOIN "hospro"."location" location ON
patient."patient_id" = location."cur_pat" INNER JOIN "hospro"."ep_details" ep_details ON
episode."episode_no" = ep_details."episode_no" AND
episode."patient_id" = ep_details."patient_id" INNER JOIN "hospro"."ep_prov_diag" ep_prov_diag ON
ep_details."episode_no" = ep_prov_diag."episode_no" OUTER JOIN "hospro"."ep_consent" ep_consent ON
ep_prov_diag."episode_no" = ep_consent."episode_no" OUTER JOIN "hospro"."lk_diagnosis" lk_diagnosis ON
ep_prov_diag."code" = lk_diagnosis."code"
WHERE
location."ward_code" = 'W3'
ORDER BY
location."ward_code" ASC,
location."bed_no" ASC

 

[synapsevampire]

<blink>

&quot;I think that your problem may be in the join, if you have an =, and there isn't a relationship, then it will appropriately not show anything for that row, which is correct.

Try changing the join to a Left Outter.&quot;

OK, I had a typo in Outer, but otherwise it's probably the solution...

But you never reponded to my subsequent post.

Here's a generic SQL tutorial:

http://www.aspalliance.com/habal/sql/default.asp

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jurassickarp]

Thanks guys
It works
I put left outter in 2 places and bingo I got the results.

I will be honest and say I do not understand why but I will find out
Thanks will have a look at the asp link.

 

[rosemaryl]

Dear JurassicKarp,

Here is an explanation of joins that I use for my students.

Table A (Clients)
Client Id (Link Field)
Table B (Orders)
Client ID

Equal Join:
Join from Table A to Table B

You are telling Crystal, please show me all the records where Client ID's match in Table A and in Table B.

Clients with Orders!

Left Outer Join
Join From Table A to Table B

You are telling Crystal, please show me all the records in Table A and if there are matches in Table B, show those too!

All Clients, and any Client Orders.

Right Outer Join
Join From Table A to Table B

You are telling Crystal, show me all the records in Table B and if there are matches in Table A, show those too.

All Orders and any matching Clients. (This would be useful if you kept historical Order data forever, but deleted customers who placed no orders within 2 years say).

For more information on joins in Crystal, go to the Help Topics Index and search for Join Types. It has a rather exhaustive explanation.

I use a diagram in class to help people remember this:

To draw yourself a diagram, draw 2 circles that intersect.
Place an equals sign = in the part of the circles that intersects and shade it, place the word Left in the Left portion of the circle and the word right in the right hand circle.

L grabs Left side and intersection,
R Grabs Right side and intersection,
Equals grabs just the intersection.

Hope this was helpful,




ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.