cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Can get not use select in with a query that uses except

Go to solution
jbrotto
Active Contributor

on ‎03-02-2022 8:42 PM

0 Kudos

I find it odd that I can not use the in clause as the inner query returns the excluded values when ran on its own.

When I run the full query I get this error

Browse mode is invalid for statements containing union, intersect, except operator.

SQL 2019 SAP 10

SELECT T9.[LineTotal]
FROM OINV T8 INNER JOIN INV1 T9 ON T8.DocEntry = T9.DocEntry 
WHERE T8.[DocNum] IN
(
SELECT Inside.[DocNum]
FROM
(
SELECT T1.[DocNum], 
CASE
WHEN T3.[ZipCodeS] IS NOT NULL AND RIGHT(T3.[ZipCodeS],1) < 'A' AND T3.[CountryS] = 'CA' 
AND LEN(T3.[ZipCodeS]) <= '7' AND LEN(T3.[ZipCodeS]) >= '6' AND LEFT(T3.[ZipCodeS],1) != 'O'
AND T3.[ZipCodeS] >= 'A' THEN T3.[ZipCodeS]
ELSE 
	CASE
		WHEN LEN(T1.[Address2]) > 7 AND LEFT(TRIM(RIGHT(T1.[Address2],7)),1) >= 'A' AND LEFT(TRIM(RIGHT(T1.[Address2],7)),1) != 'O'
AND (SUBSTRING(TRIM(RIGHT(T1.[Address2],7)),4,1) = ' ' OR SUBSTRING(TRIM(RIGHT(T1.[Address2],7)),4,1) = '-')
AND LEFT(TRIM(RIGHT(T1.[Address2],7)),2) != 'RM'
AND RIGHT(T1.[Address2],1) < 'A' THEN TRIM(RIGHT(T1.[Address2],7))
		
WHEN (T2.[MailZipCod] IS NOT NULL OR T2.[MailZipCod] NOT LIKE '' OR T2.[MailZipCod] != '') 
		AND T2.MailCountr = 'CA' AND RIGHT(T2.[MailZipCod],1) < 'A' THEN T2.[MailZipCod]
		ELSE 'Undefined'
	END
END AS 'Province'

FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
INNER JOIN INV12 T3 ON T3.DocEntry = T1.DocEntry
WHERE T1.[DocDate] >= '[%0]' AND T1.[DocDate] <= '[%1]'
GROUP BY T1.[DocNum], T3.[ZipCodeS], T2.[MailZipCod], T2.MailCountr, T3.[CountryS],T1.[Address2], TRIM(RIGHT(T1.[Address2],7))


) Inside

EXCEPT  --Union

SELECT Inside.[DocNum]
FROM
(
SELECT T1.[DocNum], 
CASE
WHEN T3.[ZipCodeS] IS NOT NULL AND RIGHT(T3.[ZipCodeS],1) < 'A' AND T3.[CountryS] = 'CA' 
AND LEN(T3.[ZipCodeS]) <= '7' AND LEN(T3.[ZipCodeS]) >= '6' AND LEFT(T3.[ZipCodeS],1) != 'O'
AND T3.[ZipCodeS] >= 'A' THEN T3.[ZipCodeS]
WHEN T4.[ZipCode] IS NOT NULL AND RIGHT(T4.[ZipCode],1) < 'A' AND T4.[Country] = 'CA' 
AND LEN(T4.[ZipCode]) <= '7' AND LEN(T4.[ZipCode]) >= '6' AND LEFT(T4.[ZipCode],1) != 'O'
AND T4.[ZipCode] >= 'A' THEN T4.[ZipCode]
ELSE 
	CASE
		WHEN LEN(T1.[Address2]) > 7 AND LEFT(TRIM(RIGHT(T1.[Address2],7)),1) >= 'A' AND LEFT(TRIM(RIGHT(T1.[Address2],7)),1) != 'O'
AND (SUBSTRING(TRIM(RIGHT(T1.[Address2],7)),4,1) = ' ' OR SUBSTRING(TRIM(RIGHT(T1.[Address2],7)),4,1) = '-')
AND LEFT(TRIM(RIGHT(T1.[Address2],7)),2) != 'RM'
AND RIGHT(T1.[Address2],1) < 'A' THEN TRIM(RIGHT(T1.[Address2],7))
		
WHEN (T2.[MailZipCod] IS NOT NULL OR T2.[MailZipCod] NOT LIKE '' OR T2.[MailZipCod] != '') 
		AND T2.MailCountr = 'CA' AND RIGHT(T2.[MailZipCod],1) < 'A' THEN T2.[MailZipCod]
		ELSE 'Undefined'
	END
END AS 'Province'

FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
INNER JOIN INV12 T3 ON T3.DocEntry = T1.DocEntry
INNER JOIN CRD1 T4 ON T4.CardCode = T2.CardCode
WHERE T1.[DocDate] >= '[%0]' AND T1.[DocDate] <= '[%1]'
AND T4.[AdresType] = 'S' AND T4.[Address] = T1.[ShipToCode] 
GROUP BY T1.[DocNum],
T4.[Country], T4.[ZipCode], T3.[ZipCodeS], T2.[MailZipCod], T2.MailCountr, T3.[CountryS],T1.[Address2], TRIM(RIGHT(T1.[Address2],7))

) Inside
)
Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

