Remove decimal places from varchar(32) result
up vote
0
down vote
favorite
I'm using SQL and windows batch script to download inventory from our POS and then upload it to a 3rd party platform. The file is successfully downloading and uploading, but the 3rd party platform is quite finicky on formatting. Specifically, it won't accept decimal place for the column titled "Quantity".
I've searched and tried various different approaches but can't seem to find one that works. The tricky aspect to this sql is that i had to use a join in order to create a title row and I'm using the format varchar(32) I've posted my sql below, any suggestions?
set nocount ON
SELECT CAST('sku' as VARCHAR(32)) AS sku,
CAST('quantity' as VARCHAR(32)) AS quantity
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV.QTY_AVAIL > 0 then
CAST(IM_INV.QTY_AVAIL AS VARCHAR(32)) else
CAST(0 as VARCHAR(32)) END as quantity
FROM IM_BARCOD INNER JOIN IM_INV ON
IM_INV.ITEM_NO = IM_BARCOD.ITEM_NO INNER
JOIN IM_PRC ON IM_INV.ITEM_NO =
IM_PRC.ITEM_NO INNER JOIN
IM_ITEM ON IM_INV.ITEM_NO = IM_ITEM.ITEM_NO
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV_CELL.QTY_AVAIL > 0 then
CAST(IM_INV_CELL.QTY_AVAIL AS VARCHAR(32))
else CAST (0 as VARCHAR (32)) END as
quantity FROM IM_BARCOD INNER JOIN IM_PRC
ON IM_BARCOD.ITEM_NO = IM_PRC.ITEM_NO INNER
JOIN IM_INV_CELL ON IM_BARCOD.ITEM_NO =
IM_INV_CELL.ITEM_NO AND
IM_INV_CELL.DIM_1_UPR=IM_BARCOD.DIM_1_UPR
AND IM_INV_CELL.DIM_2_UPR =
IM_BARCOD.DIM_2_UPR AND
IM_INV_CELL.DIM_3_UPR =IM_BARCOD.DIM_3_UPR
INNER JOIN
IM_ITEM ON IM_BARCOD.ITEM_NO =
IM_ITEM.ITEM_NO
varchar sqlcommand
edited Nov 12 at 1:48
Squashman
8,34231932
asked Nov 11 at 18:41
Liz
33
add a comment |
up vote
0
down vote
favorite
I'm using SQL and windows batch script to download inventory from our POS and then upload it to a 3rd party platform. The file is successfully downloading and uploading, but the 3rd party platform is quite finicky on formatting. Specifically, it won't accept decimal place for the column titled "Quantity".
I've searched and tried various different approaches but can't seem to find one that works. The tricky aspect to this sql is that i had to use a join in order to create a title row and I'm using the format varchar(32) I've posted my sql below, any suggestions?
set nocount ON
SELECT CAST('sku' as VARCHAR(32)) AS sku,
CAST('quantity' as VARCHAR(32)) AS quantity
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV.QTY_AVAIL > 0 then
CAST(IM_INV.QTY_AVAIL AS VARCHAR(32)) else
CAST(0 as VARCHAR(32)) END as quantity
FROM IM_BARCOD INNER JOIN IM_INV ON
IM_INV.ITEM_NO = IM_BARCOD.ITEM_NO INNER
JOIN IM_PRC ON IM_INV.ITEM_NO =
IM_PRC.ITEM_NO INNER JOIN
IM_ITEM ON IM_INV.ITEM_NO = IM_ITEM.ITEM_NO
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV_CELL.QTY_AVAIL > 0 then
CAST(IM_INV_CELL.QTY_AVAIL AS VARCHAR(32))
else CAST (0 as VARCHAR (32)) END as
quantity FROM IM_BARCOD INNER JOIN IM_PRC
ON IM_BARCOD.ITEM_NO = IM_PRC.ITEM_NO INNER
JOIN IM_INV_CELL ON IM_BARCOD.ITEM_NO =
IM_INV_CELL.ITEM_NO AND
IM_INV_CELL.DIM_1_UPR=IM_BARCOD.DIM_1_UPR
AND IM_INV_CELL.DIM_2_UPR =
IM_BARCOD.DIM_2_UPR AND
IM_INV_CELL.DIM_3_UPR =IM_BARCOD.DIM_3_UPR
INNER JOIN
IM_ITEM ON IM_BARCOD.ITEM_NO =
IM_ITEM.ITEM_NO
varchar sqlcommand
edited Nov 12 at 1:48
Squashman
8,34231932
asked Nov 11 at 18:41
Liz
33
Cast it to an integer before casting to a varchar in your SQL statement.
– Ken White
Nov 11 at 18:52
how would the formatting look for that? i.e. cast((value as (int)),as varchar(32))? am i following you correctly?
– Liz
Nov 11 at 20:38
1
No.CAST(CAST(Value as INT) AS VARCHAR(32))
– Ken White
Nov 11 at 20:48
that did it, thank you!
– Liz
Nov 11 at 20:55
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm using SQL and windows batch script to download inventory from our POS and then upload it to a 3rd party platform. The file is successfully downloading and uploading, but the 3rd party platform is quite finicky on formatting. Specifically, it won't accept decimal place for the column titled "Quantity".
I've searched and tried various different approaches but can't seem to find one that works. The tricky aspect to this sql is that i had to use a join in order to create a title row and I'm using the format varchar(32) I've posted my sql below, any suggestions?
set nocount ON
SELECT CAST('sku' as VARCHAR(32)) AS sku,
CAST('quantity' as VARCHAR(32)) AS quantity
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV.QTY_AVAIL > 0 then
CAST(IM_INV.QTY_AVAIL AS VARCHAR(32)) else
CAST(0 as VARCHAR(32)) END as quantity
FROM IM_BARCOD INNER JOIN IM_INV ON
IM_INV.ITEM_NO = IM_BARCOD.ITEM_NO INNER
JOIN IM_PRC ON IM_INV.ITEM_NO =
IM_PRC.ITEM_NO INNER JOIN
IM_ITEM ON IM_INV.ITEM_NO = IM_ITEM.ITEM_NO
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV_CELL.QTY_AVAIL > 0 then
CAST(IM_INV_CELL.QTY_AVAIL AS VARCHAR(32))
else CAST (0 as VARCHAR (32)) END as
quantity FROM IM_BARCOD INNER JOIN IM_PRC
ON IM_BARCOD.ITEM_NO = IM_PRC.ITEM_NO INNER
JOIN IM_INV_CELL ON IM_BARCOD.ITEM_NO =
IM_INV_CELL.ITEM_NO AND
IM_INV_CELL.DIM_1_UPR=IM_BARCOD.DIM_1_UPR
AND IM_INV_CELL.DIM_2_UPR =
IM_BARCOD.DIM_2_UPR AND
IM_INV_CELL.DIM_3_UPR =IM_BARCOD.DIM_3_UPR
INNER JOIN
IM_ITEM ON IM_BARCOD.ITEM_NO =
IM_ITEM.ITEM_NO
varchar sqlcommand
edited Nov 12 at 1:48
Squashman
8,34231932
asked Nov 11 at 18:41
Liz
33
I'm using SQL and windows batch script to download inventory from our POS and then upload it to a 3rd party platform. The file is successfully downloading and uploading, but the 3rd party platform is quite finicky on formatting. Specifically, it won't accept decimal place for the column titled "Quantity".
I've searched and tried various different approaches but can't seem to find one that works. The tricky aspect to this sql is that i had to use a join in order to create a title row and I'm using the format varchar(32) I've posted my sql below, any suggestions?
set nocount ON
SELECT CAST('sku' as VARCHAR(32)) AS sku,
CAST('quantity' as VARCHAR(32)) AS quantity
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV.QTY_AVAIL > 0 then
CAST(IM_INV.QTY_AVAIL AS VARCHAR(32)) else
CAST(0 as VARCHAR(32)) END as quantity
FROM IM_BARCOD INNER JOIN IM_INV ON
IM_INV.ITEM_NO = IM_BARCOD.ITEM_NO INNER
JOIN IM_PRC ON IM_INV.ITEM_NO =
IM_PRC.ITEM_NO INNER JOIN
IM_ITEM ON IM_INV.ITEM_NO = IM_ITEM.ITEM_NO
UNION
SELECT CAST(IM_BARCOD.BARCOD AS
VARCHAR(32)) as sku, case when
IM_INV_CELL.QTY_AVAIL > 0 then
CAST(IM_INV_CELL.QTY_AVAIL AS VARCHAR(32))
else CAST (0 as VARCHAR (32)) END as
quantity FROM IM_BARCOD INNER JOIN IM_PRC
ON IM_BARCOD.ITEM_NO = IM_PRC.ITEM_NO INNER
JOIN IM_INV_CELL ON IM_BARCOD.ITEM_NO =
IM_INV_CELL.ITEM_NO AND
IM_INV_CELL.DIM_1_UPR=IM_BARCOD.DIM_1_UPR
AND IM_INV_CELL.DIM_2_UPR =
IM_BARCOD.DIM_2_UPR AND
IM_INV_CELL.DIM_3_UPR =IM_BARCOD.DIM_3_UPR
INNER JOIN
IM_ITEM ON IM_BARCOD.ITEM_NO =
IM_ITEM.ITEM_NO
varchar sqlcommand
varchar sqlcommand
edited Nov 12 at 1:48
Squashman
8,34231932
asked Nov 11 at 18:41
Liz
33
edited Nov 12 at 1:48
Squashman
8,34231932
asked Nov 11 at 18:41
Liz
33
edited Nov 12 at 1:48
Squashman
8,34231932
edited Nov 12 at 1:48
Squashman
8,34231932
edited Nov 12 at 1:48
Squashman
8,34231932
8,34231932
asked Nov 11 at 18:41
Liz
33
asked Nov 11 at 18:41
Liz
33
asked Nov 11 at 18:41
Liz
33
33
Cast it to an integer before casting to a varchar in your SQL statement.
– Ken White
Nov 11 at 18:52
how would the formatting look for that? i.e. cast((value as (int)),as varchar(32))? am i following you correctly?
– Liz
Nov 11 at 20:38
1
No.CAST(CAST(Value as INT) AS VARCHAR(32))
– Ken White
Nov 11 at 20:48
that did it, thank you!
– Liz
Nov 11 at 20:55
add a comment |
Cast it to an integer before casting to a varchar in your SQL statement.
– Ken White
Nov 11 at 18:52
how would the formatting look for that? i.e. cast((value as (int)),as varchar(32))? am i following you correctly?
– Liz
Nov 11 at 20:38
1
No.CAST(CAST(Value as INT) AS VARCHAR(32))
– Ken White
Nov 11 at 20:48
that did it, thank you!
– Liz
Nov 11 at 20:55
Cast it to an integer before casting to a varchar in your SQL statement.
– Ken White
Nov 11 at 18:52
Cast it to an integer before casting to a varchar in your SQL statement.
– Ken White
Nov 11 at 18:52
how would the formatting look for that? i.e. cast((value as (int)),as varchar(32))? am i following you correctly?
– Liz
Nov 11 at 20:38
how would the formatting look for that? i.e. cast((value as (int)),as varchar(32))? am i following you correctly?
– Liz
Nov 11 at 20:38
1
1
No.
CAST(CAST(Value as INT) AS VARCHAR(32))– Ken White
Nov 11 at 20:48
No.
CAST(CAST(Value as INT) AS VARCHAR(32))– Ken White
Nov 11 at 20:48
that did it, thank you!
– Liz
Nov 11 at 20:55
that did it, thank you!
– Liz
Nov 11 at 20:55
add a comment |
active
oldest
votes
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',
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%2f53251955%2fremove-decimal-places-from-varchar32-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53251955%2fremove-decimal-places-from-varchar32-result%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
Cast it to an integer before casting to a varchar in your SQL statement.
– Ken White
Nov 11 at 18:52
how would the formatting look for that? i.e. cast((value as (int)),as varchar(32))? am i following you correctly?
– Liz
Nov 11 at 20:38
1
No.
CAST(CAST(Value as INT) AS VARCHAR(32))– Ken White
Nov 11 at 20:48
that did it, thank you!
– Liz
Nov 11 at 20:55