Solved

Get  equivalent Sql Server Query for given Oracle Query

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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