Concatenating numbers returns different values
The output of my code is showing two different things that don't make sense based on the code. I'll do my best to illustrate since it seems I can't upload pictures from my computer in SO with guided mode...
state cvmo unique_code total
IL 201701 95316 80,162.31
IL 201701 91304 21,315.69
IL 201,701 90035 63,989.21
IL 201701 96735 54,557.76
So you can see that the cvmo column has two different values of 201701 and 201,701 and I'm not sure why. The output I'm getting for the total of IL is about double what it should be so this makes me wonder if the total are getting multiplied by two because of this. Also, each unique_code only is only paired with either 201701 or 201,701.
I have a member table with cvmo taken straight from a database and all are in the form of 201701.
Next, the following code that is used on the total table:
,case when extract(month from svc_from_dt) < 10
then trim(extract(year from svc_from_dt)) || '0' || trim(extract(month from svc_from_dt))
else trim(extract(year from svc_from_dt)) || trim(extract(month from svc_from_dt))
end as cvmo
/*This is also in the form 201701*/
The output above is taken when joining the member table on cvmo and on a unique member key.
sql concatenation teradata sqldatatypes
asked Dec 28 '18 at 21:24
resonance1resonance1
287
add a comment |
The output of my code is showing two different things that don't make sense based on the code. I'll do my best to illustrate since it seems I can't upload pictures from my computer in SO with guided mode...
state cvmo unique_code total
IL 201701 95316 80,162.31
IL 201701 91304 21,315.69
IL 201,701 90035 63,989.21
IL 201701 96735 54,557.76
So you can see that the cvmo column has two different values of 201701 and 201,701 and I'm not sure why. The output I'm getting for the total of IL is about double what it should be so this makes me wonder if the total are getting multiplied by two because of this. Also, each unique_code only is only paired with either 201701 or 201,701.
I have a member table with cvmo taken straight from a database and all are in the form of 201701.
Next, the following code that is used on the total table:
,case when extract(month from svc_from_dt) < 10
then trim(extract(year from svc_from_dt)) || '0' || trim(extract(month from svc_from_dt))
else trim(extract(year from svc_from_dt)) || trim(extract(month from svc_from_dt))
end as cvmo
/*This is also in the form 201701*/
The output above is taken when joining the member table on cvmo and on a unique member key.
sql concatenation teradata sqldatatypes
asked Dec 28 '18 at 21:24
resonance1resonance1
287
so you are saying you dont want201,701. Just provide with input sample and desired o/p
– Himanshu Ahuja
Dec 28 '18 at 21:43
add a comment |
The output of my code is showing two different things that don't make sense based on the code. I'll do my best to illustrate since it seems I can't upload pictures from my computer in SO with guided mode...
state cvmo unique_code total
IL 201701 95316 80,162.31
IL 201701 91304 21,315.69
IL 201,701 90035 63,989.21
IL 201701 96735 54,557.76
So you can see that the cvmo column has two different values of 201701 and 201,701 and I'm not sure why. The output I'm getting for the total of IL is about double what it should be so this makes me wonder if the total are getting multiplied by two because of this. Also, each unique_code only is only paired with either 201701 or 201,701.
I have a member table with cvmo taken straight from a database and all are in the form of 201701.
Next, the following code that is used on the total table:
,case when extract(month from svc_from_dt) < 10
then trim(extract(year from svc_from_dt)) || '0' || trim(extract(month from svc_from_dt))
else trim(extract(year from svc_from_dt)) || trim(extract(month from svc_from_dt))
end as cvmo
/*This is also in the form 201701*/
The output above is taken when joining the member table on cvmo and on a unique member key.
sql concatenation teradata sqldatatypes
asked Dec 28 '18 at 21:24
resonance1resonance1
287
The output of my code is showing two different things that don't make sense based on the code. I'll do my best to illustrate since it seems I can't upload pictures from my computer in SO with guided mode...
state cvmo unique_code total
IL 201701 95316 80,162.31
IL 201701 91304 21,315.69
IL 201,701 90035 63,989.21
IL 201701 96735 54,557.76
So you can see that the cvmo column has two different values of 201701 and 201,701 and I'm not sure why. The output I'm getting for the total of IL is about double what it should be so this makes me wonder if the total are getting multiplied by two because of this. Also, each unique_code only is only paired with either 201701 or 201,701.
I have a member table with cvmo taken straight from a database and all are in the form of 201701.
Next, the following code that is used on the total table:
,case when extract(month from svc_from_dt) < 10
then trim(extract(year from svc_from_dt)) || '0' || trim(extract(month from svc_from_dt))
else trim(extract(year from svc_from_dt)) || trim(extract(month from svc_from_dt))
end as cvmo
/*This is also in the form 201701*/
The output above is taken when joining the member table on cvmo and on a unique member key.
sql concatenation teradata sqldatatypes
sql concatenation teradata sqldatatypes
asked Dec 28 '18 at 21:24
resonance1resonance1
287
asked Dec 28 '18 at 21:24
resonance1resonance1
287
asked Dec 28 '18 at 21:24
resonance1resonance1
287
asked Dec 28 '18 at 21:24
resonance1resonance1
287
asked Dec 28 '18 at 21:24
resonance1resonance1
287
287
so you are saying you dont want201,701. Just provide with input sample and desired o/p
– Himanshu Ahuja
Dec 28 '18 at 21:43
add a comment |
so you are saying you dont want201,701. Just provide with input sample and desired o/p
– Himanshu Ahuja
Dec 28 '18 at 21:43
so you are saying you dont want
201,701. Just provide with input sample and desired o/p– Himanshu Ahuja
Dec 28 '18 at 21:43
so you are saying you dont want
201,701. Just provide with input sample and desired o/p– Himanshu Ahuja
Dec 28 '18 at 21:43
add a comment |
1 Answer
1
active
oldest
votes
I can't tell what's wromg, but the calculation to get YYYYMM is way too complicated, use this instead:
to_char(svc_from_dt, 'yyyymm') -- 'YYYYMM' string
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
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
});
}
});
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%2f53964434%2fconcatenating-numbers-returns-different-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I can't tell what's wromg, but the calculation to get YYYYMM is way too complicated, use this instead:
to_char(svc_from_dt, 'yyyymm') -- 'YYYYMM' string
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
add a comment |
I can't tell what's wromg, but the calculation to get YYYYMM is way too complicated, use this instead:
to_char(svc_from_dt, 'yyyymm') -- 'YYYYMM' string
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
add a comment |
I can't tell what's wromg, but the calculation to get YYYYMM is way too complicated, use this instead:
to_char(svc_from_dt, 'yyyymm') -- 'YYYYMM' string
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
I can't tell what's wromg, but the calculation to get YYYYMM is way too complicated, use this instead:
to_char(svc_from_dt, 'yyyymm') -- 'YYYYMM' string
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
answered Dec 28 '18 at 22:27
dnoethdnoeth
45k31839
45k31839
add a comment |
add a comment |
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.
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%2f53964434%2fconcatenating-numbers-returns-different-values%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
so you are saying you dont want
201,701. Just provide with input sample and desired o/p– Himanshu Ahuja
Dec 28 '18 at 21:43