Skip to Content

Help with Union All function

Hello,

I am trying to make a query that will give me the same information as the built in Sales Analysis report . So far I have managed to get two separate queries working - one for sales invoices and one for sales credits per customer. Both queries work OK on their own but when I try and merge them with a Union All function I get the following error:

T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[Comments], T0.[DocTotal]*-1 as 'Doc Total', T0.[DiscSum]*-1 as 'Discount',(((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum])*-1 as 'Base Price', ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*-1 as 'Net Value after discount', T0.[GrosProfit]*-1 as 'Gross Profit', Case when ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) = 0 then 0 else (T0.[GrosProfit]/((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*100*-1) end as 'GP Ratio' FROM ORIN T0 WHERE T0.[CardName] Like '%%buy%%' and T0.[DocDate] >= [%0] and T0.[DocDate] <=[%1] and T0.[CANCELED] = 'n' ORDER BY T0.[DocNum]

union all

T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[Comments], T0.[DocTotal] as 'Doc Total', T0.[DiscSum] as 'Discount',(((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) as 'Base Price', ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) as 'Net Value after discount', T0.[GrosProfit] as 'Gross Profit', Case when ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) = 0 then 0 else (T0.[GrosProfit]/((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*100) end as 'GP Ratio' FROM OINV T0 WHERE T0.[CardName] Like '%%buy%%' and T0.[DocDate] >= [%0] and T0.[DocDate] <=[%1] and T0.[CANCELED] = 'n'ORDER BY T0.[DocNum]

ERROR Message

Incorrect Syntax near ','

Incorrect Syntax near 'T0' and so on....

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Apr 12, 2017 at 03:22 PM

    Hi Shahzad,

    When you use the UNION instruction, you can not have Order by in each select, just in the last.

    Try this:

    SELECT
    	T0.[DocNum], 
    	T0.[DocDate], 
    	T0.[CardCode], 
    	T0.[CardName], 
    	T0.[Comments], 
    	T0.[DocTotal]*-1 as 'Doc Total', 
    	T0.[DiscSum]*-1 as 'Discount',
    	(((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum])*-1 as 'Base Price', 
    	((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*-1 as 'Net Value after discount', 
    	T0.[GrosProfit]*-1 as 'Gross Profit', 
    	Case when ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) = 0 then 0 else (T0.[GrosProfit]/((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*100*-1) end as 'GP Ratio' 
    FROM 
    	ORIN T0 
    WHERE 
    	T0.[CardName] Like '%%buy%%' 
    	and T0.[DocDate] >= [%0] 
    	and T0.[DocDate] <=[%1] 
    	and T0.[CANCELED] = 'N' 
    UNION ALL
    SELECT
    	T0.[DocNum], 
    	T0.[DocDate], 
    	T0.[CardCode], 
    	T0.[CardName], 
    	T0.[Comments], 
    	T0.[DocTotal] as 'Doc Total', 
    	T0.[DiscSum] as 'Discount',
    	(((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) as 'Base Price', ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) as 'Net Value after discount',
    	T0.[GrosProfit] as 'Gross Profit', 
    	Case when ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) = 0 then 0 else (T0.[GrosProfit]/((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*100) end as 'GP Ratio' 
    FROM 
    	OINV T0 
    WHERE 
    	T0.[CardName] Like '%%buy%%' 
    	and T0.[DocDate] >= [%0] 
    	and T0.[DocDate] <=[%1] 
    	and T0.[CANCELED] = 'N'
    ORDER BY T0.[DocNum]
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12, 2017 at 03:39 PM

    Thanks a Lot Dlego Lother. it worked. Appreciate your quick response.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12, 2017 at 04:12 PM

    Hi Dlego Lother

    I'm having same issue with below report. i tried doing everything. can you please review whats wrong with this query.

    T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[Address2], T0.[TrackNo], T0.[NumAtCard], T0.[Comments], T1.[StateS] T0.[DocTotal] as 'Doc Total', T0.[VatSum] as 'Tax', T0.[TotalExpns] as 'Other Expenses', T0.[DiscSum] as 'Discount', (((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) as 'Base Value', ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) as 'Net Value', T0.[GrosProfit] as 'Gross Profit', Case when ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) = 0 then 0 else (T0.[GrosProfit]/((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*100) end as 'GP Ratio' FROM OINV T0 INNER JOIN INV12 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] and T0.[CANCELED] = 'n' and T0.[CardCode] >=[%2] and T0.[CardCode] <=[%3]

    union all

    SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[Address2], T0.[TrackNo], T0.[NumAtCard], T0.[Comments], T1.[StateS] T0.[DocTotal]*-1 as 'Doc Total', T0.[VatSum]*-1 as 'Tax', T0.[TotalExpns]*-1 as 'Other Expenses', T0.[DiscSum]*-1 as 'Discount', (((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum])*-1 as 'Base Value', ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*-1 as 'Net Value', T0.[GrosProfit]*-1 as 'Gross Profit', Case when ((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum]) = 0 then 0 else (T0.[GrosProfit]/((((T0.[DocTotal]+T0.[DiscSum])-T0.[VatSum]-T0.[TotalExpns]-T0.[DiscSum])+T0.[DiscSum]) - T0.[DiscSum])*100) end as 'GP Ratio' FROM ORIN T0 INNER JOIN RIN12 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] and T0.[CANCELED] = 'n' and T0.[CardCode] >=[%2] and T0.[CardCode] <=[%3] ORDER BY T0.[DocDate]

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 13, 2017 at 03:26 PM

    Hello Diego

    I need to learn the pivot principle in SAP. i need reports that are based on pivoting data but i really don't know the principles as i am new to SAPB1. would love to learn this art. Can you please help me out?

    Thanks

    Shahzad Ahmed

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Shahzad,

      1. Two sub columns is not possible, but you can try some approaches like this shown on this topic:

      http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates

      But I will suggest a different approach on the end of this question.

      2. Yes, you can produce your own formula, for this I suggest to see the values on the child table for OINV and ORIN. The child tables are INV1 and RIN1. I suggest you to sum the values of the column LineTotal of the child table.

      LineTotal = (Line Price - Line Discount) * Line Quantity.

      You can see a sample with your two doubts below:

      SELECT
      	CardCode,
      	CardName,
      	col,
      	ISNULL([1], 0) as [Jan], 
      	ISNULL([2], 0) as [Feb], 
      	ISNULL([3], 0) as [Mar], 
      	ISNULL([4], 0) as [Apr], 
      	ISNULL([5], 0) as [May], 
      	ISNULL([6], 0) as [Jun], 
      	ISNULL([7], 0) as [Jul], 
      	ISNULL([8], 0) as [Aug], 
      	ISNULL([9], 0) as [Sep], 
      	ISNULL([10], 0) as [Oct], 
      	ISNULL([11], 0) as [Nov], 
      	ISNULL([12], 0) as [Dec] 
      FROM
      (
      	SELECT 
      		[CardCode], 
      		[CardName],
      		[MONTH],
      		col, 
      		value
      	FROM
      	(
      	SELECT 
      		T0.[CardCode], 
      		T0.[CardName], 
      		SUM(T1.LineTotal) AS NetTotal,
      		SUM(T1.GrssProfit) AS GrossProfit,
      		MONTH(T0.[DocDate]) AS [MONTH] 
      	FROM 
      		OINV T0 
      		INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
      	WHERE
      		YEAR(T0.DocDate) = 2017
      		AND T0.CANCELED = 'N'
      	GROUP BY
      		T0.[CardCode], T0.[CardName], MONTH(T0.[DocDate])
      	UNION ALL
      	SELECT 
      		T0.[CardCode], 
      		T0.[CardName], 
      		SUM(T1.LineTotal) * (-1) AS NetTotal,
      		SUM(T1.GrssProfit) * (-1) AS GrossProfit,
      		MONTH(T0.[DocDate]) AS [MONTH] 
      	FROM 
      		ORIN T0 
      		INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
      	WHERE
      		YEAR(T0.DocDate) = 2017
      		AND T0.CANCELED = 'N'
      	GROUP BY
      		T0.[CardCode], T0.[CardName], MONTH(T0.[DocDate])
      	) T0
      	UNPIVOT
      	(
      		value
      		FOR col IN ([NetTotal], [GrossProfit])
      	) unpiv
      ) s
      PIVOT
      (
        SUM(value)
        FOR MONTH IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
      ) piv
      ORDER BY CardCode

      Hope it helps.

      Kind Regards,

      Diego Lother