Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Problem with join? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
tonywright
Newbie
Newbie
Avatar

Joined: 30 Jun 2011
Online Status: Offline
Posts: 3
Quote tonywright Replybullet Topic: Problem with join?
    Posted: 30 Jun 2011 at 3:31am
I have the following sql from a report in crystal v8
 
SELECT
    v_Member."OrganisationRef", v_Member."MembershipNo",
    v_Member_1."OrganisationRef", v_Member_1."FeepayingMember", v_Member_1."MembershipNo", v_Member_1."MemberStatus", v_Member_1."MemberGrade", v_Member_1."Feepayer", v_Member_1."AccountName", v_Member_1."InvAddress1", v_Member_1."InvTown", v_Member_1."InvPostCode"
FROM
    { oj "Members"."dbo"."v_Member" v_Member LEFT OUTER JOIN "Members"."dbo"."v_Member" v_Member_1 ON
        v_Member."MemberRef" = v_Member_1."FeepayingMemberRef"}
WHERE
    (v_Member."MembershipNo" = '7200015305' OR
    v_Member_1."MembershipNo" = '7200015305')
ORDER BY
    v_Member_1."OrganisationRef" ASC
 
This sql works fine in enterprise manager if I paste it into a query and returns 5 rows.
 
However in crytal it returns 4 rows unless I take out the second of the where clauses (in red)  in which case it returns the other 1 row
(The four are from the one table(v_Member_1) the single row is from the other(v_Member))
 
Can anyone see why this doesn't work?
 
Thanks
 
Tony
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Jun 2011 at 3:35am
My first guess is that it is omitting based on a NULL issue.

when you run it in crystal are you using it as a command?

 
IP IP Logged
tonywright
Newbie
Newbie
Avatar

Joined: 30 Jun 2011
Online Status: Offline
Posts: 3
Quote tonywright Replybullet Posted: 30 Jun 2011 at 3:43am
No not as a command, I built the report using the linking wizard and got the sql code via the "Show SQL Query" in the database menu.
 
I thought the left outer join should get rid of the null problem, it should bring back results from table1 even if there are not any matching rows in table2
 
Is there a way of telling crystal to use "isnull" for the fields (or join)?
 
I'm not sure why it works in SQLEM and not in crystal.
Thanks
 
Tony
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Jun 2011 at 3:48am
I don't use v8 but crystal does not inherently handle NULLS the same way as SQl does.
If you change the report settings to use defualt values for NULLs it should work
or
you have to write your select expert to handle them. In some cases this is a real pain and in other it is helpful. You just have to know about it or it will confuse the heck out of you.
so your select statment probably looks like
 
{v_Member.MembershipNo} = '7200015305' OR
{v_Member_1."MembershipNo} = '7200015305'
 
try
 
(isnull({v_Member.MembershipNo}) and {v_Member_1."MembershipNo} = '7200015305')
or
{v_Member.MembershipNo} = '7200015305'
 


Edited by DBlank - 30 Jun 2011 at 3:50am
IP IP Logged
tonywright
Newbie
Newbie
Avatar

Joined: 30 Jun 2011
Online Status: Offline
Posts: 3
Quote tonywright Replybullet Posted: 30 Jun 2011 at 4:12am
That fixed it,
 
thanks for the help
 
tony
IP IP Logged
karenc
Newbie
Newbie


Joined: 06 Jul 2011
Location: United States
Online Status: Offline
Posts: 5
Quote karenc Replybullet Posted: 06 Jul 2011 at 6:04am
tony -

I am having the exact same problem. What fixed it? Thanks for your help!

KC
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Jul 2011 at 6:32am
I am guessing he used the isnull select statement option
IP IP Logged
karenc
Newbie
Newbie


Joined: 06 Jul 2011
Location: United States
Online Status: Offline
Posts: 5
Quote karenc Replybullet Posted: 06 Jul 2011 at 7:07am
but if you are using the database expert "links" to create your SQL...where and how do you add that isnull statement.

When I view SQL - it is not edit-able.

Thanks for your help!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Jul 2011 at 7:09am
it is not the join but rather the select expert (which creates the where portion of the SQL query)
IP IP Logged
karenc
Newbie
Newbie


Joined: 06 Jul 2011
Location: United States
Online Status: Offline
Posts: 5
Quote karenc Replybullet Posted: 06 Jul 2011 at 7:30am
Okay - I found the select expert...

Here is my scenario...

I have a customer table and an invoice table. I join the two tables with customer id. I want the crystal report report to print out ALL of my customers regardless of invoices. I summarized a field on the invoice table. Printing the Customer and sum of all invoices. Those customers with invoices works great - and prints the total. Those customers with NO invoices don't show up on the report at all. I would like to see them with an invoice total of : $0.00.

I joined the two tables with a left outer join. customers being the left table. I "enforced both" and used "=" on the links....

It only returns those customers who have any invoice records.


Thanks for your help!! I greatly appreciate it!
Karen
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.015 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.