Error converting data type varchar to numeric.

Excuse me for my english.
Let us start with the base. I work with SQLServer 2000. My table has 12
columns of the type varchar Ex.:(Condition1_Min, Condition1_Max,
Condition2_Min, Condition2_Max etc...)
There are values which must be interpreted like the numerical one and of
other as alpha. Here a request which does not function in Query Analyser
It seems that if I cast my condition1_min, for example, as soon as I have a
request which still uses condition1_min but not cast then sql server this
error message gives me.
Here an example of request:
SELECT
NO_MACHINE,NOM_PROCEDURE,CONDITION1_MIN,
CONDITION1_MAX,CONDITION1_TOUS,CONDI
TION2_MIN,CONDITION2_MAX,CONDITION2_TOUS
,CONDITION3_MIN,CONDITION3_MAX,CONDI
TION3_TOUS,CONDITION4_MIN,CONDITION4_MAX
,CONDITION4_TOUS,CONDITION5_MIN,COND
ITION5_MAX,CONDITION5_TOUS,
CONDITION6_MIN,CONDITION6_MAX,CONDITION6
_TOUS,NO_GRP_INSTRUCTIONS,RAZ_BORNE
FROM MES_PROCEDURECONDITIONS
WHERE (NO_MACHINE = '00300') AND (CONDITION1_MIN >= 'AK' AND CONDITION1_MAX
<= 'AK') AND (CONDITION2_MIN >= '4' AND CONDITION2_MAX <= '4') AND
NOM_PROCEDURE = 'ChangRouleaux' OR (NO_MACHINE = '00300') AND
(CONDITION1_MIN >= '2' AND CONDITION1_MAX <= '2') AND (CONDITION2_MIN >= '4
'
AND CONDITION2_MAX <= '4') AND NOM_PROCEDURE = 'Insp. Feuille Début' OR
(NO_MACHINE = '00300') AND (CONDITION1_MIN >= 'False' AND CONDITION1_MAX <=
'False') AND (cast(CONDITION2_MIN as decimal) >= 0.065 AND
cast(CONDITION2_MAX as decimal) <= 0.065) AND (CONDITION3_MIN >= '4' AND
CONDITION3_MAX <= '4') AND (CONDITION4_MIN >= '2' AND CONDITION4_MAX <= '2'
)
AND NOM_PROCEDURE = 'Inspection Final' OR (NO_MACHINE = '00300') AND
(CONDITION1_MIN >= 'LR' AND CONDITION1_MAX <= 'LR') AND (CONDITION2_MIN >=
'2' AND CONDITION2_MAX <= '2') AND (CONDITION3_MIN >= '4' AND CONDITION3_MA
X
<= '4') AND (CONDITION4_MIN >= '0' AND CONDITION4_MAX <= '0') AND
NOM_PROCEDURE = 'Largeur 1 re bob équ' OR (NO_MACHINE = '00300') AND
(CONDITION1_MIN >= 'False' AND CONDITION1_MAX <= 'False') AND
(CONDITION2_MIN >= 'LR' AND CONDITION2_MAX <= 'LR') AND (CONDITION3_MIN >=
'0' AND CONDITION3_MAX <= '0') AND (CONDITION4_MIN >= '4' AND CONDITION4_MA
X
<= '4') AND NOM_PROCEDURE = 'Largeur Final 1er bob' OR (NO_MACHINE =
'00300') AND (CONDITION1_MIN >= 'AK' AND CONDITION1_MAX <= 'AK') AND
(CONDITION2_MIN >= 'LR' AND CONDITION2_MAX <= 'LR') AND (CONDITION3_MIN >=
'0' AND CONDITION3_MAX <= '0') AND (CONDITION4_MIN >= '4' AND CONDITION4_MA
X
<= '4') AND NOM_PROCEDURE = 'Largeur Foil 1re bobi.' OR (NO_MACHINE =
'00300') AND (CONDITION1_MIN >= 'LR' AND CONDITION1_MAX <= 'LR') AND
(CONDITION2_MIN >= '4' AND CONDITION2_MAX <= '4') AND NOM_PROCEDURE =
'Largeur_PI' OR (NO_MACHINE = '00300') AND (CONDITION1_MIN >= '145418001'
AND CONDITION1_MAX <= '145418001') AND (CONDITION2_MIN >= '2' AND
CONDITION2_MAX <= '2') AND (CONDITION3_MIN >= '875' AND CONDITION3_MAX <=
'875') AND (CONDITION4_MIN >= '4' AND CONDITION4_MAX <= '4') AND
NOM_PROCEDURE = 'Lavage' OR (NO_MACHINE = '00300') AND (CONDITION1_MIN <=
'False' AND CONDITION1_MAX >= 'False') AND (cast(CONDITION2_MIN as decimal)
>= 0.065 AND cast(CONDITION2_MAX as decimal(38,10)) <= 0.065) AND
(CONDITION3_MIN >= '4' AND CONDITION3_MAX <= '4') AND NOM_PROCEDURE =
'Standardi. Final 1er b' OR (NO_MACHINE = '00300') AND (cast(CONDITION1_MI
N
as decimal(38,10)) >= 0.065 AND cast(CONDITION1_MAX as decimal) <= 0.065) AN
D
(CONDITION2_MIN >= '2' AND CONDITION2_MAX <= '2') AND (CONDITION3_MIN >=
'4' AND CONDITION3_MAX <= '4') AND NOM_PROCEDURE = 'Standardisation Déb.'
ORDER BY NO_MACHINE,NOM_PROCEDURE
Under condition min and max I can have alphas and num data. The request is
made with generic program. I need your ideas.
Thank you for your assistance!!!Hi
Posting DDL and example data as described in
http://www.aspfaq.com/etiquett___e.asp?id=5006 helps when answering quest
ions
like this.
CONDITION2_MIN >= '4' AND CONDITION2_MAX <= '4'
is not the same as
CONDITION2_MIN >= 4 AND CONDITION2_MAX <= 4
e.g.
SELECT * FROM
( SELECT '10' as [Min], '20' AS [MAX]
UNION ALL SELECT '4', '8' ) A
WHERE [Min] >= '4'
SELECT * FROM
( SELECT '10' as [Min], '20' AS [MAX]
UNION ALL SELECT '4', '8' ) A
WHERE [Min] >= 4
And it would seem more logical if the condition was
CONDITION2_MIN <= 4 AND CONDITION2_MAX >= 4
If you are having conversion errors using PATINDEX or ISNUMERIC may help.
John
"Ric" wrote:

