Help with Union All function

[shahmed]

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

[former_member185682 · Accepted solution]

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

[shahmed]

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

[shahmed]

For example i want to prepare this simple query and then i ll move forward for complex ones. below mentioned query is giving error.

P.[CardCode], P.[CardName], P.[DocTotal], [1] as [Jan], [2] as [Feb], [3] as [Mar], [4] as [Apr], [5] as [May], [6] as [Jun], [7] as [Jul], [8] as [Aug], [9] as [Sep], [10] as [Oct], [11] as [Nov], [12] as [Dec] FROM (SELECT T0.[CardCode], T0.[CardName], T0.[DocTotal], MONTH(T0.DocDate) AS [MONTH] fROM oINV T0 AND YEAR(T0.DocDate)=2017) S PIVOT (SUM(DocTotal) FOR [month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P ORDER BY P.[CardCode]

[former_member185682]

Hi Shahzad,

I'm don't have much experience with PIVOT, just with simple cases like you shown above.

The correct way for your query is this:

SELECT
	P.[CardCode], 
	P.[CardName], 
	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 
		T0.[CardCode], 
		T0.[CardName], 
		T0.[DocTotal], 
		MONTH(T0.[DocDate]) AS [MONTH] 
	FROM 
		OINV T0 
	WHERE
		YEAR(T0.DocDate) = 2017) P PIVOT (SUM(DocTotal) FOR [MONTH] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P ORDER BY P.[CardCode]

About to learn how pivot function works, I think that this article can helps you:

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Hope it helps.

Kind Regards,

Diego Lother

[shahmed]

and to pivot ORIN and OINV both together, we need to union them all? with same conditions?

[former_member185682]

Hi Shahzad,

You can simply add the new query with ORIN with the query in the OINV like this:

SELECT
	P.[CardCode], 
	P.[CardName], 
	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 
		T0.[CardCode], 
		T0.[CardName], 
		T0.[DocTotal], 
		MONTH(T0.[DocDate]) AS [MONTH] 
	FROM 
		OINV T0 
	WHERE
		YEAR(T0.DocDate) = 2017
	UNION ALL
	SELECT 
		T0.[CardCode], 
		T0.[CardName], 
		T0.[DocTotal] * (-1), 
		MONTH(T0.[DocDate]) AS [MONTH] 
	FROM 
		ORIN T0 
	WHERE
		YEAR(T0.DocDate) = 2017) P PIVOT (SUM(DocTotal) FOR [MONTH] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P ORDER BY P.[CardCode]

In the sample above I multiplied the field DocTotal of ORIN by (-1) because I believe that you want decrease the values from ORIN. If I'm wrong, just remove the "* (-1)" of the query above.

Hope it helps.

Kind Regards,

Diego Lother

[shahmed]

Absolutely right. Can this Month column have two sub columns? for example, if i wana see sale and gross profit together.?

secondly, can you we use our own formula to calculate Net value instead of Doctotal?

Thanks

[former_member185682]

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

[shahmed]

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

[former_member185682]

Hi Shahzad,

You forgot the comma after the field "T1.[StateS]" in the two queries, and the parameters for cardcode should be between single quote.

Try this:

SELECT
	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]

Hope it helps.

Kind Regards,

Diego Lother

[shahmed]

Oh my bad. Thanks dear. so nice of you.

[shahmed]

I have one more question that why do we put single comma on Cardcode? while running another query i didn't use this single comma but query was ok. just wana know the logic behind this.

[former_member185682]

Hi,

Because the field CardCode is a nvarchar field, then when you want compare something with a nvarchar field it is necessary to use single quotes.

Your query can works without single quotes if all of your cardcodes does not contains letters just numbers.

For example:

Suppose you have two business partner on your database with the cardcode equals 00001 and 00002, and you write your query like this:

SELECT * FROM OCRD WHERE CardCode = [%0]

In this case the query will works fine.

But suppose that your two cardcode are C0001 and C0002, in this case you will receive an error in the sql above.

The the correct way is to use single quotes.

SELECT * FROM OCRD WHERE CardCode = '[%0]'

Hope it clears.

Kind Regards,

Diego Lother

[shahmed]

Thanks dear. helped a lot.

[shahmed]

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