I have an Execute Result Set which I loop through. Each record contains an email field(column). I would like to email only the record from the result set that is associated with the email using the automation feature. ie As I loop through and grab each record I would like to email only that record to the email address found within the record. Currently I’m sending the entire output to each email.
Not sure if I’m approaching this from the right direction. Any help or suggestions would be appreciated.
You have the right idea but it sounds like you need to execute a query that returns only the values for that user and export that. In other words, the loop would execute a query and would build a result set that contained only those records associated to that email address. You can use the loop variable in your query.
If you need assistance, send me your tas file and two excel spread sheets with sample data of the table that contains the list of email values and the table data that needs to be filtered. I can make a sample.
Yes, I was thinking I’ll need to execute a query to return the emails with say the employee num in one query. Then loop through that result and query the database using the employee_num to bring back to data to email out.
SELECT T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO AS “Employee Number”,
T803_SECURITY_USER.T803F300_EMAIL_ADDRESS AS “Email”
FROM ( AURDEV10.T001_BASIC_DETAILS T001_BASIC_DETAILS
INNER JOIN
AURDEV10.T035_INDUCTIONS T035_INDUCTIONS
ON (T001_BASIC_DETAILS.T001F010_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY))
INNER JOIN
AURDEV10.T803_SECURITY_USER T803_SECURITY_USER
ON (T803_SECURITY_USER.T803F050_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY)
WHERE (T001_BASIC_DETAILS.T001F015_EMPLOYEE_STATUS = ‘ACTIVE’)
AND (T035_INDUCTIONS.T035F020_EVENT_TYPE_CODE = ‘NAV’)
AND (T035_INDUCTIONS.T035F075_CONCLUDED_OK = ‘F’)
and ((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM) >= 20)
SELECT T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO AS “Employee_Number”,
T001_BASIC_DETAILS.T001F345_NAME_FOR_HEADINGS AS “Full Name”,
to_char(T001_BASIC_DETAILS.T001F250_DATE_COMMENCED, ‘dd-MON-yyyy’) AS “Date Commenced”,
to_char(T035_INDUCTIONS.T035F035_DATE_FROM, ‘dd-MON-yyyy’) AS “Induction Date From”,
to_char(T035_INDUCTIONS.T035F040_DATE_TO, ‘dd-MON-yyyy’) AS “Induction Date To”,
decode(T035_INDUCTIONS.T035F075_CONCLUDED_OK, ‘F’, ‘No’) AS “Completed?”,
to_char((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM),9999) as “Elapsed Days”
FROM ( AURDEV10.T001_BASIC_DETAILS T001_BASIC_DETAILS
INNER JOIN
AURDEV10.T035_INDUCTIONS T035_INDUCTIONS
ON (T001_BASIC_DETAILS.T001F010_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY))
INNER JOIN
AURDEV10.T803_SECURITY_USER T803_SECURITY_USER
ON (T803_SECURITY_USER.T803F050_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY)
WHERE T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO = :Employee_Num
AND (T001_BASIC_DETAILS.T001F015_EMPLOYEE_STATUS = ‘ACTIVE’)
AND (T035_INDUCTIONS.T035F020_EVENT_TYPE_CODE = ‘NAV’)
AND (T035_INDUCTIONS.T035F075_CONCLUDED_OK = ‘F’)
and ((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM) >= 20) Emp_emails.xls (13.5 KB)
Yes, I was thinking I’ll need to execute a query to return the emails with say the employee num in one query. Then loop through that result and query the database using the employee_num to bring back to data to email out.
SELECT T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO AS “Employee Number”,
T803_SECURITY_USER.T803F300_EMAIL_ADDRESS AS “Email”
FROM ( AURDEV10.T001_BASIC_DETAILS T001_BASIC_DETAILS
INNER JOIN
AURDEV10.T035_INDUCTIONS T035_INDUCTIONS
ON (T001_BASIC_DETAILS.T001F010_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY))
INNER JOIN
AURDEV10.T803_SECURITY_USER T803_SECURITY_USER
ON (T803_SECURITY_USER.T803F050_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY)
WHERE (T001_BASIC_DETAILS.T001F015_EMPLOYEE_STATUS = ‘ACTIVE’)
AND (T035_INDUCTIONS.T035F020_EVENT_TYPE_CODE = ‘NAV’)
AND (T035_INDUCTIONS.T035F075_CONCLUDED_OK = ‘F’)
and ((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM) >= 20)
SELECT T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO AS “Employee_Number”,
T001_BASIC_DETAILS.T001F345_NAME_FOR_HEADINGS AS “Full Name”,
to_char(T001_BASIC_DETAILS.T001F250_DATE_COMMENCED, ‘dd-MON-yyyy’) AS “Date Commenced”,
to_char(T035_INDUCTIONS.T035F035_DATE_FROM, ‘dd-MON-yyyy’) AS “Induction Date From”,
to_char(T035_INDUCTIONS.T035F040_DATE_TO, ‘dd-MON-yyyy’) AS “Induction Date To”,
decode(T035_INDUCTIONS.T035F075_CONCLUDED_OK, ‘F’, ‘No’) AS “Completed?”,
to_char((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM),9999) as “Elapsed Days”
FROM ( AURDEV10.T001_BASIC_DETAILS T001_BASIC_DETAILS
INNER JOIN
AURDEV10.T035_INDUCTIONS T035_INDUCTIONS
ON (T001_BASIC_DETAILS.T001F010_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY))
INNER JOIN
AURDEV10.T803_SECURITY_USER T803_SECURITY_USER
ON (T803_SECURITY_USER.T803F050_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY)
WHERE T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO = :Employee_Num
AND (T001_BASIC_DETAILS.T001F015_EMPLOYEE_STATUS = ‘ACTIVE’)
AND (T035_INDUCTIONS.T035F020_EVENT_TYPE_CODE = ‘NAV’)
AND (T035_INDUCTIONS.T035F075_CONCLUDED_OK = ‘F’)
and ((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM) >= 20) Outstanding_Navigate_Inductions-Individual.tas (7.73 KB)
Yes, I was thinking I’ll need to execute a query to return the emails with say the employee num in one query. Then loop through that result and query the database using the employee_num to bring back to data to email out.
SELECT T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO AS “Employee Number”,
T803_SECURITY_USER.T803F300_EMAIL_ADDRESS AS “Email”
FROM ( AURDEV10.T001_BASIC_DETAILS T001_BASIC_DETAILS
INNER JOIN
AURDEV10.T035_INDUCTIONS T035_INDUCTIONS
ON (T001_BASIC_DETAILS.T001F010_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY))
INNER JOIN
AURDEV10.T803_SECURITY_USER T803_SECURITY_USER
ON (T803_SECURITY_USER.T803F050_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY)
WHERE (T001_BASIC_DETAILS.T001F015_EMPLOYEE_STATUS = ‘ACTIVE’)
AND (T035_INDUCTIONS.T035F020_EVENT_TYPE_CODE = ‘NAV’)
AND (T035_INDUCTIONS.T035F075_CONCLUDED_OK = ‘F’)
and ((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM) >= 20)
SELECT T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO AS “Employee_Number”,
T001_BASIC_DETAILS.T001F345_NAME_FOR_HEADINGS AS “Full Name”,
to_char(T001_BASIC_DETAILS.T001F250_DATE_COMMENCED, ‘dd-MON-yyyy’) AS “Date Commenced”,
to_char(T035_INDUCTIONS.T035F035_DATE_FROM, ‘dd-MON-yyyy’) AS “Induction Date From”,
to_char(T035_INDUCTIONS.T035F040_DATE_TO, ‘dd-MON-yyyy’) AS “Induction Date To”,
decode(T035_INDUCTIONS.T035F075_CONCLUDED_OK, ‘F’, ‘No’) AS “Completed?”,
to_char((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM),9999) as “Elapsed Days”
FROM ( AURDEV10.T001_BASIC_DETAILS T001_BASIC_DETAILS
INNER JOIN
AURDEV10.T035_INDUCTIONS T035_INDUCTIONS
ON (T001_BASIC_DETAILS.T001F010_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY))
INNER JOIN
AURDEV10.T803_SECURITY_USER T803_SECURITY_USER
ON (T803_SECURITY_USER.T803F050_WAMI_KEY =
T035_INDUCTIONS.T035F005_WAMI_KEY)
WHERE T001_BASIC_DETAILS.T001F005_EMPLOYEE_NO = :Employee_Num
AND (T001_BASIC_DETAILS.T001F015_EMPLOYEE_STATUS = ‘ACTIVE’)
AND (T035_INDUCTIONS.T035F020_EVENT_TYPE_CODE = ‘NAV’)
AND (T035_INDUCTIONS.T035F075_CONCLUDED_OK = ‘F’)
and ((sysdate - T035_INDUCTIONS.T035F035_DATE_FROM) >= 20) OVERDUE_NAV_DETAILS.xls (13.5 KB)
Okay, see attached sample script. To run the sample copy all files into C:\temp. Then create a connection to the two excel files.
I used your script to start with and added a query in the middle of the loop to generate an excel report that only retrieved the rows for that employee. This is done through binding the var Employe_num to the employee num in the table. I then appended the employee number to end of the excel file. This should do what you want.