Skip to Content
0

Single query with Invoices & Cr Notes listed?

Mar 06 at 03:23 PM

33

avatar image

I have two separate queries

One for Invoices List & one for Credit Note list.

Is it possible to get both lists into one query as they have the same fields?

Query 1

SELECT T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocType], T0.[VatSum], T0.[DocTotal], (T0.[DocTotal] - T0.[VatSum]) AS 'Ex-Vat Total' FROM OINV T0 WHERE T0.[DocDate] >=[%0]

Query 2

SELECT T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocType], T0.[VatSum], T0.[DocTotal], (T0.[DocTotal] - T0.[VatSum]) AS 'Ex-Vat Total' FROM ORIN T0 WHERE T0.[DocDate] >=[%0]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Jimmy Liang Mar 06 at 05:13 PM
1

Hi Eoin,

You may add a "UNION ALL" to combine both sections:

SELECT T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocType], T0.[VatSum], T0.[DocTotal], (T0.[DocTotal] - T0.[VatSum]) AS 'Ex-Vat Total' FROM OINV T0 WHERE T0.[DocDate] >=[%0]
UNION ALL
SELECT T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocType], T0.[VatSum], T0.[DocTotal], (T0.[DocTotal] - T0.[VatSum]) AS 'Ex-Vat Total' FROM ORIN T0 WHERE T0.[DocDate] >=[%0]

Cheers,

Jimmy

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank You Jimmy, much appreciated the reply.

0