Solved

Get  equivalent Sql Server Query for given Oracle Query

Posted on 2014-07-30
3
308 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Queries 15 34
IIS Server infected with Ransomware - Postmortem investigation 12 133
PL/SQL Display based on value 4 17
Help Parsing a String with SQL Syntax 23 25
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now