SQL in routine

[elias.shaik]

Hi all,

I have a routine which will execute a SQL statement.

Lets say SQL statement returns value 'N'.

But when i check the result,i am seeing 'N' and a new line character.

How do i eliminate those new line characters.

Appreciate your help.

Thanks,
Elias.

[chulett]

Routines return a dynamic array and what you're seeing a Field Mark. Use convert() to strip it:

Code: Select all

Convert(@FM,"",YourField)

[elias.shaik]

Here is my routine code.

Code: Select all

 
 SQLSTMT = 'sqlplus<<endofsql -S ':OraUsrId:'@OraInstance/OraPwd':Char(10):'set head off pages 0;':Char(10):'SELECT COL1 FROM table':Char(10):'exit;':Char(10):'endofsql'


 


      Call DSExecute('UNIX',SQLSTMT,Output,SystemRetValue)
 


     Call DSLogInfo("Output is ":Output: "Retcode is ":SystemRetValue, DSJ.ME)
     

      
     
Call DSLogInfo(Output,DSJ.ME)

      Return(Output)

here the return value is N followed by new line .I have tried using convert function for "Output" variable.And its throwing an error.

Am calling this routine using sequencer,and then checking the return value in a nested condition stage for further processing.

[chulett]

"an error"?

You need to convert() after the routine call and where you are using it, typically in either a trigger or parameter derivation... or nested condition stage.

[elias.shaik]

could you please let me know if we could suppress the newline characters in routine itself.

Thanks,
Elias.

[chulett]

No, you cannot. It's an artifact of how the value is returned, something you have no control over, which is why I said it needed to be stripped afterwards.

[elias.shaik]

Code: Select all

SQLSTMT = 'sqlplus<<endofsql -S ':OraUsrId:'@OraInstance/OraPwd':Char(10):'set head off pages 0;':Char(10):'SELECT COL1 FROM table':Char(10):'exit;':Char(10):'endofsql'


Call DSExecute('UNIX',SQLSTMT,Output,SystemRetValue)  

   

 If FMT(Output,"L#1") = 'N' Then
      Output1='N'
 END Else
            Output1='Y'
 End
   


Call DSLogInfo("Output is ":Output1: "Retcode is ":SystemRetValue, DSJ.ME)
     
Return(Output1)

Now am able to get only 'N' this is not a correct solution but a workaround.

Issue resolved.

Thanks for all who helped.[/code]

[srinivas.g]

Ans=Left(Output,Len(Output)-1)

use this statement

[ray.wurlod]

Or even

Code: Select all

Ans = Output<1>

or

Code: Select all

Ans = Convert(@FM,"",Output)

[chulett]

OK, first off - my bad. For some reason I missed the whole "in a routine" part even though it was staring me in the face and was thinking of the Execute Command stage after seeing the sqlplus command. So, as noted, you have to remove them from the DSExecute result but yes you can do it inside the routine before it is passed back out. D'oh.

However, have you literally added a return statement in your routine code? If so I'm surprised that worked as the calling mechanism is looking to automatically pass back the 'answer' hence the mention of setting "Ans" in the code people have posted for you. You set Ans and it "hard codes" the Return(Ans) portion for you when it wraps it. So you really should yank your return and conditionally set Ans to the proper value.

And... FYI, that is the proper Resolution to your issue, not any kind of a 'workaround'.