Skip to Content
Former Member
Jun 09, 2010 at 08:07 PM

Inconsistent results when using queries with UDF


I have the following query that I can not get consistent results. Sometimes it will get the correct result but at other times it does not. I can not figure out what I am doing wrong.

The scenario is I have on the BP Contact several UDF that we are storing sales territory information. We want to pull based on the SalesRepNo the RegName. We have 2 user defined tables set up. I can accurately pull info when I am linking to the SLS_Person_Region table. On that table there is a field called region (a number). I am trying to use that number and pull the name from the Region_Major_reg table.

SELECT T0.[U_RegName] FROM [dbo].[@REGION_MAJOR_REG] T0 INNER JOIN [dbo].[@SLS_PERSON_REGION] T1 ON T1.U_Region = T0.U_Region INNER JOIN ocpr T2 on T1.[U_SlsPerNum] = T2.[U_SalesRepNo]

WHERE T1.U_SlsPerNum = $[OCpr.U_SalesRepno]

What is frustrating is that the query works fine for different terrioties.

Any help would be appreciated.