SQL query question (v8.0.1.0)

[torkelb]

Hi!

First time here, I hope there is someone that can help me.
Due to staff reduction I have to make some changes in Crystal Report v8.0.1.0, a program I have never worked with before.
I have a problem the SQL query, it seems as if the WHERE clause in the default SQL query is always used even though it isn't visible in the "Show SQL query".

Background
===============
The database contains data we use to produce statistic reports for saw mills, number of boards, number of waste boards and so on. Now we want to add a number of dry boards to and count that for each "grouping" (by order name, shift, supplier etc)

The database contains a table "ProductionStatistics" with a field "ProductType". In Crystal I have a field called "BoardQuantity" that looks like this:
If ({ProductionStatistics.ProductType} = 6) Then
{ProductionStatistics.ActualQuantity}
Else
0

I also have a Sum field called "Sum of BoardQuantity" in the report that sums up for each group. All this works perfectly well.

We have added another product type (11 = dry boards) to the database and I want to do the same summation.
I created a field "BoardQuantityDry" like this:
If ({ProductionStatistics.ProductType} = 11) Then
{ProductionStatistics.ActualQuantity}
Else
0

And also made a summation field "Sum of BoardQuantityDry" in the report and entered them next to the "Sum of BoardQuantity" in all occurencies in the report.

When I look at the "Show SQL Query" I get this:
SELECT
ProductionStatistics.`ProductName`, ProductionStatistics.`ProductType`, ProductionStatistics.`ProductMinLength`, ProductionStatistics.`ProductMaxLength`, ProductionStatistics.`ActualQuantity`, ProductionStatistics.`ActualLength`, ProductionStatistics.`ActualArea`, ProductionStatistics.`ActualVolume`, ProductionStatistics.`ExtraId1`, ProductionStatistics.`ExtraId2`, ProductionStatistics.`FromTime`, ProductionStatistics.`ToTime`,
OrderName.`Name`,
Extra1.`Text`,
Extra2.`Text`
FROM
((`ProductionStatistics` ProductionStatistics INNER JOIN `Extra1` Extra1 ON
ProductionStatistics.`ExtraId1` = Extra1.`Id`)
INNER JOIN `Extra2` Extra2 ON
ProductionStatistics.`ExtraId2` = Extra2.`Id`)
INNER JOIN `OrderName` OrderName ON
ProductionStatistics.`OrderNameId` = OrderName.`Id`
WHERE
(ProductionStatistics.`ProductType` <= 3 OR
(ProductionStatistics.`ProductType` = 5 OR
ProductionStatistics.`ProductType` = 4 OR
ProductionStatistics.`ProductType` = 6 OR
ProductionStatistics.`ProductType` = 104 OR
ProductionStatistics.`ProductType` = 103))

The WHERE clause in this query looks a bit stupid too start with but the biggest problem is that it doesn't allow product type 11.

So I changed the where clause to this instead:
WHERE
(ProductionStatistics.`ProductType` <= 6 OR
ProductionStatistics.`ProductType` = 11 OR
ProductionStatistics.`ProductType` = 12 OR
ProductionStatistics.`ProductType` = 103 OR
ProductionStatistics.`ProductType` = 104)

I also have a record selection that looks like this:
{@ValidProductType}
where ValidProductType now looks like this:

if ({@ThisIsAProdStatReport}) then
(({ProductionStatistics.ProductType} < 7) Or
({ProductionStatistics.ProductType} = 11) Or
({ProductionStatistics.ProductType} = 12))
else
(({ProductionStatistics.ProductType} = 103) Or
({ProductionStatistics.ProductType} = 104))

(ThisIsAProdStatReport is always true in this case)

So product type 11 is now allowed both in the SQL query and in the record selection.
When previewing the report I still get zero result for the "Sum of BoardQuantityDry" field.

So I deleted the full SQL query and after that the WHERE clause disappeared so there should be no limits on the selection. I still don't get any values for product type = 11!

If I press the reset button I get the initial ugly WHERE clause. I suspect that Crystal is always using this clause AND any other WHERE clause I might add even though it isn't visible in "Show SQL query".

So, how do I change the SQL query to make my selection work?

Best regards

/Torkel

 

[lbass]

You shouldn't really be modifying the Show SQL Query AND the record selection formula. If you remove the where clause in the Show SQL Query and then add the selection criteria in report->edit selection formula, those parts of the formula that pass to the server will be reflected in the where clause of Show SQL Query. Any parts that don't appear there are being processed locally, instead of on the server.

What is the content of your formula {@ThisIsAProdStatReport}?

-LB

 

[torkelb]

Hi!

Yes, my problem is that it doesn't matter what I do, I still don't get any product types except those in the default SQL query (<= 3, 4, 5, 6, 103 and 104).

I have tried running the SQL query without any WHERE clause and empty record selection criterias too and I still don't get the product type 11 to show up (or any other than the ones I listed a few lines up)

So that's why I suspect that the default WHERE clause is still used! It sounds crazy but it's the only explanation I can think of.
The fields "Sum of BoardQuantity" and "Sum of BoardQuantityDry" are exactly the same except that one filters out productType=6 and the other ProductType=11.

I also noticed that the record selection criteria is "pushed down" to the SQL query if it is written the correct way as you wrote.

{@ThisIsAProdStatReport} is set to true in the Crystal reports environment, when we use it in Delphi we set it to either true or false depending on what type of report we are going to produce.

I'm completely lost here and so is everyone else I have talked too.

 

[lbass]

Please show the content of {@ThisIsAProdStatReport}.

-LB

 

[torkelb]

As I wrote, it doesn't matter what I do. I have run the report without any Report selection criterias (ThisIsAProdStatReport is not even used in that case) but still product type 11 is not seen.

Just as a test I reopened the old report (without BoardQuantityDry) and did the following:

1. Changed the field BoardQuantity to filter out type 11 instead of 6

2. Cleared all record selection criterias

3. Deleted the whole SQL query which gave me a new query without a WHERE clause

After these changes I would be able to see any product type and especially productType = 11 but nothing shows up in the report!

This is why I can't help thinking that the default SQL query is somehow used even though it isn't visible.


How is the default SQL query made? (The query I get when I press reset)

 

[lbass]

It is based on your record selection formula, the table joins you have created in the linking expert, and the fields that you have added to the body of the report or used in formulas.

I think you should try creating a report from scratch, without touching the SQL query directly.

-LB

 

[torkelb]

Tada!
I removed all tables and links and added them again. After that I got error messages from three of my group headers but by comparing against the old version I found that the "Sort & Group" field was not initialized correctly so I changed them and after that the reports works and I can see my precious "dry boards"!
Now the default SQL query has no limits on ProductType.

It is evident that the default "SQL where clause" is always used even though it isn't visible! Very important to know and I hope it has changed in later versions if CR?

Thanks to lbass for your time.

 

[torkelb]

Just a side note: when the database tables are removed all charts are removed too! And they cannot be copied from an existing report so they have to be recreated from scratch... Jipppie!