Retornar múltiples valores en una subquery

maik_spain · 28 Diciembre, 2018 10:56

Buenos días comunidad
Estoy creando una BF que debe realizar la siguiente función:

En un UDO (nombre de la tabla @VAH_NC) tengo definido un campo de cabecera, en el cual selecciono el código IC al que se refiere el documento (mediante una BF, nombre del campo U_VAH_NCIcCode).
Después a nivel de línea de documento, tengo definido un campo en el cual puedo seleccionar el tipo de documento de origen del IC al que quiero hacer referencia (oferta de compra/venta, pedido, albarán, factura, etc)
A continuación, he definido otro campo a nivel de línea de documento, en el cual, debo introducir mi BF y que debe permitirme seleccionar un Nº de documento de la clase indicada en la columna anterior, y que debe pertenecer al IC referenciado en el campo de Cabecera.

Os dejo una captura del UDO en cuestión:

Si alguno habéis visto las múltiples ediciones del post, veréis que he probado varias BF diferentes.

SELECT
CASE 
	WHEN $[$0_U_G.C_0_1.0] = 'Of_Compra'  THEN 
	(
		SELECT
		T1.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPQT] T1 ON T1.[CardCode] = T0.[U_VAH_NCIcCode] 
		WHERE T1.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)  
	WHEN $[$0_U_G.C_0_1.0] = 'Ped_Compra'  THEN 
	(
		SELECT
		T2.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPOR] T2 ON T2.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T2.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Alb_Compra'  THEN 
	(
		SELECT
		T3.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPDN] T3 ON T3.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T3.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Fac_Compra'  THEN 
	(
		SELECT
		T4.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPCH] T4 ON T4.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T4.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]	
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Dev_Compra'  THEN 
	(
		SELECT
		T5.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORPD] T5 ON T5.[CardCode] = T0.[U_VAH_NCIcCode] 
		WHERE T5.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Ab_Compra'  THEN 
	(
		SELECT
		T6.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORPC] T6 ON T6.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T6.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Of_Venta'  THEN 
	(
		SELECT
		T7.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OQUT] T7 ON T7.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T7.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Ped_Venta'  THEN 
	(
		SELECT
		T8.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORDR] T8 ON T8.[CardCode] = T0.[U_VAH_NCIcCode] 
		WHERE T8.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Alb_Venta'  THEN 
	(
		SELECT
		T9.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ODLN] T9 ON T9.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T9.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Fac_Venta'  THEN 
	(
		SELECT
		T10.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OINV] T10 ON T10.[CardCode] = T0.[U_VAH_NCIcCode] 
		WHERE T10.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Dev_Venta'  THEN 
	(
		SELECT
		T11.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORDN] T11 ON T11.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T11.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
	WHEN $[$0_U_G.C_0_1.0] = 'Ab_Venta'  THEN 
	(
		SELECT
		T12.[DocNum] 
		FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORIN] T12 ON T12.[CardCode] = T0.[U_VAH_NCIcCode]
		WHERE T12.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
	)
ELSE NULL END

Siempre me lanzan el mismo error:

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. '' (SWEI)

¿Como podría hacer que la SUBQUERY me permita visualizar un desplegable desde el cual elegir el documento, como en una BF Normal, o cómo podría reescribir esta BF para evitar usar una SubQuery?

Gracias por vuestra inestimable ayuda
Saludos y felices fiestas!

maik_spain · 30 Diciembre, 2018 21:44

Acabo de probar esta consulta:

DECLARE @t1 AS table ([Num] varchar(MAX))
DECLARE @t2 AS table ([Num] varchar(MAX))
DECLARE @t3 AS table ([Num] varchar(MAX))
DECLARE @t4 AS table ([Num] varchar(MAX))
DECLARE @t5 AS table ([Num] varchar(MAX))
DECLARE @t6 AS table ([Num] varchar(MAX))
DECLARE @t7 AS table ([Num] varchar(MAX))
DECLARE @t8 AS table ([Num] varchar(MAX))
DECLARE @t9 AS table ([Num] varchar(MAX))
DECLARE @t10 AS table ([Num] varchar(MAX))
DECLARE @t11 AS table ([Num] varchar(MAX))
DECLARE @t12 AS table ([Num] varchar(MAX))

