CR9- Left Outer Join....!!! Error

[OrionStar]

I'm getting the following error when trying to Left Outer Join:

Failed to open a rowset.
Details: HY000: [Cache ODBC] [State: S1000][Native Code 94]
[C:\program files\crystal decisions\crystal reports 9\crw32.exe
[SQLCODE: <-94>:<Unsupported usage of OUTER JOIN.>]
[Cache Error: <<SYNTAX>errdone+9^%qaqqt>]
[Details: <FLoop - func = DQ>]
[%msg: <SQL ERROR #94:Unsupported usage of OUTER JOIN.>]

Can anyone help????

I'm sure, I've used this type of outer join before.....

Thx

"A man is only as happy,... as he makes up his mind to be...

 

[synapsevampire]

Try posting your database type/version.

It sounds a bit buggy if you're using the Crystal GUI for doing the LO join.

If you're using an Add Command and pasting in the SQL then it may be that something is wrong with the SQL so test on your database first.

If you aren't aware of the Add Command, check it out, it allows you to use a SQL statement as the source for your report.

-k

 

[OrionStar]

Database TYPE? Version????

Uhhh.. not sure. I know I use ODBC to connect.... sorry not sure of what you are asking. I'm using the visual linking in CR9 and when I do an INNER JOIN = link I get my data but one table data is duplicated as follows:

Fee.FEEid Fee.FeeAmt Cost.Costid Costs.CostAmt
1234-A1234 200.00 1234-A1234 25.00
1234-A1234 200.00 1234-A1234 50.00
1234-A1234 200.00 1234-A1234 30.00

I want to see:

Fee.FEEid Fee.FeeAmt Cost.Costid Costs.CostAmt
1234-A1234 200.00 1234-A1234 25.00
1234-A1234 50.00
1234-A1234 30.00


Thank you for your help.







"A man is only as happy,... as he makes up his mind to be...

 

[synapsevampire]

You can right click any field and select format field and turn on the suppress if duplicated.

BTW, those aren't duplicate rows.

-k

 

[lbass]

For this display, you only need to format each of the first two fields to "suppress if duplicated" (format->field->common->suppress if duplicated). A left outer join wouldn't address this concern. If I remember correctly, though, CR 9.0 had trouble with left outer joins that I believe was corrected by a service pack.

-LB

 

[OrionStar]

Suppressing if duplicated did not work. The 2 amounts are in separate tables linked by the FEE.ID field. Some FEE.ID's have Fees and some have COSTS some have BOTH. They are linked by INV# which is NOT a part of this query. Maybe this is the problem???? Should I try and get the FEE.INVNUMBER field from another table and link it here? I would love to get this to work without having to do that...

I need my data to appear on the report as noted above.

Also, some FEE.ID's have MORE than ONE fee in this Table.

We deal mainly in FEES and COSTS here. Fees are our income, and COSTS are expenses that we incur and get reimbursement back from the client.

Anyway, I still need help if you can. thanks.


"A man is only as happy,... as he makes up his mind to be...

 

[lbass]

Suppress if duplicated" would work if your sample was a display of records at the detail level. If this is at the group level, please explain your group structure.

-LB

 

[OrionStar]

I'll try to explain....

Table Fee.Feeinfo contains:

Fee.FEEID
Fee.FEEAMT

Table Costs.Costinfo contains:

Costs.COSTID
Costs.COSTAMT

They share a common link which is the FEEID AND COSTID. These are the same identical elements.

I'm pulling the detail from these two tables and combining on one report. The above is an example of the problem. The FEE.ID above only has ONE FEE of 200.00, and numerous COSTS related to this FEE ID. When I process report, it shows the EXACT number of FEE items as COST items. The fact is though that there is only ONE FEE item for this FEEID. In my opinion, LEFT OUTER JOIN would fix this, at least according to the crystal help on LOJ??? But I get the error above when I try to LOJ?

"A man is only as happy,... as he makes up his mind to be...

 

[kskid]

A LOJ would list all FEEID, regardless of whether there is a matching COSTID. I don't think you want that

If you are accessing an Oracle or Access database, then

Fee.FEEid Fee.FeeAmt Cost.Costid Costs.CostAmt
1234-A1234 200.00 1234-A1234 25.00
1234-A1234 200.00 1234-A1234 50.00
1234-A1234 200.00 1234-A1234 30.00

is the normal output and solution by LBASS and Snapsevampire solution is what you need.

Another solution

From the Report Design tab,

Create a group on {Fee.FEEid}. You can either delete the group name created or you can delete it and move the Feeid field from the detail to the group 1 header.

If you did not move the Feeid to the group header 1 section, then either suppress it or delete it.

Move {Fee.FeeAmt} from the detail section to the Group 1 header section

Right click on the Group Header 1 section on the left of the Report Designer canvas and click on format section.

Click on Underlay following section

If you follow the above, then you will get what you want

-LW