Print Page | Close Window

Problem with join?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13640
Printed Date: 04 May 2024 at 10:24am


Topic: Problem with join?
Posted By: tonywright
Subject: Problem with join?
Date 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


Replies:
Posted By: DBlank
Date 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?

 

Posted By: tonywright
Date 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

Posted By: DBlank
Date 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'
 

Posted By: tonywright
Date Posted: 30 Jun 2011 at 4:12am
That fixed it,
 
thanks for the help
 
tony

Posted By: karenc
Date Posted: 06 Jul 2011 at 6:04am
tony -

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

KC

Posted By: DBlank
Date Posted: 06 Jul 2011 at 6:32am
I am guessing he used the isnull select statement option

Posted By: karenc
Date 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!

Posted By: DBlank
Date 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)

Posted By: karenc
Date 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

Posted By: DBlank
Date Posted: 06 Jul 2011 at 7:33am
so you have no select statement at all (like invoice date > last week)?

Posted By: DBlank
Date Posted: 06 Jul 2011 at 11:40am
i ask because it sounds like you have it set up correctly but I have seen people make the their outer join into an inner join buy including a select statement that messes it up (excludes outer part rows).
Also I assume you are displaying the names using data from the table of customers not invoices.
Do you have any suppression criteria re: invoice results?

Posted By: karenc
Date Posted: 20 Jul 2011 at 8:00am
Sorry, I was out for a while...but still could use your help!!

I do have the following...

{Organizations.cmoResellerOrganizationID} <> '""' and
{@Commissionable} and
{PartGroups.UIMUDISTRIBUTORCOMMISSIONTYPE} = -1.00

Organizations is the left table of the outer join. And yes I an using the names from that table!

@comissionable is a field to make sure the invoice (right part of join) is within a certain date range.

Thanks for all your help!

Posted By: karenc
Date Posted: 20 Jul 2011 at 8:02am
I meant to say that the
{Organizations.cmoResellerOrganizationID} <> '""' and
{@Commissionable} and
{PartGroups.UIMUDISTRIBUTORCOMMISSIONTYPE} = -1.00

is in the select expert. If I am screwing up my outer join with the organization table being the left part of outer join AND in the above select...what are my options?


Print Page | Close Window