Límite de líneas en resultado de alerta programada

[Former Member]

Buenos días para todos en el foro,

Quisiera preguntar si a alguno de ustedes le ha sucedido que crean una consulta SQL y la guardan en el Query Manager para crear una nueva alerta, pero al ejecutarse la alerta genera un resultado de líneas menor que al ejecutar la consulta SQL por el Query manager.

Ejemplo de lo que nos sucede con un cliente:

Se crea la siguiente consulta sql y se almacena en el Query manager

select T0.ItemCode, T0.ItemName, T1.OnHand, T1.WhsCode 
from OITM T0
left join OITW T1 on t0.ItemCode=T1.ItemCode
where T1.WhsCode like'%0%' and T1.OnHand>0

Al ejecutar la consulta, se generan 1625 líneas como resultado

Pero al visualizar la alerta programada, se muestra un límite de 378 líneas.

Ya valide con otra base de datos de pruebas y el resultado es la misma cantidad de líneas o menor.

En base a esto, ¿Saben ustedes  si la gestión de alertas tiene un límite de líneas o debemos realizar algún procedimiento para que nos permita visualizar el total de la información en la alerta programada?

De antemano gracias por su colaboración y quedo atento a sus comentarios.

Cordial Saludo,

William Manchola.

[former_member203638 · Accepted solution]

Hola William,solo un numero limitado de renglones te arroja query manager, te dejo la nota.

 874145  - Queries in alerts may not return the full query result 
Symptom
When a specific query is run from the 'Query Generator' the results are  quite long. However when the same query is run as part of an alert not all of the results are displayed.
Cause
Application Functionality
Solution
An alert based on a query returning long results will not display the complete list should the result be over a certain size. This implies that a query which returns, for example only the invoice number, 200 lines in the query generator may only return 123 lines in the alert.

Workaround:

1) If possible limit the query in the 'where' condition.
For Example:
If the query in the alert returns all invoices that exist in the database. It may be pertinent to limit the display to those invoices added on that day only or to display only invoices that have the status 'Open'

E.G.
           Instead of 
           select T0.docnum from oinv T0 
           Write 
           select T0.docnum from oinv T0 where datediff(day,getdate(),t0. updatedate) =0
To save the query in the query manager:

To save the query open the query generator (Reports -> query    generator).
           Press 'Execute' without entering any table information.  
           Click on the pencil icon to the left of the statement 'Select   *'  This opens the query for editing. 
           Delete the words 'SELECT *' and copy in the query below.  
           Now press 'save' and the 'Query Manager' opens. 
           Click on a title from the list displayed.  In the 'Query Name'  field  type the name the query will be saved under. 
           Press Save and the query is saved in the query manager.

To create an alert:

           Open the 'Alerts Management' (Administration ->  Alerts Management). 
           Change to add mode by choosing Data -> Add. 
           Give the alert a name e.g. Invoice Document Numbers. 
           Select the 'active' option. 
           To select the saved query click on 'open saved query' and select the query from the query manager. 
           Under the heading 'Frequency' choose the desired timing for the reoccurrence of the alert e.g. "Every '1' 'Day'",  "At '9:00Am'" 
           Choose "Add" to save the alert. 
2) In case the above is not feasable please verify the following:
a) Limit the resultset in the alert by modifying the query as follows:
 DECLARE @rowsperpage INT
 DECLARE @start INT
 SET @start = 1
 SET @rowsperpage = 50
 SELECT * FROM
  (
  SELECT row_number() OVER (ORDER BY sortfield) --add a sorting field for the resultset
  AS rownum,
    -- add the requried fields here
  FROM  -- add the required table(s) here
  WHERE  -- add the required conditions here
  )
 AS A
 WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage)
 This will result in the first 50 rows of the resultset.
b) Create duplicates of the query changing the value of the @start variable. Create as many queries as needed to be sure all rows are included (estimate the maximum number of rows expected).
c) Create an alert for each query created with the same settings as the original alert.
3) In case none of the above is feasible there is the workaround of creating an alert telling the user to run a certain saved report/query
and export the results to Excel.

Saludos

Alessandro.

[Former Member · Accepted solution]

Buenas tardes Alessandro,

Muchas gracias, me sirvió bastante su ayuda con la nota SAP enviada.

Cordial Saludo,

William Manchola A.