cancel
Showing results for 
Search instead for 
Did you mean: 

Golden Arrow with UNION ALL

kenan_jaddeny
Contributor

Hi Community,

I have the following report to get a small Customer Account Statement that contains the Incoming Payments and A/R Invoices but in Draft status,
I append the both reports (Incoming Payments, A/R Invoices) using the "UNION ALL" sql keyword, but whenever we're using it, the golden arrow functionality is being losen, what is the solution for this one?

we're not using BoyumIT and we don't have to use it as the SAP B1 default functionalities should support it!

Regards,

/* SELECT FROM [dbo].[OCRD] D0 */
DECLARE @BPName AS NVARCHAR(MAX)
/* WHERE */
SET @BPName = /* D0.[CardName] */ '[%0]';

WITH CTE AS (
    SELECT
        'Invoice'[Type],T0.[DocEntry]'No.',T0.[DocDate]'Date',
        '' 'Final Customer Code','' 'Final Customer',
        T0.[CardCode]'Dealer Code',T0.[CardName]'Dealer',
        '' 'Cash Account',
        T0.[DocCur]'Currency',T0.[DocRate]'Rate',
        T0.[VatSum]'KDV',T0.[VatSumSy]'KDV $',T0.[DiscSum]'Discount',T0.[DiscSumSy]'Discount $',T0.[DocTotal]'Amount UZS',T0.[DocTotalSy]'Amount$'
    FROM
        ODRF T0
        LEFT JOIN OCRD T1 ON T1.[CardCode]=T0.[CardCode]
        LEFT JOIN OCRG T2 ON T2.[GroupCode]=T1.[GroupCode]
    WHERE
        T2.[GroupType]='C'
UNION ALL 
    SELECT
        'Payment'[Type],T0.[DocEntry]'No.',T0.[DocDate]'Date',
        T2.[CardCode]'Final Customer Code',T0.[CardName]'Final Customer',
        CASE WHEN T3.[CardCode] IS NULL THEN T2.[CardCode] ELSE T3.[CardCode] END AS 'Dealer Code',
        CASE WHEN T3.[CardCode] IS NULL THEN T2.[CardName] ELSE T3.[CardName] END AS 'Dealer',
        T1.[AcctName]'Cash Account',
        T0.[DocCurr]'Currency',T0.[SysRate]'Rate',
        NULL 'KDV',NULL 'KDV $',NULL 'Discount',NULL'Discount $',T0.[CashSum]*(-1)'Amount UZS',T0.[CashSumSy]*(-1)'Amount$'
    FROM
        OPDF T0
        LEFT JOIN OACT T1 ON T0.[CashAcct]=T1.[AcctCode] -- Accounts Name
        LEFT JOIN OCRD T2 ON T0.[CardCode]=T2.[CardCode] -- Final Customers Details
        LEFT JOIN OCRD T3 ON T2.[FatherCard]=T3.[CardCode] -- Dealer Details
    WHERE
        T0.[ObjType]='24'
)
SELECT * 
FROM CTE 
wHERE [Dealer]=@BPName
ORDER BY [Date] ASC

Accepted Solutions (0)

Answers (2)

Answers (2)

Johan_H
Active Contributor

Hi,

When the query contains two different tables, the B1 sql parser cannot determine to which table the arrow should refer to. This is a limitation of the system.

Regards,

Johan

msundararaja_perumal
Active Contributor

kenan.jaddeny

Have you tried use 'for browse'? use it outside the union all.

Example:

Select * From

(

Select A.DocDate From OINV

union all

Select A.DocDate From ORIN

)T for browse

Thanks.