Solved

Get  equivalent Sql Server Query for given Oracle Query

Posted on 2014-07-30
3
309 Views
Last Modified: 2014-07-30
Hi Experts,

    Can  you please help me to get the  Equivalent for the following Oracle function

 
create or replace
Function GetOBJ
(
   ParentID       IN  VARCHAR2,
   DefnID          IN  VARCHAR2,
   Dirctn       IN  VARCHAR2 DEFAULT '12'
)
   RETURN VARCHAR2
IS
   mRetVal    VARCHAR2(200);
BEGIN
  mRetVal := '';
  BEGIN
    IF Dirctn = '12' THEN
      SELECT  obj2.ObjName INTO mRetVal
      FROM    datarel rel, dataobj obj2
      WHERE Rel.uid1 = ParentID
              AND rel.uid2 = obj2.objuid
              AND rel.defuid = DefnID;
             
    Else
      SELECT  obj2.ObjName INTO mRetVal
      FROM    datarel rel, dataobj obj2
      WHERE Rel.uid2 = ParentID
              AND rel.uid1 = obj2.objuid
              AND rel.defuid = DefnID;
    END IF;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      mRetVal := '**>1 Child**';
    WHEN NO_DATA_FOUND THEN
      mRetVal := '';
    WHEN OTHERS THEN
      mRetVal := '!!!ERR!!!';
  END;
  RETURN mRetVal;
END GetObj;
0
Comment
Question by:Sam OZ
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 40228789
ALTER Function dbo.GetOBJ
 (
    @ParentID VARCHAR(30),
    @DefnID   VARCHAR(30),
    @Dirctn   VARCHAR(30) = '12'
 )
RETURNS VARCHAR(100)
AS 
BEGIN 
	Declare @mRetVal    VARCHAR(200) 
	SET @mRetVal = '' 
	
	IF @Dirctn = '12'  
		SELECT  @mRetVal = obj2.ObjName 
		FROM    datarel rel, dataobj obj2 
		WHERE Rel.uid1 = ParentID 
		AND rel.uid2 = obj2.objuid 
		AND rel.defuid = DefnID 
	Else 
		SELECT  @mRetVal = obj2.ObjName  
		FROM    datarel rel, dataobj obj2 
		WHERE Rel.uid2 = ParentID 
		AND rel.uid1 = obj2.objuid 
		AND rel.defuid = DefnID 

	RETURN @mRetVal 
END 

Open in new window

0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228794
Few points about above function:
1. There is no CREATE OR REPLACE option in SQL Server. You need to use CREATE first time, and ALTER thereafter.
2. SQL Server does not allow TRY... CATCH block inside a function. You need to capture the error outside the function.
3. Above SELECT statement will not throw an error if there are more than one record matching the condition; instead it assign the last fetched value to the variable.

Below is an example that shows how to catch exception in SQL Server:
begin try
	exec ('Select * from from table')
end try
begin catch
	SELECT  ERROR_MESSAGE()
end catch

Open in new window

Above code will show "Incorrect syntax near the keyword 'from'."
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 40228927
CREATE Function dbo.GetOBJ
 (
    @ParentID VARCHAR(50),
    @DefnID   VARCHAR(50),
    @Dirctn   VARCHAR(50) = '12'
 )
RETURNS VARCHAR(200)
AS 
begin 
	declare @MRETVAL    varchar(200) = '',
          @mRetCount  int
	
	if @DIRCTN = '12'  begin
    SELECT  @mRetCount = count(1)
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid1 = @ParentID 
		and REL.UID2 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
    
    if @MRETCOUNT > 1
      set @MRETVAL = '**>1 Child**'
    if @MRETCOUNT = 0
      set @MRETVAL = '!!!ERR!!!'
    
		SELECT  @mRetVal = obj2.ObjName 
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid1 = @ParentID 
		and REL.UID2 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
	END
  else 
  BEGIN
    
    SELECT  @mRetCount = count(1)
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid2 = @ParentID 
		and REL.UID1 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
    
    if @MRETCOUNT > 1
      set @MRETVAL = '**>1 Child**'
    if @MRETCOUNT = 0
      set @MRETVAL = '!!!ERR!!!'

		SELECT  @mRetVal = obj2.ObjName  
		from    DATAREL REL, DATAOBJ OBJ2 
		WHERE Rel.uid2 = @ParentID 
		and REL.UID1 = OBJ2.OBJUID 
		and REL.DEFUID = @DEFNID 
  END

	return @MRETVAL 
END 

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question