Find the Solution of the Error...!

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

select t2.tableName as 'Table Name',t2.gametype as 'Limit',Convert(varchar,Convert(numeric(18,2),t2.smallblind))
 + '/' + Convert(varchar,Convert(numeric(18,2),t2.bigblind)) as 'Stakes',t3.GameName as 'Game Name'
from tblgameinfo t1 join tblGameTables t2 on (t1.tableid=t2.tableid) join tblGames t3 on (t3.gameid = t1.gameid)
where (t2.skinid= 6 or t2.skinid= 0) and t1.nickname = 'Player1' --order by nickname

union all


Select t2.tablename as'Table Name',t1.TableID as 'Limit',t1.TournamentID as 'Stakes',t3.TournamentName'Game Name'
from tblRegistration t1 join tblGameTables_TRMNT t2 on t1.TableID=t2.TableID join tblTournaments t3 on t3.tournamentID=t1.TournamentID
where t3.skinid= 6 and t1.nickname='Player1' AND t1.Eliminated='N' and t1.TableID >-1 and t3.State='P' and t3.TournamentType='S'

if i execute the each queary at a time its work..! but when i tried to execute with
union All keyword its generate a error like this..!

SQL

"Error converting data type varchar to numeric."

please tell me how i have to manage queary!

Posted 20-Feb-12 21:52pm

Dnyanesh Wahiley

Add a Solution

6 solutions

Solution 2

I would use CAST in stead of CONVERT

SQL

select	t2.tableName as 'Table Name',
		t2.gametype as 'Limit',
		Cast(t2.smallblind as varchar) + '/' + Cast(t2.bigblind as varchar) as 'Stakes',
		t3.GameName as 'Game Name'
from 	tblgameinfo t1 
join 	tblGameTables t2 on (t1.tableid=t2.tableid) 
join 	tblGames t3 on (t3.gameid = t1.gameid)
where	(t2.skinid= 6 or t2.skinid= 0) 
and		t1.nickname = 'Player1' 
--order by nickname

union all
Select	t2.tablename as 'Table Name',
		t1.TableID as 'Limit',
		cast(t1.TournamentID as varchar) as 'Stakes',
		t3.TournamentName'Game Name'
from 	tblRegistration t1 
join	tblGameTables_TRMNT t2 on t1.TableID=t2.TableID 
join	tblTournaments t3 on t3.tournamentID=t1.TournamentID
where t3.skinid= 6 
and t1.nickname='Player1' 
AND t1.Eliminated='N' 
and t1.TableID >-1 
and t3.State='P' 
and t3.TournamentType='S'

Posted 20-Feb-12 22:01pm

Herman<T>.Instance

Updated 20-Feb-12 22:13pm

v2

Comments

Dnyanesh Wahiley 21-Feb-12 4:09am

sir again the same problem occured...!

Herman<T>.Instance 21-Feb-12 4:14am

yeah, saw the error, is now fixed in my code. Adjusted according to Abhinacs conclusions

Dnyanesh Wahiley 21-Feb-12 4:24am

sorry sir still couldn't got it right answer..!if i executed each queary i got a right output but when i executed combined the queries it thrown an error..! and i tried before and made something changes on the basis of Abhinav's suggestion but not got it..!

kishore sharma 21-Feb-12 5:16am

hey on what project you are working it seems game development.
can you tell abt project (technology used & Language)

Solution 1

When you use the UNION or UNION ALL clause, you need to ensure that the types of the columns being returned in both the queries is the same. If the order is different, you will get this error.

For e.g. the third column in the first query Convert(varchar,Convert(numeric(18,2),t2.smallblind)) is varchar.
However, the third column in the second query appears to be numeric t1.TournamentID as 'Stakes' - hence you get this error.

Posted 20-Feb-12 22:01pm

Abhinav S

Comments

Dnyanesh Wahiley 21-Feb-12 4:25am

yes abhinav i tried but could'nt got it same error occure over there!

Solution 5

as per my opinion,you just check your select statement.
in first select statement,third argument is varchar ie what u selecting (Convert(varchar,Convert(numeric(18,2),t2.smallblind))
+ '/' + Convert(varchar,Convert(numeric(18,2),t2.bigblind)) as 'Stakes') and in second select statement third argument is might be it numeric or int ie (t1.TournamentID as 'Stakes') so before union all make that one also varchar in select statement.

mark if Its anwered your query plz

otharwise revert me back

Posted 21-Feb-12 0:01am

rajdeep kumar

Comments

Dnyanesh Wahiley 21-Feb-12 6:35am

Raajdeep if u will see digimanus sir's suggetion or query u will found that columns converted but also its not working..!

Solution 3

I think first query 'Stakes' Column is string and second query 'stakes' is integer.In second query convert the t1.TournamentID to string.

Posted 20-Feb-12 22:03pm

Adersh Ram

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)

Dnyanesh Wahiley · Accepted Answer · 2012-02-21T21:40:00

SQL

select t2.tableName as 'Table Name',convert(varchar,t2.gametype) as 'Limit',Convert(varchar,Convert(numeric(18,2),t2.smallblind))
 + '/' + Convert(varchar,Convert(numeric(18,2),t2.bigblind)) as 'Stakes',t3.GameName as 'Game Name'
from tblgameinfo t1 join tblGameTables t2 on (t1.tableid=t2.tableid) join tblGames t3 on (t3.gameid = t1.gameid)
where (t2.skinid= 6 or t2.skinid= 0) and t1.nickname = 'Player1' --order by nickname

union all


Select t2.tablename as'Table Name',convert(varchar,t1.TableID) as 'Limit',convert(varchar,t1.TournamentID) as 'Stakes',t3.TournamentName'Game Name'
from tblRegistration t1 join tblGameTables_TRMNT t2 on t1.TableID=t2.TableID join tblTournaments t3 on t3.tournamentID=t1.TournamentID
where t3.skinid= 6 and t1.nickname='Player1' AND t1.Eliminated='N' and t1.TableID >-1 and t3.State='P' and t3.TournamentType='S'

Atully what we have to do there, where as second column of both queries was in with different datatypes.! so we have to also converted them as well other columns also..! thank you digimanus sir and all of u guyies..! its works now! this code is now updated..!

kishore sharma · Accepted Answer · 2012-02-20T22:48:00

Hello,
Its the problem with values of two tables
Consider table 1 values:
Name, age
A,20
B,24
Table2 Values:
Column1 ,Column2
X,10
Y,15
Z,a
Here if you execute two talbes seperatly it wil execute but while
doing union it should match the datatype(tupe of values) of column.
so if u union these two table
2nd table value of 3rd row (Column2) a can't convert to datatype int (age column of table1)

So You are getting error Please Check once again

mark if Its anwered your query

Find the Solution of the Error...!

6 solutions

Solution 2

Solution 1

Solution 5

Solution 6

Solution 3

Solution 4

Add your solution here

Preview 0