Composite Studio - return row number column

[tomhunter-la]

Hi, I’m trying to include a row number column in my result set in Composite Studio (SQL Scratchpad), however I’m getting various error messages such as the below.  Is there any way to add a row number?

An internal error has occurred.
Cause: analytical function ROW_NUMBER is not supported natively.

com.compositesw.cdms.webapi.WebapiException: An internal error has occurred.
Cause: analytical function ROW_NUMBER is not supported natively.
 analytical function ROW_NUMBER is not supported natively.
 at com.compositesw.cdms.webapi.PrefetchCursor$LoadPipeThread.run(PrefetchCursor.java:273)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:662)
Caused by: analytical function ROW_NUMBER is not supported natively.
 at com.compositesw.server.qe.QueryResult.next(SourceFile:311)
 at com.compositesw.cdms.webapi.PrefetchCursor$LoadPipeThread.run(PrefetchCursor.java:253)
 ... 3 more
Caused by: java.lang.IllegalArgumentException: analytical function ROW_NUMBER is not supported natively.
 at com.compositesw.server.qe.physical.util.FunctionExecutor.initialize(SourceFile:232)
 at com.compositesw.server.qe.physical.util.FunctionExecutor.next(SourceFile:379)
 at com.compositesw.server.qe.physical.operator.FunctionNode.next(SourceFile:197)
 at com.compositesw.server.qe.topology.SelectPTree.nextInternal(SourceFile:399)
 at com.compositesw.server.qe.topology.SelectPTree.next(SourceFile:378)
 at com.compositesw.server.qe.QueryResult.next(SourceFile:295)
 ... 4 more

 at com.compositesw.cdms.webapi.WebapiException_SOAPSerializer.doDeserialize(WebapiException_SOAPSerializer.java:58)
 at com.sun.xml.rpc.encoding.ObjectSerializerBase.deserialize(ObjectSerializerBase.java:209)
 at com.sun.xml.rpc.encoding.ReferenceableSerializerImpl.deserialize(ReferenceableSerializerImpl.java:172)
 at com.compositesw.cdms.webapi.service.WMetaDataSvc_dataset_fetch_Fault_SOAPSerializer.deserializeDetail(WMetaDataSvc_dataset_fetch_Fault_SOAPSerializer.java:54)
 at com.sun.xml.rpc.encoding.SOAPFaultInfoSerializer.doDeserialize(SOAPFaultInfoSerializer.java:151)
 at com.sun.xml.rpc.encoding.ObjectSerializerBase.deserialize(ObjectSerializerBase.java:209)
 at com.sun.xml.rpc.encoding.ReferenceableSerializerImpl.deserialize(ReferenceableSerializerImpl.java:172)
 at com.compositesw.cdms.webapi.service.WMetaDataSvc_Stub._readBodyFaultElement(WMetaDataSvc_Stub.java:23530)
 at com.sun.xml.rpc.client.StreamingSender._send(StreamingSender.java:238)
 at com.compositesw.cdms.webapi.service.WMetaDataSvc_Stub.dataset_fetch(WMetaDataSvc_Stub.java:14247)
 at com.compositesw.cdms.webapi.WDataset$FetcherThread.run(WDataset.java:273)

Version Information: Studio 6.2.5.00.32 (hotfix), Build ID 131217-101258-0800-72550, Server 6.2.5.00.32 (hotfix)

[brshao]

Hi Tom,

I think you intended your question for the other DV community here:  https://supportforums.cisco.com/community/12298251/cisco-data-virtualization

Although our parser allows the syntax of these analytic functions such as ROW_NUMBER to be submitted to us from a client, our query engine is not capable of processing it locally.

It is up to the user to ensure the resulting query plan will push the sql containing that function down to the external db (ie; oracle) to be processed there instead.  Creating the query from inside a view will provide the ability to see the resulting query plan.

Which customer (company) are you with?  If you need further assistance, I recommend you log a support case with Cisco.

Thank you,

Brad

[tomhunter-la]

Hi Brad, if I try to access the URL you provided I get an Access Denied page..  Any reason why this might be?

Thanks,

Tom

[brshao]

Tom,

It might be a membership issue, I'll ask the person here who operates the DV Community and let you know later today.

Brad