Conversion failed when converting the nvarchar value 'MX_PERSON' to data type int

[Former Member]

Hi,

I am trying to check user status by running this query: my query take as input the userid= 'USER1234' as parameter

select count (distinct mskey) from idmv_vallink_basic_active where mcAttrName='MSKEYVALUE' and mcSearchValue='USER1234'

  and mskey in (select mskey from idmv_vallink_basic_active where mcattrname='MX_DEPARTEMENT' and mcsearchvalue between 20 and 50)

  and mskey in (select mskey from idmv_vallink_basic_active where mcattrname='Z_TYPE' and mcsearchvalue = 'LOCAL')

  and mskey not in (select mskey from idmv_vallink_basic_active where mcattrname='ZPOSITION1' and mcsearchvalue = '12345') 

  and mskey not in (select mskey from idmv_vallink_basic_active where mcattrname='ZPOSITION2' and mcsearchvalue = '12345')

I am using IDM 7.2 Sp7.

Can you help me to correct this query? Should I use idmv_entry_simple as start?

Michaela

[Former Member · Accepted solution]

It's probably the "mcsearchvalue between 20 and 50" that's causing the problem and you could try

ISNUMERIC(mcsearchvalue)=1 AND mcsearchvalue between 20 and 50)

to avoid running a conversion in non-int searchvalue columns.

Br,

Chris

[Former Member · Accepted solution]

Hi Chris,

I am still getting the same error even I add you input :

select mskey from idmv_vallink_basic_active where mcattrname='MX_DEPARTEMENT' and mcsearchvalue between 20 and 50

or

select mskey from idmv_vallink_basic_active where mcattrname='MX_DEPARTEMENT' and ISNUMERIC(mcsearchvalue)=1 AND mcsearchvalue between 20 and 50

giving me a list of mskey.

Any help?

Micheala

[Former Member · Accepted solution]

Hm,

This fails for me:

SELECT mskey FROM idmv_value_basic_active where isnumeric(searchvalue)=1 and searchvalue between 20 and 50

This works:

SELECT mskey FROM idmv_value_basic_active where isnumeric(searchvalue)=1 and CAST(CAST(searchvalue AS MONEY) AS INT) between 20 and 50

seems like the cast to money is more tolerant than just a cast to INT as it filters out any non-numeric chars like $ ( ) USD NOK and similar and leave just the numerical value. Don't like it but it works. Found it on the web. So this

SELECT mskey FROM idmv_value_basic_active where attrname='MX_DEPARTEMENT' AND isnumeric(searchvalue)=1 and CAST(CAST(searchvalue AS MONEY) AS INT) between 20 and 50

should work (in theory)

Br,

Chris

Message was edited by: Per Krabsetsve

[Former Member · Accepted solution]

My reply below was indended for you, not myself 🙂

Per Krabsetsve Aug 6, 2014 5:30 PM (in response to Per Krabsetsve)

Hm,

This fails for me:
SELECT mskey FROM idmv_value_basic_active where isnumeric(searchvalue)=1 and searchvalue between 20 and 50

This works:
SELECT mskey FROM idmv_value_basic_active where isnumeric(searchvalue)=1 and CAST(CAST(searchvalue AS MONEY) AS INT) between 20 and 50

seems like the cast to money is more tolerant than just a cast to INT as it filters out any non-numeric chars like $ ( ) USD NOK and similar and leave just the numerical value. Don't like it but it works. Found it on the web. So this

SELECT mskey FROM idmv_value_basic_active where attrname='MX_DEPARTEMENT' AND isnumeric(searchvalue)=1 and CAST(CAST(searchvalue AS MONEY) AS INT) between 20 and 50

should work (in theory)

Br,
Chris

[Former Member · Accepted solution]

Hi Chris,

After adding "CAST(CAST(searchvalue AS MONEY) AS INT)" this my query works correctly, why adding "MONEY" to the query here resolves my problem?

Best regards,

Michaela

[Former Member · Accepted solution]

My guess is that "money" is a datatype that expects chars in the value such as $39.99, 299USD, £56,46 etc. and that the conversion of varchar to money simply strips away any non-numeric char (except , and . most likely).

In my mind the real problem is that the ISNUMERIC function allows a set of non-numeric chars such as ( ) + - and still report the value as numeric and that there is no YESTHISISREALLYONLYANUMERICALVALUECONTAININGJUST0TO9 function built in.

Br,

Chris

[Former Member · Accepted solution]

Hi Chris,

Thanks for the explanation, I confirm that we have to add also : isnumeric(searchvalue)=1 to work:

isnumeric(searchvalue)=1 and CAST(CAST(searchvalue AS MONEY) AS INT)

Best regards,

Michaela

[normann]

Hi Michaela,

I would recommend you to use idmv_value_* for non reference attributes and idmv_link_* for reference attributes for performance reasons. Try this:

count (distinct mskeyvalue.mskey) from idmv_value_basic_active mskeyvalue, idmv_value_basic_active department,

idmv_value_basic_active type_, idmv_value_basic_active position1, idmv_value_basic_active position2

where mskeyvalue.SearchValue = 'MSKEYVALUE' and mskeyvalue.SearchValue = 'USER1234'

and department.SearchValue = 'MX_DEPARTEMENT' and department.SearchValue between 20 and 50

and type_.SearchValue = 'Z_TYPE' and type_.SearchValue = 'LOCAL'

and position1.SearchValue = 'ZPOSITION1' and position1.SearchValue = '12345'

and position2.SearchValue = 'ZPOSITION2' and position2.SearchValue = '12345'

and mskeyvalue.mskey = department.mskey and mskeyvalue.mskey = type_.mskey and mskeyvalue.mskey = position1.mskey

and mskeyvalue.mskey = position2.mskey

Regards

Norman

[Former Member]

Hi Norman,

Thanks for your help, I will try this and keep you updated.

Michaela

[Former Member]

Hi Norman,

I am still getting the same error as described.

wthat should be done for "mskey NOT IN table"" part?

Michaela

[Steffi_Warnecke]

Hello Michaela,

did you run the query part by part by adding one "and" after another to see which one throws the error?

I mean first running

select count (distinct mskey) from idmv_vallink_basic_active where mcAttrName='MSKEYVALUE' and mcSearchValue='USER1234'
and mskey in (select mskey from idmv_vallink_basic_active where mcattrname='MX_DEPARTEMENT' and mcsearchvalue between 20 and 50) 

then

select count (distinct mskey) from idmv_vallink_basic_active where mcAttrName='MSKEYVALUE' and mcSearchValue='USER1234'
and mskey in (select mskey from idmv_vallink_basic_active where mcattrname='MX_DEPARTEMENT' and mcsearchvalue between 20 and 50)  
and mskey in (select mskey from idmv_vallink_basic_active where mcattrname='Z_TYPE' and mcsearchvalue = 'LOCAL')

and so forth.

Regards,

Steffi.

[Former Member]

Hi Steffi,

I tired to run only the first part and I getting the same error

select count (distinct mskey) from idmv_vallink_basic_active where mcAttrName='MSKEYVALUE' and mcSearchValue='USER1234'

and mskey in (select mskey from idmv_vallink_basic_active where mcattrname='MX_DEPARTEMENT' and mcsearchvalue between 20 and 50)

Michaela,

[Steffi_Warnecke]

Do you get a result when running:

select mskey from idmv_vallink_basic_active where mcattrname='MX_DEPARTEMENT' and mcsearchvalue between 20 and 50

[Former Member]

Hi Steffi,

Yes, I get a list of some mskey values.

Michaela,