How to Join (equal) two data columns that belongs to the String and Double types respectively in Alibaba...

Multi tool use
I am not authorized to share the table details.
For instance, let me consider an Example:
I am trying to join two columns of String and Double data types respectively in Alibaba MaxCompute.
In the earlier version of MaxCompute, the String and Double data types are converted to the bigint data type at the cost of precision. 1.1 = “1” in a Join condition.
Whereas the same code does not work in the new version of the MaxCompute. The code syntax is like follows:
SELECT * FROM t1 JOIN t2 ON t1.double_value = t2.string_value;
Error:
WARNING:[1,48] implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress
What is the correct syntax to do the join operation in Alibaba MaxCompute V2?
sql alibaba-cloud
add a comment |
I am not authorized to share the table details.
For instance, let me consider an Example:
I am trying to join two columns of String and Double data types respectively in Alibaba MaxCompute.
In the earlier version of MaxCompute, the String and Double data types are converted to the bigint data type at the cost of precision. 1.1 = “1” in a Join condition.
Whereas the same code does not work in the new version of the MaxCompute. The code syntax is like follows:
SELECT * FROM t1 JOIN t2 ON t1.double_value = t2.string_value;
Error:
WARNING:[1,48] implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress
What is the correct syntax to do the join operation in Alibaba MaxCompute V2?
sql alibaba-cloud
add a comment |
I am not authorized to share the table details.
For instance, let me consider an Example:
I am trying to join two columns of String and Double data types respectively in Alibaba MaxCompute.
In the earlier version of MaxCompute, the String and Double data types are converted to the bigint data type at the cost of precision. 1.1 = “1” in a Join condition.
Whereas the same code does not work in the new version of the MaxCompute. The code syntax is like follows:
SELECT * FROM t1 JOIN t2 ON t1.double_value = t2.string_value;
Error:
WARNING:[1,48] implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress
What is the correct syntax to do the join operation in Alibaba MaxCompute V2?
sql alibaba-cloud
I am not authorized to share the table details.
For instance, let me consider an Example:
I am trying to join two columns of String and Double data types respectively in Alibaba MaxCompute.
In the earlier version of MaxCompute, the String and Double data types are converted to the bigint data type at the cost of precision. 1.1 = “1” in a Join condition.
Whereas the same code does not work in the new version of the MaxCompute. The code syntax is like follows:
SELECT * FROM t1 JOIN t2 ON t1.double_value = t2.string_value;
Error:
WARNING:[1,48] implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress
What is the correct syntax to do the join operation in Alibaba MaxCompute V2?
sql alibaba-cloud
sql alibaba-cloud
asked Dec 30 '18 at 12:03


Ranjith UdayakumarRanjith Udayakumar
204
204
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I did a bit of digging and it seems like this SQL command is the recommended way of getting around this issue.
select * from t1 join t2 on t.double_value = cast(t2.string_value as double);
add a comment |
As the error message suggests:
SELECT *
FROM t1 JOIN
t2
ON CAST(t1.double_value as string) = t2.string_value;
Yep, that is clear but is there any way to do the same operation without explicit conversion.
– Ranjith Udayakumar
Dec 30 '18 at 14:19
@RanjithUdayakumar . . . Apparently not. You should not be storing such keys using different data types anyway. That is a sign of poor table design. I would also recommend that you fix the table structures.
– Gordon Linoff
Dec 30 '18 at 14:25
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%2f53977419%2fhow-to-join-equal-two-data-columns-that-belongs-to-the-string-and-double-types%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
I did a bit of digging and it seems like this SQL command is the recommended way of getting around this issue.
select * from t1 join t2 on t.double_value = cast(t2.string_value as double);
add a comment |
I did a bit of digging and it seems like this SQL command is the recommended way of getting around this issue.
select * from t1 join t2 on t.double_value = cast(t2.string_value as double);
add a comment |
I did a bit of digging and it seems like this SQL command is the recommended way of getting around this issue.
select * from t1 join t2 on t.double_value = cast(t2.string_value as double);
I did a bit of digging and it seems like this SQL command is the recommended way of getting around this issue.
select * from t1 join t2 on t.double_value = cast(t2.string_value as double);
answered Dec 30 '18 at 17:10


Deepak KamatDeepak Kamat
1,10121327
1,10121327
add a comment |
add a comment |
As the error message suggests:
SELECT *
FROM t1 JOIN
t2
ON CAST(t1.double_value as string) = t2.string_value;
Yep, that is clear but is there any way to do the same operation without explicit conversion.
– Ranjith Udayakumar
Dec 30 '18 at 14:19
@RanjithUdayakumar . . . Apparently not. You should not be storing such keys using different data types anyway. That is a sign of poor table design. I would also recommend that you fix the table structures.
– Gordon Linoff
Dec 30 '18 at 14:25
add a comment |
As the error message suggests:
SELECT *
FROM t1 JOIN
t2
ON CAST(t1.double_value as string) = t2.string_value;
Yep, that is clear but is there any way to do the same operation without explicit conversion.
– Ranjith Udayakumar
Dec 30 '18 at 14:19
@RanjithUdayakumar . . . Apparently not. You should not be storing such keys using different data types anyway. That is a sign of poor table design. I would also recommend that you fix the table structures.
– Gordon Linoff
Dec 30 '18 at 14:25
add a comment |
As the error message suggests:
SELECT *
FROM t1 JOIN
t2
ON CAST(t1.double_value as string) = t2.string_value;
As the error message suggests:
SELECT *
FROM t1 JOIN
t2
ON CAST(t1.double_value as string) = t2.string_value;
answered Dec 30 '18 at 12:13
Gordon LinoffGordon Linoff
768k35300402
768k35300402
Yep, that is clear but is there any way to do the same operation without explicit conversion.
– Ranjith Udayakumar
Dec 30 '18 at 14:19
@RanjithUdayakumar . . . Apparently not. You should not be storing such keys using different data types anyway. That is a sign of poor table design. I would also recommend that you fix the table structures.
– Gordon Linoff
Dec 30 '18 at 14:25
add a comment |
Yep, that is clear but is there any way to do the same operation without explicit conversion.
– Ranjith Udayakumar
Dec 30 '18 at 14:19
@RanjithUdayakumar . . . Apparently not. You should not be storing such keys using different data types anyway. That is a sign of poor table design. I would also recommend that you fix the table structures.
– Gordon Linoff
Dec 30 '18 at 14:25
Yep, that is clear but is there any way to do the same operation without explicit conversion.
– Ranjith Udayakumar
Dec 30 '18 at 14:19
Yep, that is clear but is there any way to do the same operation without explicit conversion.
– Ranjith Udayakumar
Dec 30 '18 at 14:19
@RanjithUdayakumar . . . Apparently not. You should not be storing such keys using different data types anyway. That is a sign of poor table design. I would also recommend that you fix the table structures.
– Gordon Linoff
Dec 30 '18 at 14:25
@RanjithUdayakumar . . . Apparently not. You should not be storing such keys using different data types anyway. That is a sign of poor table design. I would also recommend that you fix the table structures.
– Gordon Linoff
Dec 30 '18 at 14:25
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%2f53977419%2fhow-to-join-equal-two-data-columns-that-belongs-to-the-string-and-double-types%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
I6BP200SW3jqu N3d N H PTRBM7R0,6JCQ,Z HxhK,dpY mZx4MZX1w7Mr ZI8p RRaFingv2L9oDD,u,Gb mTza036CgaCtzy7Thg