SAS CIS: pass a calculated item containing input function to Oracle

[mmmaria]

Hello everyone! 

Sorry if I picked the wrong component for this, but my question has to do with SAS CIS as well as SAS programming and Oracle interaction. 

I have a calculated item in SAS CIS which contains the input function converting a string type DB table field into a number. It goes something like this: 

input (<table>.field_1, 20.0)

Because of this, export nodes containing this calculated item as one of their export fields can't pass the resulting query to Oracle, the query is processed in WORK and it ends up too heavy for our resources. 

Does anyone know of a solution I can use inside a calculated item in order to convert a string value into a number that can be passed to Oracle 12c? As far as I understand, you can't use purely Oracle functions (to_number etc) in calculated items, can't pass purely SAS functions to Oracle and I haven't been able to find a solution that works in both environments. 

[JamesAnderson]

Hi @mmmaria ,

I would have expected the calculated function not to matter on the Export as it is part of the SELECT clause. A couple of questions to understand your use case a bit more:

1) Are you using this calculated item in Refine Output ? (this would also result in it being in the WHERE clause)

2) What is your target output type (delimited, SAS data set, database table) ?

Thanks

James

[mmmaria]

Hi James! 

1. As far as I know, no. It's only used in the SELECT clause, but the subject is a DB table field so I'm imagining it's loading the entire table into WORK in order to process it? 

2. Target output type is a DB table

[JamesAnderson]

HI @mmmaria  - I don't expect it needs to load the whole table, only the records that are in the export. If you can share the export query generated in the logfile we could take a look, or perhaps speak with SAS Tech Support for a deep dive into what is happening.

As a test you have you tried exporting to a table that has that column as a character type and don use the Calculated Item - just to be sure that its the calculated item causing the problem ?

To answer you're original question, with CI Studio there is no way to use Oracle Functions directly. You could perhaps create a view in the database that does this conversion, and then use that view in CI Studio.

Regards
James