How to change a Float into a VARCHAR(500)

Question

How to change a Float into a VARCHAR(500)

Sarah George 1

In the design I want to link AgreementId from AgIDTenRef box to 2ndyrletonlyfee on the 'RenewalsCompleted' box (as shown) but when I do either of these it errors: CAST(v_statActiveCount_mis.2ndyrletonlyfee AS FLOAT) AS TEST CAST(AgIDTenRef.AgreementId AS VARCHAR) AS TEST

One of the errors is 'Error converting data type from varchar to float'

2ndyrletonlyfee = VARCHAR Agreement Id = FLOAT

PLEASE HELP!

Thanks Sarah

Viorel 122.6K Reputation points

2022-09-22T16:12:37.837+00:00
If it has sense to link the AgreementId values with 2ndyrletonlyfee (not with AgreeID), converting text to float, then this is problematic if the text is not a number.

To find the suspicious rows, try executing a query like this (manually):

select 2ndyrletonlyfee, * from v_statActiveCount_mis where 2ndyrletonlyfee is not null and try_cast(2ndyrletonlyfee as float) is null

It will display the values that cannot be converted and linked. How do you want to proceed in this case?

By the way, since AgreementId is FLOAT, then it should not be converted to VARCHAR.
LiHongMSFT-4306 31,571 Reputation points

2022-09-23T03:08:43.193+00:00

Hi @Sarah George

I want to link AgreementId from AgIDTenRef box to 2ndyrletonlyfee on the 'RenewalsCompleted' box (as shown)

However, in the screenshot you provided I only saw the AgreementId from AgIDTenRef box being linked to AgreeID on the 'RenewalsCompleted' box.
LiHongMSFT-4306 31,571 Reputation points

2022-09-27T01:34:04.147+00:00

Hi @Sarah George
We haven't heard any response from you yet. Is this issue solved now? If you have any questions, please feel free to share with us.

Best regards,
LiHong

1 answer

Your answer

Viorel 122.6K Reputation points

2022-09-22T16:12:37.837+00:00

If it has sense to link the AgreementId values with 2ndyrletonlyfee (not with AgreeID), converting text to float, then this is problematic if the text is not a number.

To find the suspicious rows, try executing a query like this (manually):

select 2ndyrletonlyfee, * from v_statActiveCount_mis where 2ndyrletonlyfee is not null and try_cast(2ndyrletonlyfee as float) is null

It will display the values that cannot be converted and linked. How do you want to proceed in this case?

By the way, since AgreementId is FLOAT, then it should not be converted to VARCHAR.
LiHongMSFT-4306 31,571 Reputation points

2022-09-23T03:08:43.193+00:00

Hi @Sarah George

I want to link AgreementId from AgIDTenRef box to 2ndyrletonlyfee on the 'RenewalsCompleted' box (as shown)

However, in the screenshot you provided I only saw the AgreementId from AgIDTenRef box being linked to AgreeID on the 'RenewalsCompleted' box.
LiHongMSFT-4306 31,571 Reputation points

2022-09-27T01:34:04.147+00:00

Hi @Sarah George
We haven't heard any response from you yet. Is this issue solved now? If you have any questions, please feel free to share with us.

Best regards,
LiHong

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

I will need to say that this sounds dubious. Something called ID which is a float? Float is good for scientific values can have very high or low ranges, but it is an approximate number, so it is not good for an id:

And comparing that to a varchar(500). As Viorel says, this will be problematic.

I think you need to go back and review the database design. I have a feeling that you went wrong at some point earlier before you came to this brick wall.

Share via

How to change a Float into a VARCHAR(500)

1 answer

Your answer