December 24, 2009 at 8:13 am
I really appreciate the responses!
I was testing the CASE statement through a remote connection into Data Manager. Through Data Manager I query the cache database for the testing of the CASE statement. Not sure if that answers your question on using a linked server vs. cache directly. We don't have a direct connection into the vendor cache database. We have to use Data Manager or SSIS to get to the source data.
For our normal processing, we use a SSIS package to extract data from the cache source in our application. After the data is extracted it is stored in a staging table. From the staging table the CALL BEGAN TIME field is converted to a VARCHAR and stored that way in the data warehouse table. The code to extract from the cache source started bombing when we encountered the original problem detailed in the first post. It took a fair amount of work with SSIS to extract the time and ultimately display it correctly in the data warehouse table. It would be optimal to modify the SSIS code (which is SQL) that does the extract from the cache database to handle the negative value causing us the error.
I'm off for the holidays (lucky me!!!) so I don't have the extract code in front of me to post here. I will be back next Wednesday and will post it then. Thanks again for your responses and all of you have a safe and Happy Holiday!!!!!!!
December 30, 2009 at 12:18 pm
Here is the code I'm testing in Data Manager. Data Manager is the interface we use to the vended cache database. Directly after the code is the error received when running this query. The commented out portion in the SELECT is the original code which bombs when the Time_call_Began has a negative value.
SELECT
Date_Call_Began
,datediff(ss,
CASE
WHEN isnumeric(Time_Call_Began) = 1
THEN dateadd(ss,
cast(Time_Call_Began AS int),
cast(Date_Call_Began + ' 00:00:00' AS datetime)
)
ELSE cast(Date_Call_Began+ ' ' + Time_Call_Began AS datetime)
END,
cast(Date_Call_Began + ' ' + Time_Call_Began AS datetime)
)
--,cast (Time_Call_Began as timestamp) as Time_Call_Began
,ACCOUNT_NUM
,CALL_LOG_INDEX
,Date_Call_Ended
,cast (Time_Call_Ended as timestamp) as Time_Call_Ended
,Dialer_ID
,Dialed_Phone_Number
,cast(LTRIM(RTRIM(ANI_Digits_Received)) as varchar(20)) as ANI_Digits_Received
,Pool_For_Call
,Inbound_Route
,Call_Type
,Termination_Code
,Inbound_Answer_Code
,cast(LTRIM(RTRIM(Manual_Talk_Time)) as varchar(10)) as Manual_Talk_Time
,cast(LTRIM(RTRIM(Inbound_Talk_Time)) as varchar(10)) as Inbound_Talk_Time
,cast(LTRIM(RTRIM(Outbound_Talk_Time)) as varchar(10)) as Outbound_Talk_Time
,Agent_ID
FROM Call_Logging_Audit
WHERE
AND ACCOUNT_NUM is not null
and Pool_For_Call is not null
and Call_Type is not null
and Termination_Code is not null
and Date_Call_Began is not null
and Time_Call_Began is not null
1. ERROR
DS-DBMS-E400: UDA driver reported the following on connection 'xxxxx_Test':
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[Cache ODBC][State : S1000][Native Code 359]
[C:\Program Files\cognos\c8\bin\dsdesign.exe]
[SQLCODE: <-359>:<SQL Function (function Stored Procedure) not found>]
[Cache Error: <<SYNTAX>errdone+1^%qaqqt>]
[Details: <Prepare>]
[%msg: < SQL ERROR #359: User defined SQL Function 'SQLUSER.ISNUMERIC' does not exist>]
December 30, 2009 at 12:48 pm
Let me quote my question/recommendation from above:
lmu92 (12/23/2009)
...What would happen if you'd store the data "as is" into a SQL Server staging table and go from there?
I don't think it's the correct place for asking questions about missing functionality of the interface you're using. It seems like that software is not capable of performing the isnumeric() check.
So, as recommended earlier: Load the table "as it is" into SQL Server and handle the transformation there.
By having just a glance at the DataManager website I think it's a tool for basic transformation, not the stuff you're asking for. But maybe you could ask customer support from this company on how to do what you're trying to...
Edit: At a second glance it seems like you could create a VB script. I don't know if it would work but I'd already vote against it...
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply