SQL in routine
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
SQL in routine
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.
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.
------------
Elias
Elias
Routines return a dynamic array and what you're seeing a Field Mark. Use convert() to strip it:
Code: Select all
Convert(@FM,"",YourField)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
Here is my routine code.
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.
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.
------------
Elias
Elias
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
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]
------------
Elias
Elias
-
- Participant
- Posts: 251
- Joined: Mon Jun 09, 2008 5:52 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Or even or
Code: Select all
Ans = Output<1>
Code: Select all
Ans = Convert(@FM,"",Output)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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'.
![Question :?:](./images/smilies/icon_question.gif)
And... FYI, that is the proper Resolution to your issue, not any kind of a 'workaround'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers