Using brackets in select expert 1

[ruan123]

Hi, I've inserted the following formula into the select expert -
{v_recon_operation_process_sub.rental_period} > 2 and
({billing_months.id} = {?Period} and
{v_recon_operation_process_sub.asset_status} = "Billable" and
{v_recon_operation_process_sub.asset_type} = "Rental" and
{discount_accounts.discount_type} = "Not Discounted" ) OR
(ISNULL({discount_accounts.id}) and
{billing_months.id} = {?Period} and
{v_recon_operation_process_sub.asset_status} = "Billable" and
{v_recon_operation_process_sub.asset_type} = "Rental")

but the query look as follows
WHERE ("v_recon_operation_process_sub"."rental_period">2 AND "billing_months"."id"=72 AND "v_recon_operation_process_sub"."asset_status"='Billable' AND "v_recon_operation_process_sub"."asset_type"='Rental' AND "discount_accounts"."discount_type"='Not Discounted' OR "discount_accounts"."id" IS NULL AND "billing_months"."id"=72 AND "v_recon_operation_process_sub"."asset_status"='Billable' AND "v_recon_operation_process_sub"."asset_type"='Rental').

Why do crystal throw away my brackets that I inserted between the OR. Without the brackets the query return the wrong values. I've tested both the queries,with and without the brakcets in PgAdmin3.

 

[Katy44]

That's very strange and worrying - what version of Crystal?

 

[ruan123]

Crystal 10 Developers Edition, I've downloaded it from their website.

 

[Madawc]

On general principles, I would split anything so complex into several formual fields. Something so complex is hard to read, hard to amend and always likely to go wrong.

Madawc Williams (East Anglia)

 

[Turkbear]

Hi,
Try adding 1 more set of parens:

(
{v_recon_operation_process_sub.rental_period} > 2 and
  (
   {billing_months.id} = {?Period}
    and
   v_recon_operation_process_sub.asset_status}="Billable"
    and
  {v_recon_operation_process_sub.asset_type} = "Rental"
    and
  {discount_accounts.discount_type} = "Not Discounted"
  )
  OR
 (
  ISNULL({discount_accounts.id})
  and 
  {billing_months.id} = {?Period}
  and
 {v_recon_operation_process_sub.asset_status}= "Billable"
  and
 {v_recon_operation_process_sub.asset_type} = "Rental"
  )
 )

See if it helps the parser understand...

 

[synapsevampire]

I have a FAQ on building the record selection to ensure proper SQL pass through:

faq767-3825

It's somewhat out of date, and was tested through CR 8.5, but the principles are still sound.

-k

 

[fredp1]

Thats very strange. Try patching the to the latest hotfix. Also see if its the db driver, by changing to a different one, i.e. ODBC, native, OLEdb.

Fred