Converting the varchar value
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I am attempting to count the values that are same in both the tables and seem to be running into an error on the first line.
select
count(substring(c.source_code, 3, 4)) comb, ---->Error
count(os.CompanyCode) comcode
from
combined c
inner join
Completes os on os.companycode = substring(c.source_code,3,4)
where
os.ReturnYearFiled = 2017
and c.return_year = 2017
Error:
Conversion failed when converting the varchar value 'SH' to data type int
Dataset:
Combined.sourcecode Completes.companycode
------------------- ---------------------
01582365 5823
14785698 7856
45879652 8796
87459633 4596
14563344 5633
Results requested:
comb comcode
------------------------------------------
5 5
sql
sql-server sql-server-2016 edited Nov 23 '18 at 15:52
marc_s
584k13011241271
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
|
show 5 more comments
0
I am attempting to count the values that are same in both the tables and seem to be running into an error on the first line.
select
count(substring(c.source_code, 3, 4)) comb, ---->Error
count(os.CompanyCode) comcode
from
combined c
inner join
Completes os on os.companycode = substring(c.source_code,3,4)
where
os.ReturnYearFiled = 2017
and c.return_year = 2017
Error:
Conversion failed when converting the varchar value 'SH' to data type int
Dataset:
Combined.sourcecode Completes.companycode
------------------- ---------------------
01582365 5823
14785698 7856
45879652 8796
87459633 4596
14563344 5633
Results requested:
comb comcode
------------------------------------------
5 5
sql
sql-server sql-server-2016 edited Nov 23 '18 at 15:52
marc_s
584k13011241271
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
1
And it's not the JOIN condition?
– jarlh
Nov 23 '18 at 15:30
Please provide sample data and desired results. The line you have highlighted is not causing this error.
– Gordon Linoff
Nov 23 '18 at 15:30
@Gordon, provided.
– Jake Wagner
Nov 23 '18 at 15:33
1
Note that the line numbers given in SQL Server error messages tell you "the error occurred in the statement (no matter how many lines it covers) that started here"
– Damien_The_Unbeliever
Nov 23 '18 at 15:38
1
@JakeWagner . . . An error on "SH" is definitely not coming from the values you have provided. Your sample data doesn't illustrate the question.
– Gordon Linoff
Nov 23 '18 at 15:39
|
show 5 more comments
0
0
0
I am attempting to count the values that are same in both the tables and seem to be running into an error on the first line.
select
count(substring(c.source_code, 3, 4)) comb, ---->Error
count(os.CompanyCode) comcode
from
combined c
inner join
Completes os on os.companycode = substring(c.source_code,3,4)
where
os.ReturnYearFiled = 2017
and c.return_year = 2017
Error:
Conversion failed when converting the varchar value 'SH' to data type int
Dataset:
Combined.sourcecode Completes.companycode
------------------- ---------------------
01582365 5823
14785698 7856
45879652 8796
87459633 4596
14563344 5633
Results requested:
comb comcode
------------------------------------------
5 5
sql
sql-server sql-server-2016 edited Nov 23 '18 at 15:52
marc_s
584k13011241271
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
I am attempting to count the values that are same in both the tables and seem to be running into an error on the first line.
select
count(substring(c.source_code, 3, 4)) comb, ---->Error
count(os.CompanyCode) comcode
from
combined c
inner join
Completes os on os.companycode = substring(c.source_code,3,4)
where
os.ReturnYearFiled = 2017
and c.return_year = 2017
Error:
Conversion failed when converting the varchar value 'SH' to data type int
Dataset:
Combined.sourcecode Completes.companycode
------------------- ---------------------
01582365 5823
14785698 7856
45879652 8796
87459633 4596
14563344 5633
Results requested:
comb comcode
------------------------------------------
5 5
sql
sql-server sql-server-2016 sql
sql-server sql-server-2016 edited Nov 23 '18 at 15:52
marc_s
584k13011241271
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
edited Nov 23 '18 at 15:52
marc_s
584k13011241271
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
edited Nov 23 '18 at 15:52
marc_s
584k13011241271
edited Nov 23 '18 at 15:52
marc_s
584k13011241271
edited Nov 23 '18 at 15:52
marc_s
584k13011241271
584k13011241271
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
asked Nov 23 '18 at 15:28
Jake WagnerJake Wagner
276417
276417
1
And it's not the JOIN condition?
– jarlh
Nov 23 '18 at 15:30
Please provide sample data and desired results. The line you have highlighted is not causing this error.
– Gordon Linoff
Nov 23 '18 at 15:30
@Gordon, provided.
– Jake Wagner
Nov 23 '18 at 15:33
1
Note that the line numbers given in SQL Server error messages tell you "the error occurred in the statement (no matter how many lines it covers) that started here"
– Damien_The_Unbeliever
Nov 23 '18 at 15:38
1
@JakeWagner . . . An error on "SH" is definitely not coming from the values you have provided. Your sample data doesn't illustrate the question.
– Gordon Linoff
Nov 23 '18 at 15:39
|
show 5 more comments
1
And it's not the JOIN condition?
– jarlh
Nov 23 '18 at 15:30
Please provide sample data and desired results. The line you have highlighted is not causing this error.
– Gordon Linoff
Nov 23 '18 at 15:30
@Gordon, provided.
– Jake Wagner
Nov 23 '18 at 15:33
1
Note that the line numbers given in SQL Server error messages tell you "the error occurred in the statement (no matter how many lines it covers) that started here"
– Damien_The_Unbeliever
Nov 23 '18 at 15:38
1
@JakeWagner . . . An error on "SH" is definitely not coming from the values you have provided. Your sample data doesn't illustrate the question.
– Gordon Linoff
Nov 23 '18 at 15:39
1
1
And it's not the JOIN condition?
– jarlh
Nov 23 '18 at 15:30
And it's not the JOIN condition?
– jarlh
Nov 23 '18 at 15:30
Please provide sample data and desired results. The line you have highlighted is not causing this error.
– Gordon Linoff
Nov 23 '18 at 15:30
Please provide sample data and desired results. The line you have highlighted is not causing this error.
– Gordon Linoff
Nov 23 '18 at 15:30
@Gordon, provided.
– Jake Wagner
Nov 23 '18 at 15:33
@Gordon, provided.
– Jake Wagner
Nov 23 '18 at 15:33
1
1
Note that the line numbers given in SQL Server error messages tell you "the error occurred in the statement (no matter how many lines it covers) that started here"
– Damien_The_Unbeliever
Nov 23 '18 at 15:38
Note that the line numbers given in SQL Server error messages tell you "the error occurred in the statement (no matter how many lines it covers) that started here"
– Damien_The_Unbeliever
Nov 23 '18 at 15:38
1
1
@JakeWagner . . . An error on "SH" is definitely not coming from the values you have provided. Your sample data doesn't illustrate the question.
– Gordon Linoff
Nov 23 '18 at 15:39
@JakeWagner . . . An error on "SH" is definitely not coming from the values you have provided. Your sample data doesn't illustrate the question.
– Gordon Linoff
Nov 23 '18 at 15:39
|
show 5 more comments
2 Answers
2
active
oldest
votes
2
I think CompanyCode or Source_code has alphanumeric data. Because of that you get error message.
I have updated your script, it should be ok:
SELECT
COUNT(SUBSTRING(c.source_code, 3, 4)) comb
,COUNT(os.CompanyCode) comcode
FROM combined c
INNER JOIN Completes os
ON CAST(os.companycode AS VARCHAR(100)) = CAST(SUBSTRING(c.source_code, 3, 4) AS VARCHAR(100))
WHERE os.ReturnYearFiled = 2017
AND c.return_year = 2017
Also, if you want to check which values are alphanumeric you can use ISNUMERIC :
SELECT * FROM combined WHERE IsNumeric(source_code)=0
SELECT * FROM Completes WHERE IsNumeric(source_code)=0
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
add a comment |
1
if ouy need the number of items not null from this you can use :
select sum(case when c.source_code is not null then 1 else 0 end) combCount,
sum(case when os.CompanyCode is not null then 1 else 0 end) comcodeCount,
substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
or if you just need the number of records:
select count(*) as records, substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53449325%2fconverting-the-varchar-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
I think CompanyCode or Source_code has alphanumeric data. Because of that you get error message.
I have updated your script, it should be ok:
SELECT
COUNT(SUBSTRING(c.source_code, 3, 4)) comb
,COUNT(os.CompanyCode) comcode
FROM combined c
INNER JOIN Completes os
ON CAST(os.companycode AS VARCHAR(100)) = CAST(SUBSTRING(c.source_code, 3, 4) AS VARCHAR(100))
WHERE os.ReturnYearFiled = 2017
AND c.return_year = 2017
Also, if you want to check which values are alphanumeric you can use ISNUMERIC :
SELECT * FROM combined WHERE IsNumeric(source_code)=0
SELECT * FROM Completes WHERE IsNumeric(source_code)=0
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
add a comment |
2
I think CompanyCode or Source_code has alphanumeric data. Because of that you get error message.
I have updated your script, it should be ok:
SELECT
COUNT(SUBSTRING(c.source_code, 3, 4)) comb
,COUNT(os.CompanyCode) comcode
FROM combined c
INNER JOIN Completes os
ON CAST(os.companycode AS VARCHAR(100)) = CAST(SUBSTRING(c.source_code, 3, 4) AS VARCHAR(100))
WHERE os.ReturnYearFiled = 2017
AND c.return_year = 2017
Also, if you want to check which values are alphanumeric you can use ISNUMERIC :
SELECT * FROM combined WHERE IsNumeric(source_code)=0
SELECT * FROM Completes WHERE IsNumeric(source_code)=0
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
add a comment |
2
2
2
I think CompanyCode or Source_code has alphanumeric data. Because of that you get error message.
I have updated your script, it should be ok:
SELECT
COUNT(SUBSTRING(c.source_code, 3, 4)) comb
,COUNT(os.CompanyCode) comcode
FROM combined c
INNER JOIN Completes os
ON CAST(os.companycode AS VARCHAR(100)) = CAST(SUBSTRING(c.source_code, 3, 4) AS VARCHAR(100))
WHERE os.ReturnYearFiled = 2017
AND c.return_year = 2017
Also, if you want to check which values are alphanumeric you can use ISNUMERIC :
SELECT * FROM combined WHERE IsNumeric(source_code)=0
SELECT * FROM Completes WHERE IsNumeric(source_code)=0
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
I think CompanyCode or Source_code has alphanumeric data. Because of that you get error message.
I have updated your script, it should be ok:
SELECT
COUNT(SUBSTRING(c.source_code, 3, 4)) comb
,COUNT(os.CompanyCode) comcode
FROM combined c
INNER JOIN Completes os
ON CAST(os.companycode AS VARCHAR(100)) = CAST(SUBSTRING(c.source_code, 3, 4) AS VARCHAR(100))
WHERE os.ReturnYearFiled = 2017
AND c.return_year = 2017
Also, if you want to check which values are alphanumeric you can use ISNUMERIC :
SELECT * FROM combined WHERE IsNumeric(source_code)=0
SELECT * FROM Completes WHERE IsNumeric(source_code)=0
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 15:43
Zeki GumusZeki Gumus
1,445313
1,445313
add a comment |
add a comment |
1
if ouy need the number of items not null from this you can use :
select sum(case when c.source_code is not null then 1 else 0 end) combCount,
sum(case when os.CompanyCode is not null then 1 else 0 end) comcodeCount,
substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
or if you just need the number of records:
select count(*) as records, substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
add a comment |
1
if ouy need the number of items not null from this you can use :
select sum(case when c.source_code is not null then 1 else 0 end) combCount,
sum(case when os.CompanyCode is not null then 1 else 0 end) comcodeCount,
substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
or if you just need the number of records:
select count(*) as records, substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
add a comment |
1
1
1
if ouy need the number of items not null from this you can use :
select sum(case when c.source_code is not null then 1 else 0 end) combCount,
sum(case when os.CompanyCode is not null then 1 else 0 end) comcodeCount,
substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
or if you just need the number of records:
select count(*) as records, substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
if ouy need the number of items not null from this you can use :
select sum(case when c.source_code is not null then 1 else 0 end) combCount,
sum(case when os.CompanyCode is not null then 1 else 0 end) comcodeCount,
substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
or if you just need the number of records:
select count(*) as records, substring(c.source_code,3,4) comb,
os.CompanyCode as comcode
from combined c
inner join Completes os on os.companycode = substring(c.source_code,3,4)
where os.ReturnYearFiled = 2017
and c.return_year = 2017
group by substring(c.source_code,3,4),os.CompanyCode
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
answered Nov 23 '18 at 15:38
picklerickpicklerick
32918
32918
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53449325%2fconverting-the-varchar-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
And it's not the JOIN condition?
– jarlh
Nov 23 '18 at 15:30
Please provide sample data and desired results. The line you have highlighted is not causing this error.
– Gordon Linoff
Nov 23 '18 at 15:30
@Gordon, provided.
– Jake Wagner
Nov 23 '18 at 15:33
1
Note that the line numbers given in SQL Server error messages tell you "the error occurred in the statement (no matter how many lines it covers) that started here"
– Damien_The_Unbeliever
Nov 23 '18 at 15:38
1
@JakeWagner . . . An error on "SH" is definitely not coming from the values you have provided. Your sample data doesn't illustrate the question.
– Gordon Linoff
Nov 23 '18 at 15:39