Skip to Content
0

Alert Query

Oct 24, 2017 at 11:41 AM

45

avatar image
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Mohd Danish Oct 25, 2017 at 09:06 AM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

No Alert still.

0

please provide SAP Entry with example (Screen shot)

0

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

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

m85hj.png (280.2 kB)
0
avatar image
Former Member Oct 25, 2017 at 06:04 AM
0

Updates??

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 24, 2017 at 12:45 PM
0

Any Updates on the above question??

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Oct 25, 2017 at 07:00 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
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.

1

Hi Waqas,

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

Regards,

Johan

0