INSERT INTO @t1
SELECT T1.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPQT] T1 ON T1.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T1.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t2
SELECT T2.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPOR] T2 ON T2.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T2.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t3
SELECT T3.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPDN] T3 ON T3.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T3.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t4
SELECT T4.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPCH] T4 ON T4.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T4.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 	
INSERT INTO @t5
SELECT T5.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORPD] T5 ON T5.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T5.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t6
SELECT T6.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORPC] T6 ON T6.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T6.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t7
SELECT T7.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OQUT] T7 ON T7.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T7.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t8
SELECT T8.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORDR] T8 ON T8.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T8.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t9
SELECT T9.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ODLN] T9 ON T9.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T9.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t10
SELECT T10.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OINV] T10 ON T10.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T10.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t11
SELECT T11.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORDN] T11 ON T11.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T11.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
INSERT INTO @t12
SELECT T12.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORIN] T12 ON T12.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T12.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]

SELECT CASE 
	WHEN $[$0_U_G.C_0_1.0] = 'Of_Compra'  THEN T13.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Ped_Compra'  THEN T14.[Num] 
	WHEN $[$0_U_G.C_0_1.0] = 'Alb_Compra'  THEN T15.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Fac_Compra'  THEN T16.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Dev_Compra'  THEN T17.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Ab_Compra'  THEN T18.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Of_Venta'  THEN T19.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Ped_Venta'  THEN T20.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Alb_Venta'  THEN T21.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Fac_Venta'  THEN T22.[Num] 
	WHEN $[$0_U_G.C_0_1.0] = 'Dev_Venta'  THEN T23.[Num]
	WHEN $[$0_U_G.C_0_1.0] = 'Ab_Venta'  THEN T24.[Num]

ELSE '0' END 

FROM 
@t1 AS T13 , 
@t2 AS T14 , 
@t3 AS T15 , 
@t4 AS T16 , 
@t5 AS T17 , 
@t6 AS T18 , 
@t7 AS T19 , 
@t8 AS T20 , 
@t9 AS T21 , 
@t10 AS T22 , 
@t11 AS T23 , 
@t12 AS T24

La BF se está ejecutando sin ningún error, y de hecho actualiza el valor del campo, aunque devuelve siempre el campo vacio.

La primera prueba la hice con NULL como acción en el ELSE, pero lo cambie a 0 para comprobar que las condiciones se cumplen correctamente (y efectivamente, no devuelve 0 al ejecutar la BF, simplemente deja vacio el campo).

¿Estoy avanzando en la dirección correcta?
¿Como hago para que me muestre la tabla y me permita seleccionar un valor de la misma?

Un saludo

maik_spain · 30 Diciembre, 2018 23:13

Finalmente conseguí sacar el código:


DECLARE @t1 AS table ([Num] nvarchar(MAX))
DECLARE @t2 AS table ([Num] nvarchar(MAX))
DECLARE @t3 AS table ([Num] nvarchar(MAX))
DECLARE @t4 AS table ([Num] nvarchar(MAX))
DECLARE @t5 AS table ([Num] nvarchar(MAX))
DECLARE @t6 AS table ([Num] nvarchar(MAX))
DECLARE @t7 AS table ([Num] nvarchar(MAX))
DECLARE @t8 AS table ([Num] nvarchar(MAX))
DECLARE @t9 AS table ([Num] nvarchar(MAX))
DECLARE @t10 AS table ([Num] nvarchar(MAX))
DECLARE @t11 AS table ([Num] nvarchar(MAX))
DECLARE @t12 AS table ([Num] nvarchar(MAX))

