Skip to Content
avatar image
Former Member

Alert query not working and getting alert

Hi experts

i had created an UDF and its type is Alphanumeric, i had created a query based on that UDF .(This is the UDF feild : T0.[U_IQId] )

when ever i change the data in this UDF. i need to get alert day wise

i had set the as that in the alert window

but the sys is not giving the msg . could u pls tell me what will be issue for that

i had attached both the alert window and the below given is the query

NB: IF there is any value existing in this UDF, and if we change it for the first time, it is no giving alert, but if we change it for the second time, it will pop up

i need to get the alert in the first time itself

pls help to sort it out

QUERY:

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

SELECT distinct T0.[U_EMPOLD_ID] as'Employee ID', T0.[empID] as 'SAP ID', isnull(T0.[firstName],'')+' '+isnull(T0.[middleName],'')+' '+isnull(T0.[lastName],'') as 'Full Name', T2.[Remarks] AS 'Dept Name',

T0.[U_IQId]as 'New Iqama Id',T1.[U_IQId]as 'Old Iqama Id',T0.[U_SponsName] As 'Sponsor Name',T0.[U_JoinDate] As 'Enrollement Date',T1.[UpdateDate] as 'Update Date'

FROM OHEM T0

INNER JOIN OUDP T2 ON T0.dept=T2.Code

INNER JOIN AHEM T1 ON T0.empID = T1.EmpID

WHERE getdate() >= T1.[UpdateDate] AND (T1.[UpdateDate]+2) >= getdate() AND

T0.[U_IQId]<> T1.[U_IQId]

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

regards

Alert Window.jpg (47.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jan 04, 2015 at 11:36 AM

    Hi,

         It tried your query on my system and it is working, it is giving me alerts of changed history in the UDF.

    SELECT distinct

    T0.[empID] as 'SAP ID', isnull(T0.[firstName],'')

    +' '+isnull(T0.[middleName],'')+' '+isnull(T0.[lastName],'') as 'Full Name',

      T2.[Remarks] AS 'Dept Name',

    T0.[U_IQId]as 'New Iqama Id',T1.[U_IQId]as 'Old Iqama Id',

    T1.[UpdateDate] as 'Update Date'

    FROM OHEM T0

    INNER JOIN OUDP T2 ON T0.dept=T2.Code

    INNER JOIN AHEM T1 ON T0.empID = T1.EmpID

    WHERE getdate() >= T1.[UpdateDate] AND (T1.[UpdateDate]+2) >= getdate() AND

    T0.[U_IQId]<> T1.[U_IQId]

    Pls try the above query and set the alert frequency for 1 minute to test and let me know the result.

    Regards,

    Manish

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06, 2015 at 02:34 AM

    Try this:

    SELECT distinct T0.[empID], max(T0.[U_IQId]), max(T1.[U_IQId]) FROM OHEM T0  INNER JOIN AHEM T1 ON T0.empID = T1.empid WHERE (T1.[U_IQId] <> T0.[U_IQId] OR T1.[U_IQId] is null ) and datediff(dd,  T0.[UpdateDate] ,getdate()) = 0 group by

    T0.[empID]

    Let me know your query result.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 21, 2015 at 07:49 AM

    Have you tried with last reply?

    Add comment
    10|10000 characters needed characters exceeded

    • Your original request is to get an alert, when there is update in UDF. If you add +1 day, then you will get an alert after a day.

      Please be clearer with your requirement to fix issus with lesser time.