Joins not working correct

[Kennymc]

I am using CR 9 and a sql 2000 database.

I have tried all join types, reversed links but still get the same number of records. There are rows in axapataoraclelines that do not have a corresponding row in inventtable. If I use Crystal SQl designer - I get all the rows

The query by CR9

SELECT "axaptaoraclelines"."invoicedate", "axaptaoraclelines"."invoiceid", "axaptaoraclelines"."itemid", "axaptaoraclelines"."lineamount", "INVENTTABLE"."DATAAREAID", "INVENTTABLE"."DIMENSION3_"
FROM {oj "axapta30sp1"."dbo"."axaptaoraclelines" "axaptaoraclelines" LEFT OUTER JOIN "axapta30sp1"."dbo"."INVENTTABLE" "INVENTTABLE" ON "axaptaoraclelines"."itemid"="INVENTTABLE"."ITEMID"}
WHERE "INVENTTABLE"."DATAAREAID"='win' AND "axaptaoraclelines"."invoicedate"<{ts '2004-01-30 00:00:01'}
ORDER BY "axaptaoraclelines"."itemid", "axaptaoraclelines"."invoiceid"

Design query
SELECT
axaptaoraclelines.invoicedate,
axaptaoraclelines.invoiceid,
axaptaoraclelines.itemid,
axaptaoraclelines.lineamount,
INVENTTABLE.DATAAREAID,
INVENTTABLE.DIMENSION3_
FROM
axaptaoraclelines axaptaoraclelines,
INVENTTABLE INVENTTABLE
Where
axaptaoraclelines.itemid *= INVENTTABLE.ITEMID
and
INVENTTABLE.DATAAREAID = 'win'
AND
axaptaoraclelines.invoicedate < '2004-01-30 00:00:01'
ORDER BY
axaptaoraclelines.itemid,
axaptaoraclelines.invoiceid

Suggestions on how to make the query work in CR9?

 

[jdemmi]

What is the relationship between the two tables (key field)?

 

[Kennymc]

Itemid is the key

 

[synapsevampire]

This query by CR 9 is what you've pasted as an Add Command, or is generated as a result of tables selected?

In either case, you are placing criteria on the child table, which overrides the Left Outer as you explicitly ask for certain data.

For any condition on the child table (as in the record selection), you need to allow for NULL as well as the condition, for example:

Report->Edit Selection Formula->Record

(
isnull({childtable.date})
or
{childtable.date} = {?DateParameter}
)

I won't bother to address the Crystal SQL Designer use as it's no longer supported and it didn't work like real SQL.

-k

 

[Kennymc]

It is generated as a result of tables selected.

I understand the reasoning, but a bit confused on the solution.

Is there a way to edit the sql generated by CR?

 

[Katy44]

Yes - go to Report menu - Selection formulas - Record.