UCCX DB Get search rows populates only row 1

[rwalker0111]

All,

I have created a DB with multiple rows.  On the DB Read test it returns all rows and the test is successful.  The DB Get field is populating with the correct columns.  The issue is for example I grab the ANI on a inbound call and want match it.  This works correctly but only for row 1.  What is the trick to searching all rows in the DB?

Thanks,

[Deepak Rawat]

I am not sure if it will be possible to do something like this using a single DB Read/Get. Why not to use multiple DB Read/Get steps and then perform the search individually on the different rows

Is the number of rows significantly high there?

Regards

Deepak

[rwalker0111]

Thanks for the reply,

My end result its to route by ANI inbound to specific agents using a DB for customer records ie phone number. 

I have the potential of a DB containing up to 1 million rows (customers). My dev setup works and populates my script variables.  The problem is when I get the ANI and say its customer record row 5 it populates row 1 variables like its not searching the whole DB

[Deepak Rawat]

So based on what you are  saying, the search is happening based on the caller ANI. Now let's say caller ANI is 12345 and you have a row (number 5) there for this match in the DB, then it does not match row 5 but row 1 infact and stops there. This gives me an impression that the DB contains more than 1 possible match and not the exact matches is that true. The moment it finds one possible match stops there and does not perform further hunting.

The more details you will give easier to look and think for the options.

Regards

Deepak

[rwalker0111]

Hi Deepak,

I have uploaded a couple screen shots.  As you will see in the script the ANI should have matched record 5 but rather populated with customer record 1 info

[Valery Kuznetsov]

Hi there,

can you share SQL query in DB Read step?

[rwalker0111]

Sure please see attached

Thanks

[Chakshu Piplani · Accepted solution]

Hi,

You need to use a query like.

Where cid is a the variable which stores customer's ID, which the customer had input in the beginning of the script using "get digit string" step.

Regards,

Chakshu Piplani

-Rate helpful posts-

[Valery Kuznetsov · Accepted solution]

This query without condition returns all rows in a table. When there are multiple rows DB Get step takes only first row by default. We can use loop and invoke DB get as many times as rows and make something with this data.

If you need to search by ANI change your query to next one, if you store ANI in $Cell_Phone variable.

SELECT * FROM Customer WHERE Cell_Phone = $Cell_Phone

This will return particular row from table with correct data.

[Gergely Szabo]

Hi,

it's always 3 steps:

1. DB Read - where you send the SQL query to the database server;

2. DB Get - where you read the data from the SQL Result Set (until there is no data);

3. DB Release - where you close the connection to the database server to free up resources.

You need to call the DB Get step multiple times if you want to have all the rows from the Result Set returned by the DB Read step. There's an easy way to achieve that. First, call the DB Read step. The execution pointer will exit on the "Successful" branch if everything is okay. Then call the DB Get step, while adding all of your business logic to the Successful exit of it, plus a Goto step that would send it back to itself. If there is data in the Result Set, it would read it. If there is no data, the execution pointer would exit on the No Data branch.

Here's a working example:

G.