Showing results for 
Search instead for 
Did you mean: 

db lookup query

Level 1
Level 1

Hi All

We have a requirement from a customer on which we are working on now.The want the prefix number of the ani to be checked before routing the call.The db which MS sql is in place n we have integrated that to the ipcc 7.2.I do a db lookup and its where i have defined the table to look and value to lookup is call.calling line id.This works fine for the complete ani number but to check the prefix out of the ani it does not work.I suppose there is no way in my db where i can i define say if 4009990 number then 400T or 400.... where 400 is prefix.

12 Replies 12

How about making a column in your db table of just the prefix? Then in ICM you can do a left() on the calling line id before you do a dip?


Level 1
Level 1


Also, your SQL select could perform a substring to parse out the data, of course your data integrity needs to be consistant with your coding in that the prefix is the first 3 characters.

Is your customer satisfied with always using the first 3 digits of the Ani, Do they have any 11 or 7 digit numbers stored? Run some sql against your your customers data to see if they always have a 10 digit number


Please rate all posts

thanks for your replies....the db has either 3 or 4 or 5 digits as a prefix numbers.I have used the left(calling line id,3) and if works fine for 3 digits.If i have to test it out for 4 and 5 too do i need to have a seperate columns to have these numbers or can i just use a or condition with the left() for 4 and 5 as well.

In SQL a case statement on the the select that would allow any of these 3 to be used against the calling Line

I do more Oracle so my Sql Server syntax is a little rusty, of course you need to figure out the test = condition(s), but this is the general idea


WHERE test = condition1 THEN

left(calling line id,3)

WHERE test = condition2 THEN

left(calling line id,4)

WHERE test = condition3 THEN

left(calling line id,5)

from table

If CRS doesn't like this Select then create a view and have CRS select from that

Please rate all posts



i tried using the or function to do a left check for 3 digit,4 and 5 but it does not work with that.Also a diffrent db lookup node to check the digits 4 and 5 on the same table also did not help much.Blair can you please explain in detail of what exactly needs to be done which could be helpful to me.


Blair is talking about UCCX while you're using UCCE. What happens when you do multiple dblookups?


The idea is to use one SQL statement to return the 3,4, or 5 digit prefix.

The case statment is used to determine what kind of prefix it is and peform a left trim function to retrieve a 3,4, or 5 digit prefix. You need to provide how the case statement needs to be contructed based on the values to determine 3, 4, or 5 digit. I hope this helps.

Is ICCE able to use a Database View or do advanced SQL?

How are you accessing SQL Server?


Please rate all usefull posts

dblookups through UCCE are very basic. Pretty much a simple select * from x where y.


Do you have the ability to Log into the SQL Server database using Query Analyzer or have a Database Admininstrator or Developer resource who knows this SQL Server installation who can help you to write SQL and/or possibly create objects (views, alter tables,etc.)using the same ID UCCE is logging in as.

For UCCE, Do you know if views are a known issue such as

Select * from view_name ?


Please rate all useful posts

>dblookups through UCCE are very basic. Pretty much a simple select * from x where y.

Agreed. Go to Java and build your own class, build a test class and test it outside of CRS, and then make a JAR, import and use in the Expression Editor.




Sorry it does work fine with multiple db lookups.Maybe some problem in the db worker process not being up when i tested.Anyways its for prefix 3,4,5 to check in the same table i had three different db lookup nodes one for each prefix and it works fine but having said that trying to do with an or operator in one db lookup node with prefix checking for 3,4,5 does not work fine.Does that mean i would need to use the db nodes as per my prefix number digits or can there be an easier way out.

The multiple db lookups is the easiest solution, but also not the prettiest one. Geoff's solution would be the best, since it is very scalable, however it will take time especially if you're not a java developer. Honestly, if this meets your needs, just move on.