Variables in DB Read step

[JimSJ]

Hi, I'm trying to do a select statement from within a DB Read step and I was wondering if it is possible to use a variable for the table being selected from. For example, this would be the select statement without a variable:

select * from table where info = $strInfo

Where table is the name of the table I want to query from. I want to do something like the following:

select * from $strTable where info = $strInfo

but its giving me a SQL error when I try.

Can this be done? Any suggestions if it can why it might not be cooperative?

Thanks for any help in advance,

Jim

[rnarayana]

Hello Jim,

Select * From

.This SQL statement is where the has to be a valid table name in SQL database and not a variable.This is not a restriction of IVR .This is how the SQL query statement is .IVR just uses the same SQL query statement.

Also may I know why do you want to do this.

I hope this helps.

Thanks,

Radhika

[JimSJ]

Thanks for the reply Radhika. The reason I wanted to do this is that my customer has several tables already in place on MS SQL 2K. One contains data for one customer division, the other contains data for the second division. Based on the number called to get into the IVR, I set a variable to something like strTable1 or strTable2.

Also, there are two or three types of tables out there. So for instance,

California Sales Table

New York Sales Table

but they also have:

California Service Table

New York Service Table

What I was hoping to do was set a variable based on which number was dialed (California or New York) to reach the queue. Then based on customer selection (service or sales), do a DB Read based on their location. So to put it all together, this is what I wanted to do:

Customer calls in for the California division.

I set the location variable to California.

Customer selects service.

Go straight to a DB Read using the california service table.

Quite convoluted, but I was hoping to avoid duplicating almost the exact same code when the only difference is that table being queried via a DB Read is slightly different.

Any suggestions on doing this more efficiently would be greatly appreciated. Thanks!

Jim

[rnarayana]

Hello Jim,

This is pretty simple to do.you don't have to set the variable for this.

Lets say the number for california is 4000 and for NY is 5000.Before you were setting a variable depending on the number.Instead do this

If callednumber is 4000 do a database lookup for california as 'select * from california sales

and if the called number is not 4000 do a different db lookup for NY as select* from NY sales.

similary you can continue with the script.All you need to do is use a if statement instead of using variable and use may be 2 different db read.

I hope this helps.

Thanks,

Radhika

[JimSJ]

Thanks for your help Radhika. Luckily it appears that I have misunderstood the customer's DB configuration and there is only one table for each functional group (ie one table for sales in every division etc) so I won't even have to worry about this. :)

I appreciate the help though.

Thanks,

Jim