Hi There,
I'm battling a bit with the below script. I have a nested for loop which I'm using to retrieve data from each server. Thanks to some help I received, this now works. The problem now comes with the text I've marked in red, where I'm trying to add data from some additional tables. What happens is that when it cycles through the loop the second time, it now creates a table called "Data-1" and then does a synthetic join (not ideal).
Is there a way to avoid this? I've tried doing the join at various other points without success. The reason I'm using the join is that not all departments in the department table receive text messages with client scores.
For each CNTR in 20, 40
if $(CNTR) = 20 then
set zServer = COMPANY1 ;
ODBC CONNECT32 TO COMPANY1 (XUserId is acCHQAFJSKZCGSdJODbSHID, XPassword is UROCRAFGJSbKTaJOBTbGWSNL);
ENDIF
if $(CNTR) = 40 then
set zServer = COMPANY2 ;
ODBC CONNECT32 TO COMPANY2 (XUserId is GdPSWAFJSKZCGSdJODbSHbD, XPassword is EQTDcAFGJSbKTaJOBTbGWSdN);
ENDIF
For each Score in 'Excellent', 'Good','Bad'
[DATA]:
LOAD recno() as [ID INLINE],
"Commslog_Key",
"Comm_Crt_Date",
"Comm_Comm_Type",
"Comm_Area_Cde",
if(Comm_Area_Cde = 'C', 'SMS linked to a claim number','SMS linked to a policy number') as "SMS",
"Comm_Status",
"ReferenceNo",
"Usr_Full_Name" as "Username",
"Comm_Reference_Cde",
"Comm_Report_Cde",
"Comm_Template",
"Comm_Recipients",
"In_Received_Date",
"In_Message",
'$(Score)' as Score,
'$(zDate)' as Today,
'$(StartDate)' as StartDate,
'$(EndDate)' as EndDate,
'$(zServer)' as Server;
SQL SELECT *
FROM Softsure.dbo."View_SMSInBox"
where Comm_Crt_Date >= '$(StartDate)' and Comm_Crt_Date <= '$(EndDate)'
AND Comm_Report_Cde = '17'
and SOUNDEX(In_Message) = SOUNDEX('$(Score)');
NEXT
Join(DATA)
UserTable:
LOAD
"Usr_Full_Name" as "Username",
"Usr_Department_Cde" as "DepartmentCode";
SQL SELECT *
FROM Softsure.dbo."View_User_Roles" where Usr_Department_Cde IS NOT NULL;
Join(DATA)
DepartmentTable:
//-------- Start Multiple Select Statements ------
LOAD "Dept_Key" as "DepartmentCode",
"Dept_Name",
"ManagerName";
SQL SELECT *
FROM Softsure.dbo."View_Departments";
//-------- End Multiple Select Statements ------
NEXT
