Hi,
I have the following query
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE CONSTELEFONICA
@E1 VARCHAR(50),
@TIPOS VARCHAR(50),
@PERINI DATETIME,
@PERFIM DATETIME,
@PERINI2 DATETIME,
@PERFIM2 DATETIME
AS
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @PERINI as DATA_INICIAL, @PERFIM as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @E1
AND DATA_HORA BETWEEN @PERINI AND @PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA IN (@TIPOS)
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @PERINI2 as DATA_INICIAL, @PERFIM2 as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @E1
AND DATA_HORA BETWEEN @PERINI2 AND @PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA NOT IN (@TIPOS)
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
And from my application I call the query like...
EXEC CONSTELEFONICA '01133722000', 'CONUR, LOCAL', '20060205','20060304 23:59:59.997', '20060203','20060302 23:59:59.997'
But it doesn't work because the CLASSIFICA_TELEFONICA NOT IN (@TIPOS) does not recognise as CLASSIFICA_TELEFONICA NOT IN ('CONUR', 'LOCAL') how can I make this query
Thanks
Query problem - TIPOS IN ()
portojbc
orcrist4
You can't do it like that, read Arrays and Lists in SQL Server by SQL Server MVP Erland Sommarskog (http://www.sommarskog.se/arrays-in-sql.html)
Denis the SQL Menace
http://sqlservercode.blogspot.com/Shariq77
I tried doing this
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @PERINI as DATA_INICIAL, @PERFIM as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@TIPOS) I ON T.CLASSIFICA_TELEFONICA = I.str WHERE NUMERO_E1 = @E1
AND DATA_HORA BETWEEN @PERINI AND @PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @PERINI2 as DATA_INICIAL, @PERFIM2 as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@TIPOS) I ON T.CLASSIFICA_TELEFONICA <> I.str WHERE NUMERO_E1 = @E1
AND DATA_HORA BETWEEN @PERINI2 AND @PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA
But I got the message that CHARLIST_TO_TABLE_SP is not a valid function...but in the reference site it's a procedure...and I've created it
Brad Hodges
I understood but I don't know how to apply in my query can you help me
Thanks