> Excuse me for my english.
> Let us start with the base. I work with SQLServer 2000. My table has 12
> columns of the type varchar Ex.:(Condition1_Min, Condition1_Max,
> Condition2_Min, Condition2_Max etc...)
> There are values which must be interpreted like the numerical one and of
> other as alpha. Here a request which does not function in Query Analyser
> It seems that if I cast my condition1_min, for example, as soon as I have
a
> request which still uses condition1_min but not cast then sql server this
> error message gives me.
> Here an example of request:
> SELECT
> NO_MACHINE,NOM_PROCEDURE,CONDITION1_MIN,
CONDITION1_MAX,CONDITION1_TOUS,CONDITION
2_
MIN,CONDITION2_MAX,CONDITION2_TOUS,CONDI
TION3_MIN,CONDITION3_MAX,CONDITION3_TOUS
,CON
DITION4_MIN,CONDITION4_MAX,CONDITION4_TO
US,CONDITION5_MIN,CONDITION5_MAX,CONDITI
ON5_
TOU
S,CONDITION6_MIN,CONDITION6_MAX,CONDITIO
N6_TOUS,NO_GRP_INSTRUCTIONS,RAZ_BORNE[co
lor=darkred
]
> FROM MES_PROCEDURECONDITIONS
> WHERE (NO_MACHINE = '00300') AND (CONDITION1_MIN >= 'AK' AND CONDITION1_MA
X
> <= 'AK') AND (CONDITION2_MIN >= '4' AND CONDITION2_MAX <= '4') AND
> NOM_PROCEDURE = 'ChangRouleaux' OR (NO_MACHINE = '00300') AND
> (CONDITION1_MIN >= '2' AND CONDITION1_MAX <= '2') AND (CONDITION2_MIN >=
'4'
> AND CONDITION2_MAX <= '4') AND NOM_PROCEDURE = 'Insp. Feuille Début' OR
> (NO_MACHINE = '00300') AND (CONDITION1_MIN >= 'False' AND CONDITION1_MAX
<=
> 'False') AND (cast(CONDITION2_MIN as decimal) >= 0.065 AND
> cast(CONDITION2_MAX as decimal) <= 0.065) AND (CONDITION3_MIN >= '4' AND
> CONDITION3_MAX <= '4') AND (CONDITION4_MIN >= '2' AND CONDITION4_MAX <= '
2')
> AND NOM_PROCEDURE = 'Inspection Final' OR (NO_MACHINE = '00300') AND
> (CONDITION1_MIN >= 'LR' AND CONDITION1_MAX <= 'LR') AND (CONDITION2_MIN >
=
> '2' AND CONDITION2_MAX <= '2') AND (CONDITION3_MIN >= '4' AND CONDITION3_
MAX
> <= '4') AND (CONDITION4_MIN >= '0' AND CONDITION4_MAX <= '0') AND
> NOM_PROCEDURE = 'Largeur 1 re bob équ' OR (NO_MACHINE = '00300') AND
> (CONDITION1_MIN >= 'False' AND CONDITION1_MAX <= 'False') AND
> (CONDITION2_MIN >= 'LR' AND CONDITION2_MAX <= 'LR') AND (CONDITION3_MIN >
=
> '0' AND CONDITION3_MAX <= '0') AND (CONDITION4_MIN >= '4' AND CONDITION4_
MAX
> <= '4') AND NOM_PROCEDURE = 'Largeur Final 1er bob' OR (NO_MACHINE =
> '00300') AND (CONDITION1_MIN >= 'AK' AND CONDITION1_MAX <= 'AK') AND
> (CONDITION2_MIN >= 'LR' AND CONDITION2_MAX <= 'LR') AND (CONDITION3_MIN >
=
> '0' AND CONDITION3_MAX <= '0') AND (CONDITION4_MIN >= '4' AND CONDITION4_
MAX
> <= '4') AND NOM_PROCEDURE = 'Largeur Foil 1re bobi.' OR (NO_MACHINE =
> '00300') AND (CONDITION1_MIN >= 'LR' AND CONDITION1_MAX <= 'LR') AND
> (CONDITION2_MIN >= '4' AND CONDITION2_MAX <= '4') AND NOM_PROCEDURE =
> 'Largeur_PI' OR (NO_MACHINE = '00300') AND (CONDITION1_MIN >= '145418001
'
> AND CONDITION1_MAX <= '145418001') AND (CONDITION2_MIN >= '2' AND
> CONDITION2_MAX <= '2') AND (CONDITION3_MIN >= '875' AND CONDITION3_MAX <=
> '875') AND (CONDITION4_MIN >= '4' AND CONDITION4_MAX <= '4') AND
> NOM_PROCEDURE = 'Lavage' OR (NO_MACHINE = '00300') AND (CONDITION1_MIN <
=
> 'False' AND CONDITION1_MAX >= 'False') AND (cast(CONDITION2_MIN as decima
l)
> (CONDITION3_MIN >= '4' AND CONDITION3_MAX <= '4') AND NOM_PROCEDURE =
> 'Standardi. Final 1er b' OR (NO_MACHINE = '00300') AND (cast(CONDITION1_
MIN
> as decimal(38,10)) >= 0.065 AND cast(CONDITION1_MAX as decimal) <= 0.065)
AND
> (CONDITION2_MIN >= '2' AND CONDITION2_MAX <= '2') AND (CONDITION3_MIN >=
> '4' AND CONDITION3_MAX <= '4') AND NOM_PROCEDURE = 'Standardisation Déb.'
> ORDER BY NO_MACHINE,NOM_PROCEDURE
> Under condition min and max I can have alphas and num data. The request is
> made with generic program. I need your ideas.
> Thank you for your assistance!!!
>[/color]