Select not selecting one value of the two

[bobpv]

Hello All,

I have a VFP database, running on a SQL server. Using Crystal 8.5. It contains a field for Division (DIVNO) and a field for product. There are about 15 products in the table, and several divisions.

The report is to pull ONLY the product called 'PURCHASE' if the parameter DIVNO is = 'P'. Otherwise, all produsts should be in the report.

I have tried a number of things, and am frustrated. I must be missing something here and am hoping someone can give me a clue.

Here is the select:
works for div C

IF {?@tcDivno} ='C' then true
else
{rq_jc_profitability;1.product} = "PURCHASE"

But the above will not work for DIV P, this will work for P but not for C:

Works for div P

IF {?@tcDivno} <>'C' then
{rq_jc_profitability;1.product} = "PURCHASE"

else IF {?@tcDivno} = 'C' then
{rq_jc_profitability;1.product}={rq_jc_profitability;1.product}

I need to get it to work for both! Short of a second report, what can I do?

Thanks in advance.

 

[bdreed35]

I think this is what you are looking for:

Put the following in your Record Selection Criteria:

if {?@tcDivno} = 'P' then
{rq_jc_profitability;1.product} = "PURCHASE"
else if {?@tcDivno} <> 'P' then
True

~Brian

 

[bobpv]

Thanks but no good. Same result. This formula does select from Div P, but missed about 30 jobs in Div C.

I do appreciate the assist however!

 

[kskid]

I believe you need double quotes for the C

"C" instead of 'C'

 

[bobpv]

No, sorry.

Single or doubles are fine as long as they are consistant. Crystal does doubles by default, but you do not have to. I prefer singles, so I don't have to hit the shift key. But I do appreciate your time!

 

[kskid]

Then how about this?

IF isnull({?@tcDivno}) or {?@tcDivno} <> 'P' then
{rq_jc_profitability;1.product}={rq_jc_profitability;1.product}
else
{rq_jc_profitability;1.product} = "PURCHASE"

 

[lyanch]

What does the SQL look like with diffent formulas and different choices (Database->Show SQL Query).
If "This formula does select from Div P, but missed about 30 jobs in Div C." does get some of the Div C ones why doesn't it get them all (some other part of the selection formula?) because it should be sending nothing to the SQL?

Lisa

 

[bobpv]

Lisa,

The report is calling a stored procedure on the server.

It is really weird, that the report will get all of each division, but miss some of the others. Yes, there are many more parameters, but the SP does not gather all data, as compared to running a qry against the tables. Even if I match the other parameters.

 

[lyanch]

So, this selection formula works only locally correct? In other words, no matter which formula you choose, the same thing gets sent to the database (the call to the SP). That is a big difference. If you remove the selection formula completely do you actually get all the division c records (in addition to the p records obviously). There is no reason that the formula won't work correctly *if* all the data is getting there in the first place.

Lisa

 

[bobpv]

Exactly. I can get the data to return if I remove the select. But trying to filter on the div to product is the issue. What is most wierd, is how one will work fine for P and fail for C, while the other is just the oppisite!

The select is local, as it is filtering the result set returned from the server.

Good thing I was going bald before I started this!

 

[lbass]

Here's a comment that may be off-base. {?@tcDivno} is kind of a weird name for a parameter, implying that you named your parameter "@tcDivno". Are you sure you are using the parameter field in your select statement and not a formula for division called {@tcDivno}?

-LB

 

[bobpv]

No, the tc is a parameter naming convebtion for the program.
@ - Crystal Convebtion
T - parameter
C - Char
Divno - parameter name.

The parameters are IN the procedure to speed up processing. So the procedure is calling all Div P or C and then Crystal is filtering on product.

The starngest part is why it is not returning all records. As I said in the first post, it will retun all P or C depending on the selection, but neither select will return all of both. I can get all C with this
IF {?@tcDivno} ='C' then true
else
{rq_jc_profitability;1.product} = "PURCHASE"

but will not get all P (only 12 of 22 returned) while this

IF {?@tcDivno} <>'C' then
{rq_jc_profitability;1.product} = "PURCHASE"

else IF {?@tcDivno} = 'C' then
{rq_jc_profitability;1.product}={rq_jc_profitability;1.product}

gives me all 22 P but only 426 of 462 C's.

Really odd.

 

[lyanch]

Look at the "show SQL" statement. the "?" is Crystals convention for a parameter, the @ is the convention for SP parameters for SQL server. What you put in the select statement is not ever sent to the database since all that is sent is the call to the SP with the parameter value. If you remove your select statement completely. Do you get all the 'P's with a P prameter value and all the 'C's with a 'C' parameter?

My guess is that some {rq_jc_profitability;1.product} have a NULL value, which crystal will exclude with {rq_jc_profitability;1.product}={rq_jc_profitability;1.product}

since by convention NULL = NULL is false (as well as NULL <> NULL)

Lisa

 

[bobpv]

Thanks for the help. But as the field Product is a required field, and nulls are not allowed, nope.

I also checked the SQL inside Crystal, and it is calling as expected.I want all the data returned from the procedure and will filter inside the report. Not the most efficent I realize, but I thought it would work.

So I must look at the SP itself, there must be something there I missed.

I do appreciate it!