Data Type Conversion issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Type Conversion issue

Author

Topic

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-27 : 16:10:22

Im running the following join on derived/real tables. I get 18 rows when I run query1 mentioned below & I get 122 rows in query 2.
Column, id is nvarchar & column membernumber is INT. Im converting nvarchar to INT ...but it is still not fetching desired results..

Thanks
Anil Kumar.

Query1--

select isnull(ea.id,0) , coalesce(sum(ea.earn),0) ,
coalesce(B.membernumber,0) , coalesce(B.RecruitingBonus,0)
from [hqvm03\hqsqlinst05].sdk.dbo.DecBonusPaidSPBN Ea
left outer join
(select A.Membernumber, A.RecruitingBonus from
(select bb.beenumber as MemberNumber, sum(pc.RecruitingBonusMTD) as RecruitingBonus
from beebusiness bb
join probucketamount pc on pc.beebusinessguid = bb.beebusinessguid
join beeentity be on be.beeentityguid = bb.beeentityguid
where pc.RecruitingBonusMTD != 0 group by bb.beenumber)A)B
--
on B.Membernumber = ea.id
--
where ea.earn != 0 or b.RecruitingBonus != 0
group by isnull(ea.id,0), coalesce(B.membernumber,0), coalesce(B.RecruitingBonus,0)

Query 2--

select isnull(ea.id,0) , coalesce(sum(ea.earn),0) ,
coalesce(B.membernumber,0) , coalesce(B.RecruitingBonus,0)
from [hqvm03\hqsqlinst05].sdk.dbo.DecBonusPaidSPBN Ea
left outer join
(select A.Membernumber, A.RecruitingBonus from
(select bb.beenumber as MemberNumber, sum(pc.RecruitingBonusMTD) as RecruitingBonus
from beebusiness bb
join probucketamount pc on pc.beebusinessguid = bb.beebusinessguid
join beeentity be on be.beeentityguid = bb.beeentityguid
where pc.RecruitingBonusMTD != 0 group by bb.beenumber)A)B
--
on B.Membernumber = convert(int, ea.id)
--
where ea.earn != 0 or b.RecruitingBonus != 0
group by isnull(ea.id,0), coalesce(B.membernumber,0), coalesce(B.RecruitingBonus,0)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 16:11:23

You haven't provided enough information for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-27 : 16:20:48

Tkizer,

I think Im running into a Conversion issue & Im clueless as how to handle this.

Simply put, Query1 & Query2 are the same except for the portion that I marked as '--'.

I need to get this query right given the condition that

Id is nvarchar datatype
and
Membernumber is integer datatype.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-27 : 18:37:24

If ID is varchar and you are converting it to INT for the join then that is correct. What exactly is the conversion error?

Are all the values of ID numeric values that can be cast to an integer? What about the result set your are getting, is it not right? Is either count correct (18 or 122)?

I noticed that you are doing a LEFT OUTER JOIN to derived table B (you can remove derived table A) and I see that in your WHERE clause is "OR b.RecruitingBonus != 0" Do you need to COALESCE that or perhaps move that condition into the join clause? Because NULL != 0 is UNKNOWN unless you have changed the ANSI_NULLS settings.

EDIT: When I say youy can get rid of derived table A, I mean something like this:

SELECT
	isnull(ea.id, 0),
	COALESCE(SUM(ea.earn), 0),
	COALESCE(B.membernumber, 0),
	COALESCE(B.RecruitingBonus, 0)
FROM
	[hqvm03\hqsqlinst05].sdk.dbo.DecBonusPaidSPBN AS Ea
LEFT OUTER JOIN
	(
		SELECT
			bb.beenumber AS MemberNumber,
			SUM(pc.RecruitingBonusMTD) AS RecruitingBonus
		FROM
			beebusiness AS bb
		JOIN 
			probucketamount AS pc
			ON pc.beebusinessguid = bb.beebusinessguid
		JOIN 
			beeentity AS be
			ON be.beeentityguid = bb.beeentityguid
		WHERE
			pc.RecruitingBonusMTD != 0
		GROUP BY
			bb.beenumber
	) AS B 
	ON B.Membernumber = CONVERT(INT, ea.id)
WHERE
	ea.earn != 0
	OR b.RecruitingBonus != 0
GROUP BY
	isnull(ea.id, 0),
	COALESCE(B.membernumber, 0),
	COALESCE(B.RecruitingBonus, 0)

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-28 : 11:32:08

Lamprey,

You are right in that I should have checked that null value condition (on b.RecruitingBonus column) before using it...I changed it to the way you have suggested inside my stored procedure that uses this query & it works great.

Thank you very much !

regards,
Anil Kumar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources