Oracle function to SQL Server conversion
I have simplified a case below which is a function in Oracle. For some reason I need to test this on a SQL Server database and I'm lost as I don't know much about SQL Server and while I know I need to demonstrate how I have tried, my conversion to SQL has been so pathetic that almost every single character is flagged as error and in red!
Could somebody please give me a hint on how to do this. It is basically to create a comma separated list using two tables. So simple and yet so frustrating! Please be gentle...it's my first SQL post :-)
CREATE or REPLACE FUNCTION ListOfOrdersFunction
(inputKey IN NUMBER)
RETURN VARCHAR2 IS orderNumbers VARCHAR2(3000);
BEGIN
SELECT DISTINCT
LISTAGG (selection.order_number, ',') WITHIN GROUP (ORDER BY selection.customer_id)
OVER(PARTITION BY selection.customer_id)
INTO orderNumbers
FROM (
SELECT
customers.customerId AS customer_id
,orders.orderNumber AS order_number
FROM customberTable customers
LEFT JOIN ordersTable orders ON orders.order_key = customers.order_key
WHERE
customers.customerId = inputKey
) selection;
RETURN orderNumbers
END;
sql
sql-server oracle tsql stored-functions edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
asked Nov 13 '18 at 1:46
Rasul
88118
add a comment |
I have simplified a case below which is a function in Oracle. For some reason I need to test this on a SQL Server database and I'm lost as I don't know much about SQL Server and while I know I need to demonstrate how I have tried, my conversion to SQL has been so pathetic that almost every single character is flagged as error and in red!
Could somebody please give me a hint on how to do this. It is basically to create a comma separated list using two tables. So simple and yet so frustrating! Please be gentle...it's my first SQL post :-)
CREATE or REPLACE FUNCTION ListOfOrdersFunction
(inputKey IN NUMBER)
RETURN VARCHAR2 IS orderNumbers VARCHAR2(3000);
BEGIN
SELECT DISTINCT
LISTAGG (selection.order_number, ',') WITHIN GROUP (ORDER BY selection.customer_id)
OVER(PARTITION BY selection.customer_id)
INTO orderNumbers
FROM (
SELECT
customers.customerId AS customer_id
,orders.orderNumber AS order_number
FROM customberTable customers
LEFT JOIN ordersTable orders ON orders.order_key = customers.order_key
WHERE
customers.customerId = inputKey
) selection;
RETURN orderNumbers
END;
sql
sql-server oracle tsql stored-functions edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
asked Nov 13 '18 at 1:46
Rasul
88118
1
what version ofSQL Serverare you using ?
– Squirrel
Nov 13 '18 at 1:48
1
SQL Server 2017 has aSTRING_AGGsee docs.microsoft.com/en-us/sql/t-sql/functions/…
– Squirrel
Nov 13 '18 at 1:51
@Squirrel: using SQL Server 2017.
– Rasul
Nov 13 '18 at 2:37
1
Please also refer to SQL Server documentation on correct syntax for creating a function docs.microsoft.com/en-us/sql/t-sql/statements/…
– Squirrel
Nov 13 '18 at 3:19
@Rasul in SQL Server you can add Linked Server to an Oracle database server. So add a linked server and then you have the database and also it's functions in SQL Server.
– Mohammad Mohabbati
Nov 13 '18 at 5:26
add a comment |
I have simplified a case below which is a function in Oracle. For some reason I need to test this on a SQL Server database and I'm lost as I don't know much about SQL Server and while I know I need to demonstrate how I have tried, my conversion to SQL has been so pathetic that almost every single character is flagged as error and in red!
Could somebody please give me a hint on how to do this. It is basically to create a comma separated list using two tables. So simple and yet so frustrating! Please be gentle...it's my first SQL post :-)
CREATE or REPLACE FUNCTION ListOfOrdersFunction
(inputKey IN NUMBER)
RETURN VARCHAR2 IS orderNumbers VARCHAR2(3000);
BEGIN
SELECT DISTINCT
LISTAGG (selection.order_number, ',') WITHIN GROUP (ORDER BY selection.customer_id)
OVER(PARTITION BY selection.customer_id)
INTO orderNumbers
FROM (
SELECT
customers.customerId AS customer_id
,orders.orderNumber AS order_number
FROM customberTable customers
LEFT JOIN ordersTable orders ON orders.order_key = customers.order_key
WHERE
customers.customerId = inputKey
) selection;
RETURN orderNumbers
END;
sql
sql-server oracle tsql stored-functions edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
asked Nov 13 '18 at 1:46
Rasul
88118
I have simplified a case below which is a function in Oracle. For some reason I need to test this on a SQL Server database and I'm lost as I don't know much about SQL Server and while I know I need to demonstrate how I have tried, my conversion to SQL has been so pathetic that almost every single character is flagged as error and in red!
Could somebody please give me a hint on how to do this. It is basically to create a comma separated list using two tables. So simple and yet so frustrating! Please be gentle...it's my first SQL post :-)
CREATE or REPLACE FUNCTION ListOfOrdersFunction
(inputKey IN NUMBER)
RETURN VARCHAR2 IS orderNumbers VARCHAR2(3000);
BEGIN
SELECT DISTINCT
LISTAGG (selection.order_number, ',') WITHIN GROUP (ORDER BY selection.customer_id)
OVER(PARTITION BY selection.customer_id)
INTO orderNumbers
FROM (
SELECT
customers.customerId AS customer_id
,orders.orderNumber AS order_number
FROM customberTable customers
LEFT JOIN ordersTable orders ON orders.order_key = customers.order_key
WHERE
customers.customerId = inputKey
) selection;
RETURN orderNumbers
END;
sql
sql-server oracle tsql stored-functions sql
sql-server oracle tsql stored-functions edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
asked Nov 13 '18 at 1:46
Rasul
88118
edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
asked Nov 13 '18 at 1:46
Rasul
88118
edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
edited Nov 13 '18 at 6:53
a_horse_with_no_name
292k46446540
292k46446540
asked Nov 13 '18 at 1:46
Rasul
88118
asked Nov 13 '18 at 1:46
Rasul
88118
asked Nov 13 '18 at 1:46
Rasul
88118
88118
1
what version ofSQL Serverare you using ?
– Squirrel
Nov 13 '18 at 1:48
1
SQL Server 2017 has aSTRING_AGGsee docs.microsoft.com/en-us/sql/t-sql/functions/…
– Squirrel
Nov 13 '18 at 1:51
@Squirrel: using SQL Server 2017.
– Rasul
Nov 13 '18 at 2:37
1
Please also refer to SQL Server documentation on correct syntax for creating a function docs.microsoft.com/en-us/sql/t-sql/statements/…
– Squirrel
Nov 13 '18 at 3:19
@Rasul in SQL Server you can add Linked Server to an Oracle database server. So add a linked server and then you have the database and also it's functions in SQL Server.
– Mohammad Mohabbati
Nov 13 '18 at 5:26
add a comment |
1
what version ofSQL Serverare you using ?
– Squirrel
Nov 13 '18 at 1:48
1
SQL Server 2017 has aSTRING_AGGsee docs.microsoft.com/en-us/sql/t-sql/functions/…
– Squirrel
Nov 13 '18 at 1:51
@Squirrel: using SQL Server 2017.
– Rasul
Nov 13 '18 at 2:37
1
Please also refer to SQL Server documentation on correct syntax for creating a function docs.microsoft.com/en-us/sql/t-sql/statements/…
– Squirrel
Nov 13 '18 at 3:19
@Rasul in SQL Server you can add Linked Server to an Oracle database server. So add a linked server and then you have the database and also it's functions in SQL Server.
– Mohammad Mohabbati
Nov 13 '18 at 5:26
1
1
what version of
SQL Server are you using ?– Squirrel
Nov 13 '18 at 1:48
what version of
SQL Server are you using ?– Squirrel
Nov 13 '18 at 1:48
1
1
SQL Server 2017 has a
STRING_AGG see docs.microsoft.com/en-us/sql/t-sql/functions/…– Squirrel
Nov 13 '18 at 1:51
SQL Server 2017 has a
STRING_AGG see docs.microsoft.com/en-us/sql/t-sql/functions/…– Squirrel
Nov 13 '18 at 1:51
@Squirrel: using SQL Server 2017.
– Rasul
Nov 13 '18 at 2:37
@Squirrel: using SQL Server 2017.
– Rasul
Nov 13 '18 at 2:37
1
1
Please also refer to SQL Server documentation on correct syntax for creating a function docs.microsoft.com/en-us/sql/t-sql/statements/…
– Squirrel
Nov 13 '18 at 3:19
Please also refer to SQL Server documentation on correct syntax for creating a function docs.microsoft.com/en-us/sql/t-sql/statements/…
– Squirrel
Nov 13 '18 at 3:19
@Rasul in SQL Server you can add Linked Server to an Oracle database server. So add a linked server and then you have the database and also it's functions in SQL Server.
– Mohammad Mohabbati
Nov 13 '18 at 5:26
@Rasul in SQL Server you can add Linked Server to an Oracle database server. So add a linked server and then you have the database and also it's functions in SQL Server.
– Mohammad Mohabbati
Nov 13 '18 at 5:26
add a comment |
0
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',
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%2f53272596%2foracle-function-to-sql-server-conversion%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
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%2f53272596%2foracle-function-to-sql-server-conversion%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
what version of
SQL Serverare you using ?– Squirrel
Nov 13 '18 at 1:48
1
SQL Server 2017 has a
STRING_AGGsee docs.microsoft.com/en-us/sql/t-sql/functions/…– Squirrel
Nov 13 '18 at 1:51
@Squirrel: using SQL Server 2017.
– Rasul
Nov 13 '18 at 2:37
1
Please also refer to SQL Server documentation on correct syntax for creating a function docs.microsoft.com/en-us/sql/t-sql/statements/…
– Squirrel
Nov 13 '18 at 3:19
@Rasul in SQL Server you can add Linked Server to an Oracle database server. So add a linked server and then you have the database and also it's functions in SQL Server.
– Mohammad Mohabbati
Nov 13 '18 at 5:26