August 27, 2010 at 4:25 pm
Hello,
I am very new to writing SQL code. I am joining two tables and the field I'm joining on is a different type in each table. The BAN field is decimal in the a table and varchar in the s table.
SELECT
a.BAN,
a.ACC_TYPE,
a.ACC_TYPE_SUB_TYPE_DESC,
a.BL_MAJOR_ACCOUNT_NO,
a.ACTIVE_SUBSCRIBER_CNT,
s.SCS_PR
s.SCS_Manager_PR,
s.SCR_Manager_PR,
s.SCS_SR_Manager_PR,
s.SCR_SR_Manager_PR
FROM dbo.ndw_account_dim a right outer join dbo.sct_master_query s
ON a.BAN = s.BAN
WHERE a.BAN IN ('144537397','271918331','591906437')
Thank you!
August 27, 2010 at 4:31 pm
Try googling "CAST SQL" or "CONVERT SQL"
You should be able to just copy and paste the codes and replace the column names.
August 27, 2010 at 4:37 pm
Yes, I did try that. I'm not clear on where exactly it goes in the query and the exact syntax.
August 27, 2010 at 4:58 pm
Do you have non numerical values in table s?
August 27, 2010 at 5:03 pm
Oh and what's precision and scale of the decimal? DECIMAL(?,?)
August 27, 2010 at 7:37 pm
The decimal is 9.
August 29, 2010 at 11:28 am
This should work for you
ON CAST(a.BAN as varchar(??)) = s.BAN
where ?? is the varchar length!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2010 at 3:34 am
or even CONVERT(VARCHAR(??),a.BAN) = s.BAN 🙂
August 30, 2010 at 3:52 am
yes, that too! 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2010 at 1:00 pm
If data type is DECIAL(9, 0), which means no decimals, then you can use the method suggested on previous posts.
SELECT
a.BAN,
a.ACC_TYPE,
a.ACC_TYPE_SUB_TYPE_DESC,
a.BL_MAJOR_ACCOUNT_NO,
a.ACTIVE_SUBSCRIBER_CNT,
s.SCS_PR
s.SCS_Manager_PR,
s.SCR_Manager_PR,
s.SCS_SR_Manager_PR,
s.SCR_SR_Manager_PR
FROM dbo.ndw_account_dim a right outer join dbo.sct_master_query s
ON a.BAN = s.BAN
WHERE a.BAN IN ('144537397','271918331','591906437')
FYI,
This query will only return three rows (assuming '144537397','271918331','591906437' exists).
In other words, your right outer join will behave like a inner join because you are filtering on dbo.ndw_account_dim in the where statement.
You should filter on dbo.sct_master_query for your right outer join to behave like a outer join.
September 1, 2010 at 6:27 am
Yes, these both work perfectly. Thank you so much!! 😀
September 1, 2010 at 6:33 am
Thanks for your note. What I am trying to get is all rows from the dbo.ndw_account_dim a table that meet the where clause (I only put in a few accounts but usually there are at least several hundred). Additionally, I want those fields from dbo.sct_master_query s when those account numbers that are in the where clause are in that table. They are not always in dbo.sct_master_query but when they are, I need those fields. I hope I'm making sense.
Do I need to change my join?
Thanks again. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply