Left Outer Join Math

[jmd0252]

I have a primary table, with everything, I have SQL commands I have created, and run fine, but because of the prompting and dates, do not contain everything. So the math is simple,, beginning balance(primary file) - Sales(sql command), +/- adjust, transfer, crushing(sql command). What I see is when there are records for everything,, it works great, otherwise I do not get a value. And no the one record with the 3, may have 1,2,or all items. Now I can do math on the "3" record, no problem. But my few attempts at writing formulas have not been pretty. The tables are linked left outer to the sql commands, via a station code, and a material code. Ideas, suggestions, comments, queries??? Any help is great. I am using XI, and the report is grouped by station, then by material. Thanks in advance,, Jim

 

[lbass]

This is very confusing. Especially

And no the one record with the 3, may have 1,2,or all items. Now I can do math on the "3" record, no problem.

Don't have a clue what that means.

It sounds like you have multiple commands and some tables taht you are then linking together. This is generally a bad idea, as the linking will occur locally and result in a slow report. You should ideally build everything into one command to use as your sole datasource.

However, if you must link, you should be careful not to add any selection criteria on fields from the tables/commands on the right side of a left outer join, as this will effectively "undo" the left outer join, and you will lose records.

-LB

 

[jmd0252]

Well the 1,2,3 is they are different fields that I sum, via the sql command this one,, SELECT TOP (100) PERCENT STATION, MAT_CODE, SUM(ADJUST) AS adj, SUM(CRUSH) AS crsh, SUM(TRANS) AS trns
FROM dbo.INVENTOR
WHERE INV_DATE between '2010-10-01' and {?Week-Ed}
GROUP BY STATION, MAT_CODE. So that is why that there may be value is any, some or all the fields, or none of the fields. I run thru a database to get ytd info, and then run thru it again, to get weekly info. I do this with 2 different database files. Again, once to get ytd, and once to get weekly numbers. I was grouping via station, and material, and printing on the material grouping line. I have changed that so I group only by station, and do a detail line for the material. But I get the same results. How do I evaluate the fact if I have a record for my links? the "sums", appear as blanks, if I can figure out how to "make" them zero, would the math work correctly? Or could I say if the field value ISNULL, think that would work? In my selection criteria I do not exclude anything.

 

[Madawc]

Are you using the SQL command to collect data? And then to find totals as well? If so, limit the SQL to just selection and use Crystal's excellent automatic totals to do the rest. Probably you need running totals, and they have the advantage that you can put them next to the detail lines to investigate something that's not working.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

I also can't see why you need SQL at all, Crystal can do simple selections of the sort you seem to be using.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[jmd0252]

yes I am using the sql command to summarize fields from the database and produce a single record of that summary data.

 

[lbass]

You can use a formula like this to sum the three fields:

(
if isnull({command.adj}) then
0 else
{command.adj}
) +
(
if isnull({command.crsh}) then
0 else
{command.crsh}
) +
(
if isnull({command.trns}) then
0 else
{command.trns}
)

You realize that this value will be the same for both detail and group level records within the same group instance, right?

-LB

 

[jmd0252]

Well,, I did find what I wanted to know.. If there is no record on the right,, when you do a left outer join,, the value of the field are set to "null".. So my formula that works is this now.
if IsNull ({@step1}) and IsNull ({Command_2.YTDTONS}) then
+ {Beginning_Balance.Tons} else
if IsNull ({@step1}) and {Command_2.YTDTONS} <> 0 then
{Beginning_Balance.Tons} - {Command_2.YTDTONS} else
if IsNull ({Command_2.YTDTONS}) and {@step1} <> 0 then
{Beginning_Balance.Tons} + {@step1} else
if {Command_2.YTDTONS} <> 0 and {@step1} <> 0 then
{Beginning_Balance.Tons} + {@step1} - {Command_2.YTDTONS}
else 0

step1 is {Command.adj} + {Command.crsh} + {Command.trns}
So that gives me a single field to test the existence of the data. For that particular SQL Command.

Thanks to everyone, who looked at my problem, and submitted any ideas.