Alert Query

[Former Member]

Hi Everyone,

Below is my query in HANA, it returns the result but alert is not triggered using this query.

SELECT Top 1 A."DocDate", A."DocNum", A."Filler" AS From_Location , A."ToWhsCode", A."Comments" FROM OWTQ AS A WHERE A."DocDate" = CURRENT_DATE Order By A."DocNum" Desc

Thanks in advance

Waqas

[MD1]

please try

SELECT
Top 1 A."DocDate",
A."DocNum",
A."Filler" AS From_Location ,
A."ToWhsCode",
A."Comments"
FROM OWTQ AS A
---------WHERE A."DocDate" = CURRENT_DATE
Where DAYS_BETWEEN (CURRENT_DATE,A."DocDate") =0
Order By A."DocNum" Desc

[Former Member]

No Alert still.

[MD1]

please provide SAP Entry with example (Screen shot)

[MD1]

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

First

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

SELECT
T1."DocNum",
T1."CardName",
T2."ItemCode",
T2."Dscription",
T2."Quantity",
T0."Quantity",
T0."BatchNum"
FROM IBT1 T0
LEFT OUTER JOIN OWTQ T1 ON T0."BaseType" = T1."ObjType"
LEFT OUTER JOIN WTQ1 T2 ON T1."DocEntry" = T2."DocEntry"
WHERE T0."Direction" = '2'
AND T0."BaseNum" = T1."DocNum"
GROUP BY T1."DocNum",
T1."CardName",
T2."ItemCode",
T2."Dscription",
T2."Quantity",
T0."Direction",
T0."Quantity",
T0."BatchNum"
ORDER BY T1."DocNum"

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

Second

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

SELECT
T0."DocDate",
T0."DocNum",
T0."Filler" AS From_Location ,
T0."ToWhsCode",
T0."Comments"
FROM OWTQ T0
Where DAYS_BETWEEN (CURRENT_DATE,T0."DocDate") =0
Order By T0."DocNum" Desc

[Johan_H]

Hi Waqas,

It may be because of this: A."DocDate" = CURRENT_DATE and because of a possible minute difference in how B1 executes a query in the client, and directly in the HANA database.

The DocDate field contains the date with time stamp 00:00:00, whereas CURRENT_DATE returns the date including the time at the moment the query is run. When you run the query in the B1 client's Query Generator, the client's code conveniently drops the time from CURRENT_DATE. This does not necessarily happen when the query is run on behalf of the alert.

You can test if this is the case, by writing a manual date instead of the CURRENT_DATE function, and testing the alert then.

Regards,

Johan

[Former Member]

Hi,

I have changed query to this:

SELECT Top 1 CAST(A."DocDate" AS DATE), A."DocNum", A."Filler" AS From_Location , A."ToWhsCode", A."Comments" FROM OWTQ AS A ---------WHERE A."DocDate" = CURRENT_DATE Where DAYS_BETWEEN (CURRENT_DATE,CAST(A."DocDate" AS DATE)) =0 Order By A."DocNum" Desc

Its now showing only date no timestamp, still result is showing but alert is not triggering. Services are okay as Built-in alerts works fine.

[Johan_H]

Hi Waqas,

Could you please post a screenshot of the Alerts Management form for your alert?

Regards,

Johan

[Former Member]

Updates??

[Former Member]

Any Updates on the above question??