cancel
Showing results for 
Search instead for 
Did you mean: 

Inconsistent results when using queries with UDF

Former Member
0 Kudos
71

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.

Thanks

Steve

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Steve,

Your query equals to:

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

If the result is not correct, it simply means the link is not correct.

Thanks,

Gordon

Former Member
0 Kudos

Gordon

In further testing I realized that the sales reps I was having trouble with were the ones that started with a zero. The sales rep # is alpha numeric as is the Sales person #.

Anyting over 1000 works like a charm. when it is 0106 It does not work but 0205 does.

I am really struggling to figure this out. Appreciate your help.

Any thoughts?

Thanks

Former Member
0 Kudos

How many of those # are < 1000?

Former Member
0 Kudos

the scheme is the first tow indicate the territory

The next two digits represent the rep

When a rep is replaced we assign an open territory or the next rep the next number

an example is 0105 was an open territory when we signed a new rep we gave them 0106

To answer your question we have nine

0106

0205

0303

0401

0503

0602

0702

0801

0904

I think that is what you are asking

Former Member
0 Kudos

The easy solution actually would be reallocate all your territory to be great than 10. 01-09 could just leave empty.

Former Member
0 Kudos

Unfortunately that is not possible.

Would changing the field to text solve that problem?

Former Member
0 Kudos

That is possible. You could try it first.

Former Member
0 Kudos

Gordon

Thanks for your assistance. I ended up re-importing the contact data and now my queries work. I beleive what happened when I was bringing in data through DTW I lost my formatting as a text field. I re-imported everything and now my queries work like a charm.

Thanks for your support.

Answers (0)