Skip to Content
0
Former Member
Feb 05, 2009 at 07:22 PM

Selection Formula to SQL translation issue

17 Views

I have noticed that parenthetical grouping doesn't always translate over from my record selection formula to the SQL generated for a report. It's become a problem when I have left joined tables that I want to filter data from, but only when the join returned a record from the right hand table.

Take this record selection formula for example (TICKET table is left outer joined to TICKETDATA):

(IsNull({TICKETDATA.ID}) OR
 ( {TICKETDATA.DATATYPE} = 5 AND
   {TICKETDATA.STAFFID1} = {?StaffID} AND
   {TICKETDATA.STAFFID2} = 0
 )
)

To me, this says that we will allow a TICKET record that didn't join to a TICKETDATA record, OR a ticket record that was joined to a TICKETDATA record with the right datatype and staff ID's. However, the sql generated becomes:

 SELECT "TICKETDATA"."ID", "TICKETDATA"."DATATYPE", "TICKETDATA"."STAFFID1", "TICKETDATA"."STAFFID2"
 FROM   "TICKETDATA" "TICKETDATA"
 WHERE  ("TICKETDATA"."ID" IS  NULL  OR "TICKETDATA"."DATATYPE"=5 AND "TICKETDATA"."STAFFID1"=1 AND "TICKETDATA"."STAFFID2"=0)

The change in grouping can often lead to logic different than what was originally intended.

Does anyone one know why this occurs or have suggestions on a better way to do this? Any help would be appreciated!

I've noticed this on both XI r2 and 2008.

Thanks,

Chuck