Oracle to SQl Server Function

Question

Hi There,
I am trying to convert a function form Rocale to SQL server and having some issues with syntx for the conversion. Her eis teh code snippet that shows the Oracle fucntion.  

FOR REC IN (SELECT * FROM OWNENAME)
LOOP
  ownerName = ownerName || ' AND ' || REC.OWNERNAME;
END LOOP;

Thanks in advance
Jay

Answer 1

What does this function do?

---

Abdallah, PMP, MCTS

Answer 2

Hi Abdallah,
It concatenate the all the owner names and retuns the output. Below is the full function.
Thanks
Jay   

CREATE OR REPLACE FUNCTION "UFR_CONCAT_OWNER_NAME"
(
)
RETURN VARCHAR2
AS
  ownerName                  VARCHAR2(2000);
BEGIN

     FOR REC IN
     (
       SELECT OWNER_NAME.OWNER_NAME
                     FROM OWNER_NAME
     )
     LOOP
       ownerName := ownerName || ' AND ' || REC.OWNER_NAME;
     END LOOP;

 RETURN  ownerName;
END;
 

Answer 3

can you give us the example of input and desired output?

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 4

Hi Melissa, 
I am just doing the code conversion from Orcale to SQL Server and unfortunately dont have access to data itself. AFAIU, the current function returns concatenated string of all the owner names. My current issue is related equivalent of FOR REC IN in t-SQL functions. 
Thanks
Jay    

Answer 5

I'm guessing you want something like this:

DECLARE @OwnerList VARCHAR(MAX)
SELECT @OwnerList=COALESCE(@OwnerList+' AND ' , '')+Owner_Name
FROM Owner_Name

---

--Brad (My Blog)

Answer 6

I'm guessing you want something like this:

DECLARE @OwnerList VARCHAR(MAX)
SELECT @OwnerList=COALESCE(@OwnerList+' AND ' , '')+Owner_Name
FROM Owner_Name

---

--Brad (My Blog)

He just needs to remove the first AND from your code Brad.

---

Abdallah, PMP, MCTS

Answer 7

Thanks Brad, I will give that a try. It seems like this is what I was looking for.  
Thanks again
Jay

Answer 8

Since @OwnerList starts as a NULL value (because all variables are NULL when they are DECLAREd), the COALESCE() logic I put in there will essentially eliminate the first 'AND'.

Try it!

---

--Brad (My Blog)

Answer 9

is there a way to unpropose an answer
by mistake I proposed Abdallah's answer

Answer 10

is there a way to unpropose an answer
by mistake I proposed Abdallah's answer

I took care of it.

---

--Brad (My Blog)

Answer 11

is there a way to unpropose an answer
by mistake I proposed Abdallah's answer

I took care of it.

---

--Brad (My Blog)

thanks Brad.

But there should be a way for us to unpropose the answer as well

Answer 12

is there a way to unpropose an answer
by mistake I proposed Abdallah's answer

I took care of it.

---

--Brad (My Blog)

thanks Brad.

But there should be a way for us to unpropose the answer as well

I suppose I agree with you... but if you don't have an "unpropose" option, I guess it's something that only moderators are allowed to do.

---

--Brad (My Blog)

Answer 13

I suppose I agree with you... but if you don't have an "unpropose" option, I guess it's something that only moderators are allowed to do.

---

--Brad (My Blog)

Moderators and answerers.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 14

is there a way to unpropose an answer
by mistake I proposed Abdallah's answer

I took care of it.

---

--Brad (My Blog)

thanks Brad.

But there should be a way for us to unpropose the answer as well

I suppose I agree with you... but if you don't have an "unpropose" option, I guess it's something that only moderators are allowed to do.

---

--Brad (My Blog)

if you propose an answer then there should be an option to undo

Answer 15

Hi there,
Finally, I got to the point of testing this function. Somehow it always returns NULL value. I have tried serveral test cases but it still have the the same results. I was curious if there is anything missing. 
Thanks
Jay  
 
