Skip to Content
0
Former Member
Jul 09, 2012 at 02:19 PM

drill-down arrows are in a query, but not in an alert

76 Views

Hello guys,

I have this SQL query which I need to use in an alert with drill-down links to orders:

SELECT TOP 10000

ORDR.DocEntry,

ORDR.DocNum,

T1.CardCode,

T1.CardName,

DATEDIFF(day, T2.Oldest, GETDATE()) AS [Dnů po splatnosti nejstarší faktury],

T2.Invoices AS [Počet faktur po splatnosti],

T2.NotPaid AS [Dluží celkem]

FROM

ORDR /* kvůli drill-down šipce ve výstraze */

JOIN (

SELECT /* zakázky, které mají min. jednu položku v limitu */

ORDR.DocNum,

ORDR.CardCode,

ORDR.CardName

FROM

ORDR

JOIN

RDR1

ON

RDR1.DocEntry = ORDR.DocEntry

WHERE

DATEDIFF(day, GETDATE(), RDR1.ShipDate) BETWEEN 0 AND 5

GROUP BY

ORDR.DocNum,

ORDR.CardCode,

ORDR.CardName

) T1

ON

T1.DocNum = ORDR.DocNum

JOIN (

SELECT /* hříšníci po splatnosti */

OINV.CardCode,

MIN(OINV.DocDueDate) AS [Oldest],

COUNT(*) AS [Invoices],

SUM(OINV.DocTotal - OINV.PaidToDate) AS [NotPaid]

FROM

OINV

WHERE

DATEDIFF(day, OINV.DocDueDate, GETDATE()) >= 1 AND

OINV.DocStatus = 'O'

GROUP BY

OINV.CardCode

) T2

ON

T2.CardCode = T1.CardCode

FOR BROWSE

I use two sub-queries in it. Why? Just to be able to safely use necessary GROUP BY in both of them. The main SELECT doesn't utilize GROUP BY so the links in DocEntry, DocNum to their apropriate tables should not be destroyed.

This works well when running the query directly as you may see in the picture - arrows_YES.png.

But the drill-down arrows disappear when the query is run as an alert.

Do you know why?

SAP B1 8.82

Attachments

arrows_NO.png (60.3 kB)
arrows_YES.png (38.3 kB)