Query By Insanity 4

[elead]

I am having a time with a join and could use some help.
First the db is MSSQL 7.0.
Two tables, Customer, Customer Data.
I have the customer table left joined to the customer data table by account.

In my report I have one group which is customer.account
I am displaying all the customers along with some other data in the group footer.
The problem is that if there is no data in the Customer data table the customer.account does not show in the group...I think it should with a left join...any suggestions

 

[kenhamady]

Are you putting criteria on the outer table?
What is your selection formula? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Hattusas]

In outer joins (left outer or right),there is something you should consider.In the beyond outer table; that is to say the right table at left outer joins or the left table in right outer ones , if you add a selection criteria, the database engine will skip the left record in left outer joins if there are no records at the right table.To prevent this, in your selection criteria you should add the &quot;<field_name> is null or the selection you wrote&quot; syntax.If you omit &quot;field_name is null &quot; tokens, than there is no way to get your record on the left.Also the syntax &quot;your selection or <field_name> is null&quot; does not work either.In fact It should have worked but It didn't in my reports.That is the possible reason I can suggest .If it still doesn't work let me know.

 

[Ngolem]

Hattusas:

&quot;To prevent this, in your selection criteria you should add the &quot;<field_name> is null or the selection you wrote&quot; syntax.&quot;

could you provide a clear example....I have the same problem as described. If you put a selection criteria on a field in the left outter joined table and the field does not exist....my experience is that not only does the join not work but it kills the entire record Period....nothing is returned.

It is not clear to me the structure of the Select statement that you use....a concise example would be welcome.

Thanks Jim

 

[elead]

Here is the SQL Query--The only parameters it takes is ReportStart and ReportStop
It is a left join where tblCCCompany.szCompany=tblRaw.Account
******************************************************
SELECT
tblCCCompany.szCompany, tblCCCompany.lGroupID,
tblRaw.account, tblRaw.termcd, tblRaw.campaignid, tblRaw.entrydate, tblRaw.faxtype, tblRaw.q2
FROM
dbCCReports.dbo.tblCCCompany tblCCCompany,
dbCCReports.dbo.tblRaw tblRaw
WHERE
tblCCCompany.szCompany *= tblRaw.account AND
tblCCCompany.lGroupID = 1 AND
(tblRaw.campaignid = 'UPS' OR
tblRaw.campaignid = 'FUP') AND
(tblRaw.termcd <> 'XX' AND
tblRaw.termcd <> 'TR' AND
tblRaw.termcd <> 'DP') AND
tblRaw.entrydate >= &quot;Nov 12 2001 00:00:00AM&quot; AND
tblRaw.entrydate < &quot;Dec 13 2001 00:00:00AM&quot;
ORDER BY
tblCCCompany.szCompany ASC
**************************************************

Here is the record selection formula:

{tblRaw.campaignid} in [&quot;FUP&quot;, &quot;UPS&quot;] and
not ({tblRaw.termcd} in [&quot;DP&quot;, &quot;TR&quot;, &quot;XX&quot;]) and
{tblRaw.entrydate} in {?ReportStart} to {?ReportStop} and
{tblCCCompany.lGroupID} = 1

Thanks

 

[Ngolem]

Ok...I think I understand.

So instead of just specifying what the Values for tblRaw.termcd should be ...you should also specify what they should not be....interesting...I have never tried that.

But what if there is no record at all...I think the Join would then fail and the fields for tblCCCompany.szCompany, tblCCCompany.lGroupID would not be returned.

So I think it won't solve my problem particular problem (I need previous year and this year inventory info for products which fails when the product is brand new) but it may help in other situations...thanks for the Post
Jim

 

[Hattusas]

Sorry.I couldn't reply immediately.But the steps I have performed before will guide you.
First I created two sample tables.
LEVEL1
create table test.tablename1
(
key int not null primary key,
data varchar(20) not null);