ALTER FUNCTION [dbo].[ufrGedConcatOwnerName]
(@titleReferenceNumber varchar, @tenancyGroup varchar)
RETURNS varchar
AS
BEGIN
  DECLARE @ownerName VARCHAR(MAX);
  SELECT @ownerName=COALESCE(@ownerName+' AND ' , '')+onm.OWNERNAME
  FROM  ALTA_DEVL.dbo.OWNER_NAME onm, ALTA_DEVL.dbo.OWNER [owner]
  WHERE owner.TITLEREFERENCENUMBER = @titleReferenceNumber AND
                  owner.TENANCYGROUP = @tenancyGroup AND
                  owner.TITLEREFERENCENUMBER = onm.TITLEREFERENCENUMBER AND
                  owner.SEQUENCENUMBER = onm.OWNERSEQUENCENUMBER
  RETURN @ownerName;
END;

Answer 16

The function is pretty straight forward. Try running the following code and see if you get any results. And if you do, see if there are any fields in the the following fields owner.TITLEREFERENCENUMBER,owner.TENANCYGROUP , that match the values you are using as parameters in the function.

SELECT onm.OWNERNAME,owner.TITLEREFERENCENUMBER,owner.TENANCYGROUP
  FROM  ALTA_DEVL.dbo.OWNER_NAME onm, ALTA_DEVL.dbo.OWNER [owner]
  WHERE      owner.TITLEREFERENCENUMBER = onm.TITLEREFERENCENUMBER AND
                  owner.SEQUENCENUMBER = onm.OWNERSEQUENCENUMBER

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 17

Hi,
Thanks for your response. I checked the query and it does return values and there are cases it should return the concatenated values. Here is how I am testing this fucntion and it always return NULL. 
Thanks
Jay 

SELECT

 

<Schema>.dbo.ufrGedConcatOwnerName('001004918', '001')

Answer 18

Try applying coalesce for both

SELECT @ownerName=COALESCE(@ownerName+ COALESCE(' AND '+  onm.OWNERNAME)
, '')   FROM  ALTA_DEVL.dbo.OWNER_NAME onm, ALTA_DEVL.dbo.OWNER [owner]

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 19

I don't have access to a SQL Server machine right now to test. You don't need to include the schema. Also, if your result returns more than one value, you might need to use an inline table-valued function where it retuns a table instead of one result as a VARCHAR. Check out the following link.

http://msdn.microsoft.com/en-us/library/ms186755.aspx

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 20

Hi Naomi,
 
I tried the query but SQL server was prompting for mismatching ). I made some changes and the function compiles but doesn't return any values. I am not sure if my changes have problem. Below is the updated version. 
Appreciate any feedback.  
Thanks
Jay

SELECT

 

@ownerName=COALESCE(@ownerName+ COALESCE((' AND '+ onm.OWNERNAME), ''), '')

Answer 21

Hi Abdallah,
The function needs to return a single value, which is the concatenated OWNERNAME. This is why I had this scaler valued function. 
Thanks
Jay  

Answer 22

Right now there are too many ()

Basically, we may try instead:

select coalesce(@OwnerName + ' AND ','') + coaleasce(onm.OwnerName,'') as MyOwner ...

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 23

Hi Naomi,
I tried this but still no luck, still getting null value as output. I just wanted to bring this up that the values that I am trying to cocatenate are in different rows that are matching criteria.  I am thinking, my be I need to loop to this this. In PL.SQL code, this is what has been used: 

    FOR REC IN
     (
       SELECT OWNER_NAME.OWNER_NAME
                     FROM OWNER_NAME
     )
     LOOP  
         ownerName := ownerName || ' AND ' || REC.OWNER_NAME;
     END LOOP;

SELECT part seems fine but the not sure about looping.
 
select coalesce(@OwnerName + ' AND ','') + coaleasce(onm.OwnerName,'') as

Thanks
Jay

Answer 24

No, that's the whole idea of UDF - it performs concatenation for you. This was a solution to concatenate rows into one record (produce CSV) in the SQL Server 2000 (pre XML path solution).

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog