Skip to Content
0
Jun 27, 2011 at 11:11 AM

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

272 Views

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) )