cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements

Cisco Community Designated VIP Class of 2020

1273
Views
5
Helpful
8
Replies
Highlighted
Beginner

Looking for an SQL query to retreive callvariables + ECC from a RUN SCRIPT RESULT (Translation to VRU)

Hi Team,

I am looking for an SQL query to check the data (ECC + CallVariable) received following a RUN SCRIPT RESULT when requesting an external VRU with a Translation Route to VRU with a "Run External Script".

I believe the data are parsed between the Termination Call Detail + Termination Call Variable .

If you already have such an SQL query I would very much appreciate to have it.

Thank you and Regards

Nick

1 ACCEPTED SOLUTION

Accepted Solutions
Advocate

Re: Looking for an SQL query to retreive callvariables + ECC fro

Omar,

with all due respect, shortening a one day's interval might not be an option for a historical report ;-)

I would recommend to take a look the following SQL query:

DECLARE @dateFrom DATETIME, @dateTo DATETIME

SET @dateFrom = '2014-01-24 00:00:00'

SET @dateTo   = '2014-01-25 00:00:00'

SELECT

tcv.DateTime,

tcd.RecoveryKey,

tcd.RouterCallKeyDay,

tcd.RouterCallKey,

ecv.EnterpriseName AS [ECVEnterpriseName],

tcv.ArrayIndex,

tcv.ECCValue

FROM Termination_Call_Variable tcv

JOIN

(SELECT RouterCallKeyDay,RouterCallKey,RecoveryKey FROM Termination_Call_Detail WHERE DateTime > @dateFrom AND DateTime < @dateTo) tcd

ON tcv.TCDRecoveryKey = tcd.RecoveryKey

LEFT OUTER JOIN Expanded_Call_Variable ecv ON tcv.ExpandedCallVariableID = ecv.ExpandedCallVariableID

WHERE tcv.DateTime > @dateFrom AND tcv.DateTime < @dateTo

With variables, you can parametrize your code (for instance, you could write SET @dateFrom = ? and let the calling application fill in the datetime value in for you).

Plus joining two large tables with all rows like you did (TCD-TCV) is never a good option.

Another aspect to consider: all ECC's are actually arrays (always), so it's not good to leave out the index value (tcv.ArrayIndex).

G.

View solution in original post

8 REPLIES 8
Enthusiast

Looking for an SQL query to retreive callvariables + ECC from a

You'll want to link up the two tables on the following:

Termination_Call_Detail.RecoveryKey = Termination_Call_Variable.TCDRecoveryKey

Beginner

Looking for an SQL query to retreive callvariables + ECC from a

Hi,

Thank you for your answer.

I made this query bellow. Now the only thing is that this query doesn't show the RouterCallKey/RouterCallKeyDay

Therefore there is probalbly some enhancement to be done on this query

Regards

Nick

-----------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT 

LEFT(RTRIM(Termination_Call_Variable.DateTime),25 )AS DateTime,

LEFT(RTRIM(Termination_Call_Variable.ExpandedCallVariableID),10 )AS ECC_ID,

LEFT(RTRIM(Expanded_Call_Variable.EnterpriseName),25 )AS EnterpriseName,

LEFT(RTRIM(Termination_Call_Variable.ECCValue),25 )AS ECCValue,

Termination_Call_Variable.RecoveryKey,

Termination_Call_Variable.TCDRecoveryKey

FROM  Termination_Call_Variable, Expanded_Call_Variable

WHERE Termination_Call_Variable.ExpandedCallVariableID = Expanded_Call_Variable.ExpandedCallVariableID

AND  DateTime > '2014-01-20 12:00:00'

AND  DateTime < '2014-11-20 16:02:00'

ORDER BY TCDRecoveryKey, RecoveryKey

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Enthusiast

Looking for an SQL query to retreive callvariables + ECC from a

RouterCallKey and RouterCallKeyDay are stored in Termination_Call_Detail which you do not have in your query

Rising star

Looking for an SQL query to retreive callvariables + ECC from a

Nick,

This might be what you're looking for. If this query works for you, please be conservative with it and shorten the DateTime gaps.

SELECT

LEFT(RTRIM(TCV.DateTime),25) AS DateTime,

LEFT(RTRIM(TCV.ExpandedCallVariableID),10) AS ECC_ID,

LEFT(RTRIM(ECV.EnterpriseName),25) AS EnterpriseName,

LEFT(RTRIM(TCV.ECCValue),25) AS ECCValue,

TCV.TCDRecoveryKey, TCV.RecoveryKey, TCD.RouterCallKey, TCD.RouterCallKeyDay

FROM Termination_Call_Variable TCV

JOIN Termination_Call_Detail TCD ON (TCDRecoveryKey = TCV.TCDRecoveryKey)

JOIN Expanded_Call_Variable ECV ON (ECV.ExpandedCallVariableID = TCV.ExpandedCallVariableID)

WHERE TCV.DateTime BETWEEN '2014-01-24 00:00:00' AND '2014-01-24 23:59:59'

GROUP BY TCV.DateTime, TCV.ExpandedCallVariableID, TCV.TCDRecoveryKey, TCV.RecoveryKey,

TCV.ECCValue, ECV.EnterpriseName, TCD.RouterCallKey, TCD.RouterCallKeyDay

ORDER BY TCDRecoveryKey, TCV.RecoveryKey

Advocate

Re: Looking for an SQL query to retreive callvariables + ECC fro

Omar,

with all due respect, shortening a one day's interval might not be an option for a historical report ;-)

I would recommend to take a look the following SQL query:

DECLARE @dateFrom DATETIME, @dateTo DATETIME

SET @dateFrom = '2014-01-24 00:00:00'

SET @dateTo   = '2014-01-25 00:00:00'

SELECT

tcv.DateTime,

tcd.RecoveryKey,

tcd.RouterCallKeyDay,

tcd.RouterCallKey,

ecv.EnterpriseName AS [ECVEnterpriseName],

tcv.ArrayIndex,

tcv.ECCValue

FROM Termination_Call_Variable tcv

JOIN

(SELECT RouterCallKeyDay,RouterCallKey,RecoveryKey FROM Termination_Call_Detail WHERE DateTime > @dateFrom AND DateTime < @dateTo) tcd

ON tcv.TCDRecoveryKey = tcd.RecoveryKey

LEFT OUTER JOIN Expanded_Call_Variable ecv ON tcv.ExpandedCallVariableID = ecv.ExpandedCallVariableID

WHERE tcv.DateTime > @dateFrom AND tcv.DateTime < @dateTo

With variables, you can parametrize your code (for instance, you could write SET @dateFrom = ? and let the calling application fill in the datetime value in for you).

Plus joining two large tables with all rows like you did (TCD-TCV) is never a good option.

Another aspect to consider: all ECC's are actually arrays (always), so it's not good to leave out the index value (tcv.ArrayIndex).

G.

View solution in original post

Rising star

Looking for an SQL query to retreive callvariables + ECC from a

Perhaps your query will work for Nick, but it did not return any results for me. Regardless, what you said makes sense.

Beginner

Re: Looking for an SQL query to retreive callvariables + ECC fro

Hi Gergely

This query of yours is great ! It's exacly what I was looking for.

Thank you for your help.

Best Regards

Nick

Rising star

Re: Looking for an SQL query to retreive callvariables + ECC fro

Gergely,

Is there a way to include non-persistent variables?

EDIT: Kindly disregard. Just realized that non-persistent variables are not written to the database.

CreatePlease to create content
Content for Community-Ad
FusionCharts will render here