create table test.tablename2
(key int not null,
record varchar(20) not null,
CONSTRAINT fk1 FOREIGN KEY (key) references test.tablename1);
LEVEL2 (Inserting sample records to table1 )
insert into test.tablename1(key,data)
values
(1,'DATA1'),
(2,'DATA2'),
(3,'DATA3'),
(4,'DATA4');
LEVEL3 (Inserting sample records to table2)
insert into test.tablename2 (key,record)
values
(1,'RECORD1'),
(1,'RECORD2'),
(2,'RECORD3'),
(4,'RECORD4') ;
Notice that I didn't insert a record referencing 3.So the record with primary key value &quot;3&quot; in table1 remains empty.
LEVEL4 (Crystal Report Part)
I called the fields table1.key,table1.data,table2.record...
If you equal join them,the record (3,'DATA3') in table1 is not visible...
LEVEL5
If you change the link option to left outer.Then you will be able to see all of the records...
LEVEL6
If you add a selection criteria to tablename2 then selection criteria works but we lose the record (3,'DATA3') in table1 again..
Here is my selection expert :
{TABLENAME2.RECORD} <> &quot;'RECORD1'&quot; etc.
LEVEL7
Now here is another trying...
I typed into selection expert part that syntax:
{TABLENAME2.RECORD} <> &quot;'RECORD1'&quot; OR isnull
({TABLENAME2.RECORD})
We can't see the record (3,'DATA3') in table1 again..
LEVEL8
Now here is FINAL trying...
I typed the selection expert part that syntax:
isnull({TABLENAME2.RECORD}) OR {TABLENAME2.RECORD}
<> &quot;'RECORD1'&quot;
AND THERE IT WORKS!!!
the record (3,'DATA3') in table1 is VISIBLE!

Hope I have been helpful to all of you.

 

[Ngolem]

So it is an order of operation problem??? I'll have to test that on my problem...you have solved a major problem for me if this works in my situation.

I'll let you know when I get a chance

Jim

 

[Ngolem]

Damn!....It works!!!!

Thank you Hattusas!!! I owe you a beer for that advice...Great post...You have saved me a lot of work.

 

[kenhamady]

Hattusas is correct in that you always have to test for null before you otherwise use the field in a formula. All formulas in CR choke if they hit a null value.

BUT, there may be one problem with this solution. You are assuming that the outer table always has one null record. In many cases there are matching records in the outer table, but none of them meet the criteria on the outer table.

Take a concrete example of customers and their orders. You want a report showing all customers - even those that don't have orders, so you do an outer join, Customers to Orders. Next, you you add a criteria that limits the orders (outer table) to a single month, using the criteria above. Your report will include:
1) all customers who have orders in that month
2) all customers who have no orders at all, ever.

But it will NOT include a customer who has all old orders. Their records are neither null nor in the current month. They will all be skipped, dropping that customer from the report. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

Point taken...as with any solution to a problem...one must be carefull not to let in new problems...

Having said that though Hattusas's solution is a major break through for me in one particular thorny report...

I had to report on a inventory of items...this year and the previous year.

I tried aliasing the previous year records but never could match them for New Products that had no previous year history...I had to make a subreport to do it....now I don't have to.

there are many other instances where this solution will work for me....Hattusas gets my cheers for an excellent example that solved a lot of my problems.

 

[Ngolem]

besides...if you wanted only records returned for customers that had orders in a given month....just make it an EQUAL join and you will get just those records back...if you want customers with orders in a given month And Customers who have never ordered put the left outter join on and you have them....

I think this covers all situations If you want all customers...outter join with no secondary criteria

 

[Kalipso]

Hattusas thanks for your hint for the left outer join tip.But there is something more to ask you.Do you mean to say the &quot;or&quot; function has a &quot;bug??&quot;

 

[kenhamady]

The problem is that when any formula evaluates a null value, it stops and returns the null value. That is why you must test for NULL using IsNull() before you use the field in the formula. That way the formula stops evaluating before it hits the null value. IsNull is the only function that can read a null value without choking. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com