former_member499887
Explorer
‎03-03-2022 1:35 AM
0 Kudos

Hello Jonathan, try

declare @di as datetime, @df as datetime
set @di = /* SELECT Max(T0.DocDate) FROM OINV T0  */ '[%0]'
set @df = /* SELECT Max(T0.DocDate) FROM OINV T0  */ '[%1]'

SELECT T9.[LineTotal]
FROM OINV T8
INNER JOIN INV1 T9 ON T8.DocEntry = T9.DocEntry
WHERE T8.[DocNum] IN (
		SELECT Inside.[DocNum]
		FROM (
			SELECT T1.[DocNum]
				,CASE 
					WHEN T3.[ZipCodeS] IS NOT NULL
						AND RIGHT(T3.[ZipCodeS], 1) < 'A'
						AND T3.[CountryS] = 'CA'
						AND LEN(T3.[ZipCodeS]) <= '7'
						AND LEN(T3.[ZipCodeS]) >= '6'
						AND LEFT(T3.[ZipCodeS], 1) != 'O'
						AND T3.[ZipCodeS] >= 'A'
						THEN T3.[ZipCodeS]
					ELSE CASE 
							WHEN LEN(T1.[Address2]) > 7
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) >= 'A'
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) != 'O'
								AND (
									SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = ' '
									OR SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = '-'
									)
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 2) != 'RM'
								AND RIGHT(T1.[Address2], 1) < 'A'
								THEN TRIM(RIGHT(T1.[Address2], 7))
							WHEN (
									T2.[MailZipCod] IS NOT NULL
									OR T2.[MailZipCod] NOT LIKE ''
									OR T2.[MailZipCod] != ''
									)
								AND T2.MailCountr = 'CA'
								AND RIGHT(T2.[MailZipCod], 1) < 'A'
								THEN T2.[MailZipCod]
							ELSE 'Undefined'
							END
					END AS 'Province'
			FROM INV1 T0
			INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
			INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
			INNER JOIN INV12 T3 ON T3.DocEntry = T1.DocEntry
			WHERE T1.[DocDate] >= @di
				AND T1.[DocDate] <= @df
			GROUP BY T1.[DocNum]
				,T3.[ZipCodeS]
				,T2.[MailZipCod]
				,T2.MailCountr
				,T3.[CountryS]
				,T1.[Address2]
				,TRIM(RIGHT(T1.[Address2], 7))
			
			UNION ALL
			
			SELECT T1.[DocNum]
				,CASE 
					WHEN T3.[ZipCodeS] IS NOT NULL
						AND RIGHT(T3.[ZipCodeS], 1) < 'A'
						AND T3.[CountryS] = 'CA'
						AND LEN(T3.[ZipCodeS]) <= '7'
						AND LEN(T3.[ZipCodeS]) >= '6'
						AND LEFT(T3.[ZipCodeS], 1) != 'O'
						AND T3.[ZipCodeS] >= 'A'
						THEN T3.[ZipCodeS]
					ELSE CASE 
							WHEN LEN(T1.[Address2]) > 7
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) >= 'A'
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) != 'O'
								AND (
									SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = ' '
									OR SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = '-'
									)
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 2) != 'RM'
								AND RIGHT(T1.[Address2], 1) < 'A'
								THEN TRIM(RIGHT(T1.[Address2], 7))
							WHEN (
									T2.[MailZipCod] IS NOT NULL
									OR T2.[MailZipCod] NOT LIKE ''
									OR T2.[MailZipCod] != ''
									)
								AND T2.MailCountr = 'CA'
								AND RIGHT(T2.[MailZipCod], 1) < 'A'
								THEN T2.[MailZipCod]
							ELSE 'Undefined'
							END
					END AS 'Province'
			FROM RIN1 T0
			INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry
			INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
			INNER JOIN RIN12 T3 ON T3.DocEntry = T1.DocEntry
			WHERE T1.[DocDate] >= @di
				AND T1.[DocDate] <= @df
			GROUP BY T1.[DocNum]
				,T3.[ZipCodeS]
				,T2.[MailZipCod]
				,T2.MailCountr
				,T3.[CountryS]
				,T1.[Address2]
				,TRIM(RIGHT(T1.[Address2], 7))
			) Inside
		
		EXCEPT --Union
		
		SELECT Inside.[DocNum]
		FROM (
			SELECT T1.[DocNum]
				,CASE 
					WHEN T3.[ZipCodeS] IS NOT NULL
						AND RIGHT(T3.[ZipCodeS], 1) < 'A'
						AND T3.[CountryS] = 'CA'
						AND LEN(T3.[ZipCodeS]) <= '7'
						AND LEN(T3.[ZipCodeS]) >= '6'
						AND LEFT(T3.[ZipCodeS], 1) != 'O'
						AND T3.[ZipCodeS] >= 'A'
						THEN T3.[ZipCodeS]
					WHEN T4.[ZipCode] IS NOT NULL
						AND RIGHT(T4.[ZipCode], 1) < 'A'
						AND T4.[Country] = 'CA'
						AND LEN(T4.[ZipCode]) <= '7'
						AND LEN(T4.[ZipCode]) >= '6'
						AND LEFT(T4.[ZipCode], 1) != 'O'
						AND T4.[ZipCode] >= 'A'
						THEN T4.[ZipCode]
					ELSE CASE 
							WHEN LEN(T1.[Address2]) > 7
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) >= 'A'
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) != 'O'
								AND (
									SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = ' '
									OR SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = '-'
									)
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 2) != 'RM'
								AND RIGHT(T1.[Address2], 1) < 'A'
								THEN TRIM(RIGHT(T1.[Address2], 7))
							WHEN (
									T2.[MailZipCod] IS NOT NULL
									OR T2.[MailZipCod] NOT LIKE ''
									OR T2.[MailZipCod] != ''
									)
								AND T2.MailCountr = 'CA'
								AND RIGHT(T2.[MailZipCod], 1) < 'A'
								THEN T2.[MailZipCod]
							ELSE 'Undefined'
							END
					END AS 'Province'
			FROM INV1 T0
			INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
			INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
			INNER JOIN INV12 T3 ON T3.DocEntry = T1.DocEntry
			INNER JOIN CRD1 T4 ON T4.CardCode = T2.CardCode
			WHERE T1.[DocDate] >= @di
				AND T1.[DocDate] <= @df
				AND T4.[AdresType] = 'S'
				AND T4.[Address] = T1.[ShipToCode]
			GROUP BY T1.[DocNum]
				,T4.[Country]
				,T4.[ZipCode]
				,T3.[ZipCodeS]
				,T2.[MailZipCod]
				,T2.MailCountr
				,T3.[CountryS]
				,T1.[Address2]
				,TRIM(RIGHT(T1.[Address2], 7))
			
			UNION ALL
			
			SELECT T1.[DocNum]
				,CASE 
					WHEN T3.[ZipCodeS] IS NOT NULL
						AND RIGHT(T3.[ZipCodeS], 1) < 'A'
						AND T3.[CountryS] = 'CA'
						AND LEN(T3.[ZipCodeS]) <= '7'
						AND LEN(T3.[ZipCodeS]) >= '6'
						AND LEFT(T3.[ZipCodeS], 1) != 'O'
						AND T3.[ZipCodeS] >= 'A'
						THEN T3.[ZipCodeS]
					WHEN T4.[ZipCode] IS NOT NULL
						AND RIGHT(T4.[ZipCode], 1) < 'A'
						AND T4.[Country] = 'CA'
						AND LEN(T4.[ZipCode]) <= '7'
						AND LEN(T4.[ZipCode]) >= '6'
						AND LEFT(T4.[ZipCode], 1) != 'O'
						AND T4.[ZipCode] >= 'A'
						THEN T4.[ZipCode]
					ELSE CASE 
							WHEN LEN(T1.[Address2]) > 7
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) >= 'A'
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 1) != 'O'
								AND (
									SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = ' '
									OR SUBSTRING(TRIM(RIGHT(T1.[Address2], 7)), 4, 1) = '-'
									)
								AND LEFT(TRIM(RIGHT(T1.[Address2], 7)), 2) != 'RM'
								AND RIGHT(T1.[Address2], 1) < 'A'
								THEN TRIM(RIGHT(T1.[Address2], 7))
							WHEN (
									T2.[MailZipCod] IS NOT NULL
									OR T2.[MailZipCod] NOT LIKE ''
									OR T2.[MailZipCod] != ''
									)
								AND T2.MailCountr = 'CA'
								AND RIGHT(T2.[MailZipCod], 1) < 'A'
								THEN T2.[MailZipCod]
							ELSE 'Undefined'
							END
					END AS 'Province'
			FROM RIN1 T0
			INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry
			INNER JOIN OCRD T2 ON T2.CardCode = T1.CardCode
			INNER JOIN RIN12 T3 ON T3.DocEntry = T1.DocEntry
			INNER JOIN CRD1 T4 ON T4.CardCode = T2.CardCode
			WHERE T1.[DocDate] >= @di
				AND T1.[DocDate] <= @df
				AND T4.[AdresType] = 'S'
				AND T4.[Address] = T1.[ShipToCode]
			GROUP BY T1.[DocNum]
				,T4.[Country]
				,T4.[ZipCode]
				,T3.[ZipCodeS]
				,T2.[MailZipCod]
				,T2.MailCountr
				,T3.[CountryS]
				,T1.[Address2]
				,TRIM(RIGHT(T1.[Address2], 7))
			) Inside
		)
Show replies
Show replies
jbrotto
Active Contributor
‎03-04-2022 3:51 PM
0 Kudos

It works as I got the amounts but as a project I'll need the postal code would rewriting to use exist or a sub query within the from section would be the best appraoch?

Answers (0)

Ask a Question