Solved

Get  equivalent Sql Server Query for given Oracle Query

Posted on 2014-07-30
3
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 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