Skip to Content
avatar image
Former Member

Alert Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Oct 25, 2017 at 09:06 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • --------------------

      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)
  • avatar image
    Former Member
    Oct 25, 2017 at 06:04 AM

    Updates??

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 24, 2017 at 12:45 PM

    Any Updates on the above question??

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 25, 2017 at 07:00 AM

    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

    Add comment
    10|10000 characters needed characters exceeded