'Set DEFINE OFF' with Data Services

Hi,

We are using a SQL transform to fetch data from one of the databases.
In the Where clause, we have a condition, where one of the values has ‘&’ in it.

When run, it asks for a value which need the input (since & is a default DEFINE character).

One of the solutions is to ‘set define off’ from BODS, so that SQL doesn’t treat ‘&’ as an asking character.

Does anyone know how do we achieve it?

Thanks,
Bhupendra


ds41user :india: (BOB member since 2007-09-03)

Why would you not just escape the &?

To expand slightly, SET DEFINE OFF is a SQL*Plus command. I don’t know of any easy way to get those to work inside DS. There are a variety of other solutions to this problem, though. You can use concats to single out the ampersands, escape them, use an ASCII value, etc.

  • E

eepjr24 :us: (BOB member since 2005-09-16)

OK.

The reason why I am asking this is because ‘&’ is a part of one of the values in the IN statement in the WHERE clause of the query.

for e.g. we have the query whose WHERE clause reads as
STRING in (‘ABC’,‘XYZ’,‘P&Q’,‘DEF’).

When the DEFINE variable is ON, and we don’t give a value, or give a dummy value, ‘P&Q’ data isn’t being retrieved by the query.

The actual statement can’t be posted in this forum because of confidentiality issues.
Our WHERE clause is a bit complex, and had to be used in a SQL transform.


ds41user :india: (BOB member since 2007-09-03)

Do this then:


STRING in ('ABC','XYZ','P&'||'Q','DEF')
  • E

eepjr24 :us: (BOB member since 2005-09-16)

Thanks. That works.

However, I am more interested to know how to enable/disable Oracle session variables, as we may need them from BODS.

If anyone has any ideas?


ds41user :india: (BOB member since 2007-09-03)

Well, if you can handle it being set for the entire datastore…

http://wiki.scn.sap.com/wiki/display/EIM/Database+Session+parameters

  • E

eepjr24 :us: (BOB member since 2005-09-16)

DEFINE is an Oracle SQL Plus feature. You shouldn’t need to mess with that when executing a SQL statement using the DS sql() function.


eganjp :us: (BOB member since 2007-09-12)