Click here to Skip to main content
15,918,168 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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'
 
Share this answer
 
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)
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.
 
Share this answer
 
Comments
Dnyanesh Wahiley 21-Feb-12 4:25am    
yes abhinav i tried but could'nt got it same error occure over there!
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
 
Share this answer
 
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..!
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..!
 
Share this answer
 
I think first query 'Stakes' Column is string and second query 'stakes' is integer.In second query convert the t1.TournamentID to string.
 
Share this answer
 
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
 
Share this answer
 
Comments
Dnyanesh Wahiley 21-Feb-12 4:56am    
yes kishore you r right but that error occured in my first query first line and i couldn,t understant what i have to do over there i tried from last night but not get it!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900