Selection Formula to SQL translation issue

[Former Member]

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

[Former Member · Accepted solution]

Hi Chuck,

I had a similar issue that I posted on this forum yesterday, search "Using Left Outer Join." You can review it and it has a lot of material that might help you.

Sometimes you'll need to play with the record selection criteria in order to have the SQL query do what you want it to do.

Just a thought, try to remove the outer most paranthesis from your record selection as follows:

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

I hope this helps.

Regards,

Zack H.

[Former Member]

Actually, that isn't going to work either. I need parameters auto-filled in for the query and there's no quick way I can see to do that with our current set up.

I'll check out the post you mentioned for tips.

[Former Member]

Thanks for the reply, Zack.

I've tried removing those, but with no luck. I've read a few posts and I do believe I can accomplish what I'm trying to do by using an SQL command for the table instead of linking directly to the table. For example, create the table with:

SELECT * FROM TICKETDATA WHERE DataType=5 AND Status=1 etc etc

After doing that, I can simply left join TICKET to that and I believe I will get the proper results. At least I'm hoping!

Chuck