Ho un tavolo:
Account_Code | Desc
503100 | account xxx
503103 | account xxx
503104 | account xxx
503102A | account xxx
503110B | account xxx
Dove Account_Code
è un varchar
.
Quando creo una query di seguito:
Select
cast(account_code as numeric(20,0)) as account_code,
descr
from account
where isnumeric(account_code) = 1
Funziona bene restituendo tutti i record che hanno un valore numerico valido in account_code
colonna.
Ma quando provo ad aggiungere un'altra selezione, nidificata a sql precedente:
select account_code,descr
from
(
Select cast(account_code as numeric(20, 0)) as account_code,descr
from account
where isnumeric(account_code) = 1
) a
WHERE account_code between 503100 and 503105
la query restituirà un errore
Errore durante la conversione del tipo di dati varchar in numerico.
Cosa sta succedendo lì?
Ho già convertito in numerico se account_code
valido, ma sembra che la query stia ancora tentando di elaborare un record non valido.
Ho bisogno di usare la clausola BETWEEN
nella mia query.
SQL Server 2012 e versioni successive
Usa Try_Convert
anziché:
TRY_CONVERT accetta il valore passato ad esso e tenta di convertirlo nel tipo di dati specificato. Se il cast ha esito positivo, TRY_CONVERT restituisce il valore come tipo di dati specificato; se si verifica un errore, viene restituito null. Tuttavia, se richiedi una conversione esplicitamente non consentita, TRY_CONVERT non riesce con un errore.
lteriori informazioni su Try_Convert .
SQL Server 2008 e precedenti
Il modo tradizionale di gestirlo è tutelare ogni espressione con un'istruzione case in modo che, indipendentemente dalla valutazione, non creerà un errore, anche se logicamente sembra che l'istruzione CASE non dovrebbe essere necessaria. Qualcosa come questo:
SELECT
Account_Code =
Convert(
bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
CASE
WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
ELSE X.Account_Code
END
),
A.Descr
FROM dbo.Account A
WHERE
Convert(
bigint,
CASE
WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
ELSE X.Account_Code
END
) BETWEEN 503100 AND 503205
Tuttavia, mi piace usare strategie come questa con SQL Server 2005 e versioni successive:
SELECT
Account_Code = Convert(bigint, X.Account_Code),
A.Descr
FROM
dbo.Account A
OUTER APPLY (
SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
) X
WHERE
Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205
Quello che fa è cambiare strategicamente il Account_Code
valori a NULL
all'interno della tabella X
quando non sono numerici. Inizialmente ho usato CROSS APPLY
ma come Mikael Eriksson così sottolineato in modo appropriato, questo ha provocato lo stesso errore perché il parser di query ha riscontrato lo stesso identico problema di ottimizzare il mio tentativo di forzare l'ordine delle espressioni (il pushdown del predicato lo ha sconfitto) . Passando a OUTER APPLY
ha cambiato il significato effettivo dell'operazione in modo che X.Account_Code
could contiene NULL
valori all'interno della query esterna, richiedendo quindi un corretto ordine di valutazione.
Potresti essere interessato a leggere richiesta Microsoft Connect di Erland Sommarskog su questo problema relativo all'ordine di valutazione. In realtà lo chiama un bug.
Ci sono altri problemi qui, ma non posso risolverli ora.
Post scriptum Ho avuto un brainstorming oggi. Un'alternativa al "modo tradizionale" che ho suggerito è un'espressione SELECT
con un riferimento esterno, che funziona anche in SQL Server 2000. (Ho notato che dall'apprendimento CROSS/OUTER APPLY
Ho migliorato la mia capacità di query anche con le versioni precedenti di SQL Server - mentre sto diventando più versatile con le funzionalità di "riferimento esterno" di SELECT
, ON
e WHERE
clausole!)
SELECT
Account_Code =
Convert(
bigint,
(SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
),
A.Descr
FROM dbo.Account A
WHERE
Convert(
bigint,
(SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
) BETWEEN 503100 AND 503205
È molto più breve dell'istruzione CASE
.
Non vi è alcuna garanzia che SQL Server non tenterà di eseguire da CONVERT
a numeric(20,0)
prima esegue il filtro nella clausola WHERE
.
E, anche se lo facesse, ISNUMERIC
non è adeguato, poiché riconosce £
E 1d4
Come numerici, nessuno dei quali può essere convertito in numeric(20,0)
. (*)
Dividilo in due query separate, la prima delle quali filtra i risultati e li inserisce in una tabella temporanea o variabile di tabella, la seconda delle quali esegue la conversione. (Le subquery e le CTE sono inadeguate per impedire all'ottimizzatore di tentare la conversione prima del filtro)
Per il tuo filtro, probabilmente usa account_code not like '%[^0-9]%'
Invece di ISNUMERIC
.
(*) ISNUMERIC
risponde alla domanda che nessuno (per quanto ne sappia) ha mai voluto porre - "questa stringa può essere convertita in any dei tipi di dati numerici - Non mi importa quale? " - quando ovviamente, ciò che molte persone vogliono chiedere è "questa stringa può essere convertita in x?" dove x
è un tipo di dati specifico target.
Se si esegue SQL Server 2012 o versioni successive, è anche possibile utilizzare la nuova funzione TRY_PARSE () :
Restituisce il risultato di un'espressione, tradotto nel tipo di dati richiesto o null se il cast non riesce in SQL Server. Utilizzare TRY_PARSE solo per la conversione da stringa a data/ora e tipi di numero.
Oppure TRY_CONVERT / TRY_CAST :
Restituisce un cast di valori nel tipo di dati specificato se il cast ha esito positivo; in caso contrario, restituisce null.
grazie, prova questo invece
Select
STR(account_code) as account_code_Numeric,
descr
from account
where STR(account_code) = 1
Sono felice di aiutarti
Penso che il problema non sia nella query secondaria ma nella clausola WHERE della query esterna. Quando si usa
WHERE account_code between 503100 and 503105
Il server SQL proverà a convertire tutti i valori nel campo Account_code in numero intero per testarlo nelle condizioni fornite. Ovviamente non ci riuscirà se ci saranno caratteri non interi in alcune righe.