Skip to Content

Single query with Invoices & Cr Notes listed?

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]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 06 at 05:13 PM

    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

    Add comment
    10|10000 characters needed characters exceeded