Data missing on addition of another table

[mikeinfbg]

I've poured over this forum, and although there are similar threads to my question, I can't get it to work with my problem.

Tables:
PRODUCT (detail of things we sell by PLU#)
INVHEADER (top level info of an invoice)
INVDETAIL (detail by PRODUCT# of an invoice)
DEPT (department to which each PRODUCT# belongs)
DEL_PRODUCT (any PRODUCT that is deleted goes here)

I'm trying to run a report filtering by INVHEADER date and EMPLOYEE number, and DEPT number with the detail being INVDETAIL. If I don't include DEPT in the selection formula or GroupBy, then I get all invdetail for those dates and employees. Once I include DEPT, then it selects only INVDETAIL records that it finds in the PRODUCT table or DEL_PRODUCT table.
The problem is that there are a couple of INVDETAIL plu#s that are not in the PRODUCT table or DEL_PRODUCT table. Therefore they won't have a DEPT #. I want to include them on this report.

The selection formula I've tried is:
{INVHEADER.RegisterNo} in [3, 1] and
{INVHEADER.InvoiceTime} in DateTime (2008, 01, 07, 08, 00, 00) to DateTime
(2008, 01, 07, 23, 59, 00) and
{INVHEADER.EmployeeNo} <> 50 and
{INVHEADER.Type_Of_Invoice} in ["Sales", "Void"] and
isnull({PRODUCT.Dept}) or {PRODUCT.dept} in [25, 26, 20, 19, 18, 17, 16,
15, 14, 13, 12, 11, 10]

Thanks.

Mike

 

[kskid]

You were missing a pair of parenthesis

{INVHEADER.RegisterNo} in [3, 1] and
{INVHEADER.InvoiceTime} in DateTime (2008, 01, 07, 08, 00, 00) to DateTime
(2008, 01, 07, 23, 59, 00) and
{INVHEADER.EmployeeNo} <> 50 and
{INVHEADER.Type_Of_Invoice} in ["Sales", "Void"] and
(
isnull({PRODUCT.Dept}) or
{PRODUCT.dept} in [25, 26, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10]
)

 

[mikeinfbg]

I had mistyped that in my original post, so even using your correction it still isn't allowing PLUs from the INVDETAIL table if that PLU isn't in the product table or deleted product table and therefore not associated with the DEPT (which I am filtering for).
I understand how a record is not selected if a field within that table has a null value and that field is one of the criteria for selecting records, but if one is selecting records in one table based upon criteria in one or more other tables, I'm not sure where the NULL value comes in.
I'm wondering if this also isn't a JOIN issue.
Right now my joins are like this:
INVHEADER<<IJ<<INVDETAIL<<IJ<<PRODUCT<<IJ<<DEPT so all are inner joined right now.

 

[Turkbear]

Hi,
Anytime a IJ encounters a NULL ( or missing) value in any of the connected fields in the joined tables, it will not return any data for that record ( including those from tables in the join with data in that field..)

Change your joins to Left Outer and try again..



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mikeinfbg]

Still not working. I've worked all day at it. I'll try to summarize where I am. My joins are all Left Outer Joins.

INVHEADER<<LOJ<<INVDETAIL<<LOJ<<PRODUCT<<LOJ<<DEPT

The two tables that have the PLU field in them is INVDETAIL and PRODUCT. INVDETAIL will have records (PLUs) that are not in PRODUCT if the PRODUCT.PLU was deleted at some point after a sale. What I want to see are all the records in INVDETAIL where the DEPT.dept# is 1,2, or 3 (for ex.) AND any INVDETAIL.PLU that isn't in the PRODUCT table (regardless of DEPT# because it wouldn't have one because its not in the PRODUCT table which is linked to the DEPT table)

INVDETAIL.PLU PRODUCT.PLU DEPT.DEPT

REDSHIRT2 REDSHIRT2 4
REDSHIRT3
BLUESHIRT1 BLUESHIRT1 2
YELLOWSHIRT7 YELLOWSHIRT7 3

Given the way I've shown how my tables are linked above, if I filter for Depts 1,2,3 then I get BLUESHIRT1 & YELLOWSHIRT7. I also want BLUESHIRT3. It doesn't give that to me because it doesn't have a DEPT number.

Thanks for your help.

 

[lbass]

You cannot preserve the left outer joins if you select on any tables to the right of a left outer join--unless you do this in a command where you build the criteria into the joins in the 'from' clause. What version of CR are you using? You should always specify this. If you are using a version of 9 or above, please copy your current "show SQL query" into the thread.

-LB

 

[mikeinfbg]

VERSION: Crystal Reports XI Professional

SELECT "INVDETAIL"."FullPrice", "INVDETAIL"."Qty", "INVDETAIL"."Dates",
"INVDETAIL"."PLU", "INVDETAIL"."Cost", "INVDETAIL"."FullDesc",
"INVDETAIL"."LQty", "INVDETAIL"."ConsignmentAmt", "INVDETAIL"."ExtPrice",
"INVHEADER"."EmployeeNo", "INVHEADER"."InvoiceNo", "INVDETAIL"."Status",
"INVHEADER"."InvoiceTime", "INVHEADER"."RegisterNo",
"INVHEADER"."Type_Of_Invoice", "PRODUCT"."Dept"
FROM ("Comcash"."dbo"."INVHEADER" "INVHEADER" LEFT OUTER JOIN
"Comcash"."dbo"."INVDETAIL" "INVDETAIL" ON
("INVHEADER"."InvoiceNo"="INVDETAIL"."InvoiceNo") AND
("INVHEADER"."LocationNo"="INVDETAIL"."LocationNo")) LEFT OUTER JOIN
"Comcash"."dbo"."PRODUCTAll" "PRODUCT" ON
("INVDETAIL"."PLU"="PRODUCT"."PLU") AND
("INVDETAIL"."LocationNo"="PRODUCT"."LocationNo")
WHERE ("INVHEADER"."InvoiceTime">={ts '2008-01-07 08:00:00'} AND
"INVHEADER"."InvoiceTime"<{ts '2008-01-07 23:59:01'}) AND
("INVHEADER"."RegisterNo"=1 OR "INVHEADER"."RegisterNo"=3) AND
"INVHEADER"."EmployeeNo"<>50 AND ("INVHEADER"."Type_Of_Invoice"='Sales' OR
"INVHEADER"."Type_Of_Invoice"='Void') AND (("PRODUCT"."Dept"=10 OR
"PRODUCT"."Dept"=11 OR "PRODUCT"."Dept"=12 OR "PRODUCT"."Dept"=13 OR
"PRODUCT"."Dept"=14 OR "PRODUCT"."Dept"=15 OR "PRODUCT"."Dept"=16 OR
"PRODUCT"."Dept"=17 OR "PRODUCT"."Dept"=18 OR "PRODUCT"."Dept"=19 OR
"PRODUCT"."Dept"=20 OR "PRODUCT"."Dept"=25 OR "PRODUCT"."Dept"=26) OR
"PRODUCT"."Dept" IS NULL )

Thanks.

 

[Turkbear]

Since you are using the DEPT table in your criteria, the JOIN becomes EQUI not LEFT as lbass points out.

Try using DEPT as the leftmost table in the join.

DEPT<<LOJ<<INVHEADER<<LOJ<<INVDETAIL<<LOJ<<PRODUCT



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mikeinfbg]

LBASS & Turkbear: Sorry I haven't gotten back. I've been trying your suggestions for the last 7 days and have tried to figure it out on my own, but to no avail. I had another idea though.
INVDETAIL table contains all PLUs ever created AND sold. PRODUCT table contains only current PLUs. The ONLY PLU that could be on the PRODUCT table but not be in INVDETAIL would be an item that hasn't sold yet.
That being said, couldn't I write a selection formula that says: If PRODUCT.plu is not NULL then filter by the criteria in my first post discussed above. But that if PRODUCT.plu is NULL then filter by everything in my first post, but leave off the DEPT filter. This is what I tried, but its not working as of yet.
if not(isnull({PRODUCT.PLU})) then
{INVHEADER.InvoiceTime} in DateTime (2008, 01, 07, 08, 00, 00) to DateTime (2008, 01, 07, 23, 59, 00) and
{INVHEADER.RegisterNo} in [3, 1] and
{INVHEADER.EmployeeNo} <> 50 and
{INVHEADER.Type_Of_Invoice} in ["Sales", "Void"] and
({PRODUCT.Dept} in [25, 26, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10]) ELSE

{INVHEADER.InvoiceTime} in DateTime (2008, 01, 07, 08, 00, 00) to DateTime (2008, 01, 07, 23, 59, 00) and
{INVHEADER.RegisterNo} in [3, 1] and
{INVHEADER.EmployeeNo} <> 50 and
{INVHEADER.Type_Of_Invoice} in ["Sales", "Void"]

I've also dropped the DEPT table and I am relying on the PRODUCT.dept for dept#. This was to alleviate your previous comments. Any help would be appreciated.
Thanks.

 

[lbass]

Your selection on the Product.Dept field undoes the left outer joins. Try the following as a command to use as your only datasource for the report:

SELECT "INVDETAIL"."FullPrice", "INVDETAIL"."Qty", "INVDETAIL"."Dates",
"INVDETAIL"."PLU", "INVDETAIL"."Cost", "INVDETAIL"."FullDesc",
"INVDETAIL"."LQty", "INVDETAIL"."ConsignmentAmt", "INVDETAIL"."ExtPrice",
"INVHEADER"."EmployeeNo", "INVHEADER"."InvoiceNo", "INVDETAIL"."Status",
"INVHEADER"."InvoiceTime", "INVHEADER"."RegisterNo",
"INVHEADER"."Type_Of_Invoice", "PRODUCT"."Dept"
FROM ("Comcash"."dbo"."INVHEADER" "INVHEADER" LEFT OUTER JOIN
"Comcash"."dbo"."INVDETAIL" "INVDETAIL" ON
("INVHEADER"."InvoiceNo"="INVDETAIL"."InvoiceNo") AND
("INVHEADER"."LocationNo"="INVDETAIL"."LocationNo") and
("INVHEADER"."InvoiceTime">={ts '2008-01-07 08:00:00'} AND
"INVHEADER"."InvoiceTime"<{ts '2008-01-07 23:59:01'}) AND
("INVHEADER"."RegisterNo"=1 OR "INVHEADER"."RegisterNo"=3) AND
"INVHEADER"."EmployeeNo"<>50 AND ("INVHEADER"."Type_Of_Invoice"='Sales' OR
"INVHEADER"."Type_Of_Invoice"='Void')
)

LEFT OUTER JOIN
"Comcash"."dbo"."PRODUCTAll" "PRODUCT" ON
("INVDETAIL"."PLU"="PRODUCT"."PLU") AND
("INVDETAIL"."LocationNo"="PRODUCT"."LocationNo") AND
(
("PRODUCT"."Dept"=10 OR
"PRODUCT"."Dept"=11 OR
"PRODUCT"."Dept"=12 OR
"PRODUCT"."Dept"=13 OR
"PRODUCT"."Dept"=14 OR
"PRODUCT"."Dept"=15 OR
"PRODUCT"."Dept"=16 OR
"PRODUCT"."Dept"=17 OR
"PRODUCT"."Dept"=18 OR
"PRODUCT"."Dept"=19 OR
"PRODUCT"."Dept"=20 OR
"PRODUCT"."Dept"=25 OR
"PRODUCT"."Dept"=26
)OR
"PRODUCT"."Dept" IS NULL
)

-LB

 

[mikeinfbg]

This was what finally worked, just to close this thread.

PRODUCT<<LOJ<<INVDETAIL<<IJ<<INVHEADER

I turned off "Convert Database NULL values to Default" and my SQL query was:

SELECT "INVDETAIL"."FullPrice", "INVDETAIL"."Qty", "INVDETAIL"."Dates",
"INVDETAIL"."PLU", "INVDETAIL"."Cost", "INVDETAIL"."FullDesc",
"INVDETAIL"."LQty", "INVDETAIL"."ConsignmentAmt", "INVDETAIL"."ExtPrice",
"INVHEADER"."EmployeeNo", "INVHEADER"."InvoiceNo", "INVDETAIL"."Status",
"INVHEADER"."InvoiceTime", "INVHEADER"."RegisterNo",
"INVHEADER"."Type_Of_Invoice", "PRODUCT"."Dept"
FROM ("Comcash"."dbo"."INVHEADER" "INVHEADER" INNER JOIN
"Comcash"."dbo"."INVDETAIL" "INVDETAIL" ON
("INVHEADER"."InvoiceNo"="INVDETAIL"."InvoiceNo") AND
("INVHEADER"."LocationNo"="INVDETAIL"."LocationNo")) LEFT OUTER JOIN
"Comcash"."dbo"."PRODUCTAll" "PRODUCT" ON
("INVDETAIL"."PLU"="PRODUCT"."PLU") AND
("INVDETAIL"."LocationNo"="PRODUCT"."LocationNo")
WHERE ("INVHEADER"."RegisterNo"=1 OR "INVHEADER"."RegisterNo"=3) AND
("INVHEADER"."InvoiceTime">={ts '2008-01-07 08:00:00'} AND
"INVHEADER"."InvoiceTime"<{ts '2008-01-07 23:59:01'}) AND
"INVHEADER"."EmployeeNo"<>50 AND ("INVHEADER"."Type_Of_Invoice"='Sales' OR
"INVHEADER"."Type_Of_Invoice"='Void') AND ("PRODUCT"."Dept" IS NULL OR
("PRODUCT"."Dept"=10 OR "PRODUCT"."Dept"=11 OR "PRODUCT"."Dept"=12 OR
"PRODUCT"."Dept"=13 OR "PRODUCT"."Dept"=14 OR "PRODUCT"."Dept"=15 OR
"PRODUCT"."Dept"=16 OR "PRODUCT"."Dept"=17 OR "PRODUCT"."Dept"=18 OR
"PRODUCT"."Dept"=19 OR "PRODUCT"."Dept"=20 OR "PRODUCT"."Dept"=25 OR
"PRODUCT"."Dept"=26))

Thanks for your help.

Mike