IF EXISTS
(SELECT 1 FROM [dbo].[OPQT] T1 WHERE T1.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t1
SELECT T1.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPQT] T1 ON T1.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T1.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[OPOR] T2 WHERE T2.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t2
SELECT T2.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPOR] T2 ON T2.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T2.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[OPDN] T3 WHERE T3.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t3
SELECT T3.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPDN] T3 ON T3.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T3.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[OPCH] T4 WHERE T4.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t4
SELECT T4.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OPCH] T4 ON T4.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T4.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 	
END

IF EXISTS
(SELECT 1 FROM [dbo].[ORPD] T5 WHERE T5.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t5
SELECT T5.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORPD] T5 ON T5.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T5.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[ORPC] T6 WHERE T6.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t6
SELECT T6.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORPC] T6 ON T6.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T6.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[OQUT] T7 WHERE T7.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t7
SELECT T7.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OQUT] T7 ON T7.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T7.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[ORDR] T8 WHERE T8.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t8
SELECT T8.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORDR] T8 ON T8.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T8.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[ODLN] T9 WHERE T9.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t9
SELECT T9.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ODLN] T9 ON T9.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T9.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[OINV] T10 WHERE T10.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t10
SELECT T10.[DocNum] AS Num
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[OINV] T10 ON T10.[CardCode] = T0.[U_VAH_NCIcCode] 
	WHERE T10.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[ORDN] T11 WHERE T11.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t11
SELECT T11.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORDN] T11 ON T11.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T11.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode] 
END

IF EXISTS
(SELECT 1 FROM [dbo].[ORIN] T12 WHERE T12.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode])
BEGIN 
INSERT INTO @t12
SELECT T12.[DocNum] AS Num 
	FROM [dbo].[@VAH_NC] T0 INNER JOIN [dbo].[ORIN] T12 ON T12.[CardCode] = T0.[U_VAH_NCIcCode]
	WHERE T12.[CardCode] = $[@VAH_NC.U_VAH_NCIcCode]
END

IF ($[$0_U_G.C_0_1.0] = 'Of_Compra')
BEGIN 
	SELECT T13.[Num] 
	FROM @t1 AS T13
END

IF ($[$0_U_G.C_0_1.0] = 'Ped_Compra')  
BEGIN 
	SELECT T14.[Num] 
	FROM @t2 AS T14 
END 

IF ($[$0_U_G.C_0_1.0] = 'Alb_Compra')
BEGIN 
	SELECT T15.[Num] 
	FROM @t3 AS T15 
END

IF ($[$0_U_G.C_0_1.0] = 'Fac_Compra')  
BEGIN 
	SELECT 16.[Num] 
	FROM @t4 AS T16
END
 
IF ($[$0_U_G.C_0_1.0] = 'Dev_Compra')  
BEGIN 
	SELECT T17.[Num] 
	FROM @t5 AS T17
END

IF ($[$0_U_G.C_0_1.0] = 'Ab_Compra') 
BEGIN
	SELECT T18.[Num]
	FROM @t6 AS T18 
END

IF ($[$0_U_G.C_0_1.0] = 'Of_Venta')  
BEGIN
	SELECT T19.[Num]
	FROM @t7 AS T19
END

IF ($[$0_U_G.C_0_1.0] = 'Ped_Venta')  
BEGIN
	SELECT T20.[Num]
	FROM @t8 AS T20 
END

IF ($[$0_U_G.C_0_1.0] = 'Alb_Venta')  
BEGIN
	SELECT T21.[Num]
	FROM @t9 AS T21 
END

IF ($[$0_U_G.C_0_1.0] = 'Fac_Venta')  
BEGIN
	SELECT T22.[Num]
	FROM @t10 AS T22
END

IF ($[$0_U_G.C_0_1.0] = 'Dev_Venta')  
BEGIN
	SELECT T23.[Num]
	FROM @t11 AS T23 
END

IF ($[$0_U_G.C_0_1.0] = 'Ab_Venta')  
BEGIN
	SELECT T24.[Num] 
	FROM @t12 AS T24
END

Espero que a alguien más le pueda servir de ayuda!!
Un saludo y felices fiestas