locked
Oracle to SQl Server Function RRS feed

  • 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
    Friday, October 23, 2009 4:39 PM

Answers

  • 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)
    • Proposed as answer by Arif Hasan Friday, October 23, 2009 5:54 PM
    • Marked as answer by Kalman Toth Wednesday, October 28, 2009 2:09 PM
    Friday, October 23, 2009 5:12 PM

  • 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
    • Marked as answer by Kalman Toth Sunday, January 15, 2012 12:35 AM
    Saturday, October 24, 2009 6:13 AM
  • 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
    • Marked as answer by Kalman Toth Sunday, January 15, 2012 12:34 AM
    Wednesday, December 30, 2009 6:10 PM

All replies

  • What does this function do?


    Abdallah, PMP, MCTS
    Friday, October 23, 2009 4:44 PM
  • 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;
     

    Friday, October 23, 2009 4:59 PM
  • 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.

    Friday, October 23, 2009 5:02 PM
  • 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    
    Friday, October 23, 2009 5:12 PM
  • 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)
    • Proposed as answer by Arif Hasan Friday, October 23, 2009 5:54 PM
    • Marked as answer by Kalman Toth Wednesday, October 28, 2009 2:09 PM
    Friday, October 23, 2009 5:12 PM
  • 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
    • Proposed as answer by Arif Hasan Friday, October 23, 2009 5:54 PM
    • Unproposed as answer by Brad_Schulz Friday, October 23, 2009 6:04 PM
    Friday, October 23, 2009 5:17 PM
  • Thanks Brad, I will give that a try. It seems like this is what I was looking for.  
    Thanks again
    Jay
    Friday, October 23, 2009 5:22 PM
  • 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)
    Friday, October 23, 2009 5:32 PM
  • is there a way to unpropose an answer
    by mistake I proposed Abdallah's answer
    Friday, October 23, 2009 5:56 PM
  • is there a way to unpropose an answer
    by mistake I proposed Abdallah's answer

    I took care of it.
    --Brad (My Blog)
    Friday, October 23, 2009 6:04 PM
  • 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
    Friday, October 23, 2009 6:24 PM
  • 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)
    Friday, October 23, 2009 6:49 PM

  • 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
    • Marked as answer by Kalman Toth Sunday, January 15, 2012 12:35 AM
    Saturday, October 24, 2009 6:13 AM
  • 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
    Saturday, October 24, 2009 6:22 AM
  • 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;

    Wednesday, December 23, 2009 6:24 PM
  • 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
    Thursday, December 24, 2009 3:55 AM
  • 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')

    Tuesday, December 29, 2009 5:29 PM
  • 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
    Tuesday, December 29, 2009 5:38 PM
  • 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
    Tuesday, December 29, 2009 5:38 PM
  • 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), ''), '')

    Tuesday, December 29, 2009 6:16 PM
  • 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  
    Tuesday, December 29, 2009 6:25 PM
  • 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
    Tuesday, December 29, 2009 11:19 PM
  • 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
    Wednesday, December 30, 2009 5:57 PM
  • 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
    • Marked as answer by Kalman Toth Sunday, January 15, 2012 12:34 AM
    Wednesday, December 30, 2009 6:10 PM