Orange Link Arrow disappears when I use UNION ALL Clause. How to reappear?

[leon_laikan]

Dear All,

I am new to SQL Queries. I am writing my very first Query to display all details about A/P Invoices and A/P Credit Memos so that the staff at the Accounting Dept can verify rapidly all details concerning A/P Invoices and Credit Memos which they have input. In particular, I want them to make sure they have posted all invoices to the correct GL Account.

My Query works fine when I run only the top half (for invoices) or the only the bottom half (for credit memos). The GOLDEN LINK ARROW appears in the "Doc No" column (2nd column), and this is what I want.

But when I unite the 2 halves using UNION ALL, the golden link arrow disappears. I understand that the UNION clause makes it disappear. I tried "FOR BROWSE" but it does not work when the UNION clause is present.

Is there a way to make the Link arrow re-appear? As I am a newbee, if the solution requires complex topics, please give me the exact codes to make my query work.

Thanks

Leon

-

---

TABLES----

---

--T0 = OCRD = Business Partner --T1 = PCH1 = A/P Invoice - Rows --T2 = OACT = G/L Accounts --T3 = OVTG = Tax Definition

--T4 = OPCH = A/P Invoice --T5 = OJDT = Journal Entry --T6 = OSLP = Sales Employee

-

---

SELECT

CASE TransType

WHEN '18' THEN 'PU'

WHEN '19' THEN 'PC'

ELSE 'Error'

END 'TxnTp',

T4.[DocNum] AS 'Doc No',

T1.[DocDate] AS 'Posting Dt',

T1.[ImportLog] AS 'Ship Code',

LEFT(T2.[AcctName],50) AS 'Account Name',

LEFT(T0.[CardName],25) AS 'BP Name',

T1.[LineTotal] AS 'Row Total',

LEFT(T1.[Dscription],40) AS 'Item/Service Description',

T3.[Name] AS 'VAT Description',

T1.[LineVat] AS 'Tax Amount',

T1.[GTotal] AS 'Gross Total',

'Opt >>>' AS 'Optional',

CASE TransType

WHEN '18' THEN 'PU ' + CONVERT(varchar(10), DocNum)

WHEN '19' THEN 'PC' + CONVERT(varchar(10), DocNum)

ELSE 'Error'

END 'Posting Ref',

T4.[TaxDate] AS 'Document Dt',

T1.[DocDate] AS 'Posting Dt',

CASE

WHEN MONTH (T1.[DocDate]) = MONTH (T4.[TaxDate]) THEN 'Yes'

ELSE 'No ! ! !'

END 'Same MTH?',

T1.[VatGroup] AS 'VAT Code',

T0.[LicTradNum] AS 'VAT Regn No',

T6.[SlpName] AS 'Input By',

T4.[CreateDate] AS 'System Dt'

FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[PCH1] T1 ON T1.[BaseCard] = T0.[CardCode]

INNER JOIN [dbo].[OACT] T2 ON T1.[AcctCode] = T2.[AcctCode]

LEFT OUTER JOIN [dbo].[OVTG] T3 ON T1.[VATGroup] = T3.[Code]

-- I am not sure if this is the correct join. What will happen if VAT Code is blank?

INNER JOIN [dbo].[OPCH] T4 ON T1.[DocEntry] = T4.[DocEntry]

INNER JOIN [dbo].[OSLP] T6 ON T4.[SlpCode] = T6.[SlpCode]

INNER JOIN [dbo].[OJDT] T5 ON T4.[TransId] = T5.[TransId]

WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) ) AND T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112) )

UNION ALL

-

---

-

---

TABLES----

---

--T0 = OCRD = Business Partner --T1 = RPC1 = A/P Credit Memo - Rows --T2 = OACT = G/L Accounts --T3 = OVTG = Tax Definition

--T4 = Orpc = A/P Credit Memoe --T5 = OJDT = Journal Entry --T6 = OSLP = Sales Employee

-

---

SELECT

CASE TransType

WHEN '18' THEN 'PU'

WHEN '19' THEN 'PC'

ELSE 'Error'

END 'TxnTp',

T4.[DocNum] AS 'Doc No',

T1.[DocDate] AS 'Posting Dt',

T1.[ImportLog] AS 'Ship Code',

LEFT(T2.[AcctName],50) AS 'Account Name',

LEFT(T0.[CardName],25) AS 'BP Name',

T1.[LineTotal] AS 'Row Total',

LEFT(T1.[Dscription],40) AS 'Item/Service Description',

T3.[Name] AS 'VAT Description',

T1.[LineVat] AS 'Tax Amount',

T1.[GTotal] AS 'Gross Total',

'Opt >>>' AS 'Optional',

CASE TransType

WHEN '18' THEN 'PU ' + CONVERT(varchar(10), DocNum)

WHEN '19' THEN 'PC' + CONVERT(varchar(10), DocNum)

ELSE 'Error'

END 'Posting Ref',

T4.[TaxDate] AS 'Document Dt',

T1.[DocDate] AS 'Posting Dt',

CASE

WHEN MONTH (T1.[DocDate]) = MONTH (T4.[TaxDate]) THEN 'Yes'

ELSE 'No ! ! !'

END 'Same MTH?',

T1.[VatGroup] AS 'VAT Code',

T0.[LicTradNum] AS 'VAT Regn No',

T6.[SlpName] AS 'Input By',

T4.[CreateDate] AS 'System Dt'

FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[RPC1] T1 ON T1.[BaseCard] = T0.[CardCode]

INNER JOIN [dbo].[OACT] T2 ON T1.[AcctCode] = T2.[AcctCode]

LEFT OUTER JOIN [dbo].[OVTG] T3 ON T1.[VATGroup] = T3.[Code]

-- I am not sure if this is the correct join. What will happen if VAT Code is blank?

INNER JOIN [dbo].[ORPC] T4 ON T1.[DocEntry] = T4.[DocEntry]

INNER JOIN [dbo].[OSLP] T6 ON T4.[SlpCode] = T6.[SlpCode]

INNER JOIN [dbo].[OJDT] T5 ON T4.[TransId] = T5.[TransId]

WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) ) AND T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112) )

[Former Member]

Hi,

Welcome you post on the forum.

Unfortunately, this is unavoidable. Whenever you use UNION ALL Clause, link arrow will not be available. You may add two branch queries beside this main query when you need arrow, run those queries.

You may found more query info from my book: Mastering SQL Queries for SAP Business One

Thanks,

Gordon

[leon_laikan]

Dear Gordon,

Thank you for your lightning fast reply.

I recently bought your WONDERFUL book online from PACKT, and I would not have been able to write my query without it. It is already helping me a lot to help my accounting staff working on SAP B1.

However, I sent my question because on page 94 of your book, it is stated:

Some queries will hide link arrows BY DEFAULT. Here is a partial list:

Queries with a Union clause....

The way the statement is phrased led me to think that maybe there is a way to bypass the DEFAULT.

Too bad if there is no simple solution!

I hope you will continue writing other great books on SAP Queries with lots of more sophisticated codes that will enable newbies like me to create great queries.

I close this question, but if ever you find the trick that will unhide the link arrow, who knows... please let me knowl. Thanks a lot ! ! !

Leon Lai

Mauritius

[Former Member]

By default I mean it is the default that the link arrow will not be present and there is no work around to bypass it.

I should use the word it is not possible to display link arrow for those cases. I will change it in next edition if the book sales well.

Thanks for you comments.

[Former Member]

Dear Leon/Gordon,

In my case it worked well. I didn't get any difficulty to get the solution.

I am using 'Union All' to link multiple table and taken object type from each table.

Inside of hyperlink I used condition to link each nunber to its particular document.

E.g.

IF ({Command.ObjectType}=13) THEN

'http://$b1$/link?table=OINV&key='+Totext(Command.DocEntry,0,"","")

ELSE IF ({Command.ObjectType}=14) THEN

'http://$b1$/link?table=ORIN&key='+Totext(Command.DocEntry,0,"","")

Regards,

Vinod.