How I do error converting data type varchar to smallint?

Question

1.00/5 (2 votes)

See more:

SELECT 
    F_TARIFGAM.TG_Prix, F_TARIFGAM.TG_RefCF, F_TARIFGAM.AR_Ref, 
    P_CATTARIF.CT_Intitule, P_CATTARIF.cbIndice
FROM 
    F_TARIFGAM 
INNER JOIN
    P_CATTARIF ON F_TARIFGAM.cbMarq = P_CATTARIF.cbMarq
WHERE 
     
    F_TARIFGAM.TG_RefCF = P_CATTARIF.cbIndice

What I have tried:

CAST(P_CATTARIF.cbIndice AS VARCHAR) or
CONVERT (VARCHAR() , P_CATTARIF.cbIndice)

Posted 29-Oct-19 0:40am

stephan20

Updated 29-Oct-19 21:34pm

v2

Add a Solution

Comments

Afzaal Ahmad Zeeshan 29-Oct-19 6:41am

What error do you get?

stephan20 29-Oct-19 6:56am

error converting datatype varchar to smallint .F_TARIFGAM.TG_RefCF datatype 'varchar' and P_CATTARIF.cbIndice 'datatype smallint'

Richard Deeming 29-Oct-19 8:39am

Where did you put the CAST? It will need to be in the WHERE clause:

WHERE F_TARIFGAM.TG_RefCF = CAST(P_CATTARIF.cbIndice As varchar(20))

stephan20 29-Oct-19 9:03am

yes it work but didn't give me any data ??

Richard Deeming 29-Oct-19 10:38am

Then the data in your tables isn't what you think it is.

We can't see your data, so we can't tell you what the problem is.

Try selecting the data from both tables separately and comparing the columns you're joining on to see why they don't match.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2019-10-29T01:13:00

Solution 1

~~You cannot cast a VARCHAR (which is an array of characters) to a SMALLINT, they are totally different objects. You must use a converter, as the VARCHAR object could contain anything.~~

See Richard Deeming's comment below.

[edit]
No idea what happened to all the comments, but Richard pointed out the the SQL CAST function does convert values.
[/edit]

Posted 29-Oct-19 1:13am

Richard MacCutchan

Updated 29-Oct-19 23:36pm

v3

Comments

stephan20 29-Oct-19 7:46am

can show me where and how to use CONVERT in script . thank you

Richard Deeming 29-Oct-19 8:37am

SQL's CAST function[^] will let you do precisely that, and will throw an error if the string isn't a valid number.

Richard MacCutchan 29-Oct-19 9:35am

Thanks for clarifying.

stephan20 29-Oct-19 9:23am

so how about CONVERT .how to use it . thank you

Richard MacCutchan 29-Oct-19 9:34am

SQL Server CONVERT() Function[^]

stephan20 29-Oct-19 9:53am

i try it but she give same as cast ' no data' thank you.

Richard MacCutchan 29-Oct-19 10:13am

It is becoming fairly obvious that whatever is in that field is not a valid number. You need to use your debugger to find out exactly what data it is trying to convert.

stephan20 29-Oct-19 12:09pm

THANKS BUT I understand . i'am new in sql server . can you elaborate please

Richard MacCutchan 29-Oct-19 12:32pm

Well there is not really anything more elaborate than "use the debugger". Alternatively find some way to display the string that you are trying to convert.

stephan20 2-Nov-19 3:13am

ok i will try it , thanks You