SQL Command

[smither]

Not sure if anyone has come accross this problem or not but this is the first time I've had this problem. I've written a pretty lengthy SQL Command but after the designer retrieves the data it is not apply the filter in the data selection. This is the data selection:

{Command.GBOBJ}="9300" or
(
If {?JDE - Object Account All} ="All" then
{Command.GBOBJ}={Command.GBOBJ}
Else
{Command.GBOBJ}={?JDE - Object Account All}
) and
(
If {?JDE - Business Unit All} ="All" then
{Command.GBMCU}={Command.GBMCU}
Else
{Command.GBMCU}={?JDE - Business Unit All}
) and
{Command.GBCO}= "71110" and
{Command.GBLT}={?Ledger Type} or {Command.GBLT} = "AU"

To be more specific it is not applying this filter ({Command.GBCO}= "71110" ) to the dataset that was retrieve from the SQL. Has anyone come accross this problem before??

Thx.

 

[lbass]

One problem I see right away is that you are not clarifying what clauses go with the "ors". You should make sure you have parens around the clauses that are alternative to each "or". I'm not sure what you want to do here, but one interpretation might be:

(
{Command.GBOBJ}="9300" or
(
If {?JDE - Object Account All} <> "All" then
{Command.GBOBJ}={?JDE - Object Account All} else
if {?JDE - Object Account All} = "All" then
true
)
) and
(
If {?JDE - Business Unit All} <> "All" then
{Command.GBMCU}={?JDE - Business Unit All} else
If {?JDE - Business Unit All} = "All" then
true
) and
{Command.GBCO}= "71110" and
(
if {Command.GBLT} <> "AU" then
{Command.GBLT}={?Ledger Type} else
if {Command.GBLT} = "AU" then
true
)

I'm not sure about the last block either, but if "AU" is the default, then it should be set up like this, but at least it should be enclosed in parens. Note the parens around the first clauses, also.

Finally, you should really be setting up your record selection and parameters within the command, not in the Crystal GUI, as this causes processing to occur locally.

-LB

 

[smither]

Thanks for the reply...I've looked at the data selection more carefully and have realized that the OR statements were causing some problems. I've reorganized the criterias in the data selection and the report is working fine now.

Thanks again.