SData - custom sql view with Group By

Hello,

We have a custom SQL view for opportunities. The view works fine when ran in SQL or when used to create a Report in CRM (7.3), but if I try to access it via Sdata:

myserver/.../vforecastmatrixapi

It throws an error. The sql view has a few "group by" clauses at the end, which i suspect is causing sdata to freak out?

Also, I can't access the sdata $schema either. Other regular out of the box views work fine via sdata.

Here is the error:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sdata:diagnosis xmlns:sdata="">schemas.sage.com/.../1">
<sdata:applicationCode> </sdata:applicationCode>
<sdata:payloadPath> </sdata:payloadPath>
<sdata:severity>Fatal</sdata:severity>
<sdata:stackTrace>java.lang.NullPointerException&#xD;
at java.lang.String.concat(Unknown Source)&#xD;
at com.sage.crm.sdata.classes.SDataUrlBuilder.getSingleResourceUrl(SDataUrlBuilder.java:122)&#xD;
at com.sage.crm.sdata.classes.SDataUrlBuilder.getSingleResourceUrl(SDataUrlBuilder.java:117)&#xD;
at com.sage.crm.sdata.endpoint.SDataEndpoint.getResourceLinks(SDataEndpoint.java:592)&#xD;
at com.sage.crm.sdata.endpoint.SDataEndpoint.processResourceEntry(SDataEndpoint.java:352)&#xD;
at com.sage.crm.sdata.endpoint.SDataEndpoint.processResourceFeed(SDataEndpoint.java:223)&#xD;
at com.sage.crm.sdata.endpoint.SDataEndpoint.handleMessage(SDataEndpoint.java:140)&#xD;
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)&#xD;
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)&#xD;
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)&#xD;
at java.lang.reflect.Method.invoke(Unknown Source)&#xD;
at org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:112)&#xD;
at org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:102)&#xD;
at org.springframework.expression.spel.ast.MethodReference.access$000(MethodReference.java:49)&#xD;
at org.springframework.expression.spel.ast.MethodReference$MethodValueRef.getValue(MethodReference.java:342)&#xD;
at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:88)&#xD;
at org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:131)&#xD;
at org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:330)&#xD;
at org.springframework.integration.util.AbstractExpressionEvaluator.evaluateExpression(AbstractExpressionEvaluator.java:164)&#xD;
at org.springframework.integration.util.MessagingMethodInvokerHelper.processInternal(MessagingMethodInvokerHelper.java:276)&#xD;
at org.springframework.integration.util.MessagingMethodInvokerHelper.process(MessagingMethodInvokerHelper.java:142)&#xD;
at org.springframework.integration.handler.MethodInvokingMessageProcessor.processMessage(MethodInvokingMessageProcessor.java:75)&#xD;
at org.springframework.integration.handler.ServiceActivatingHandler.handleRequestMessage(ServiceActivatingHandler.java:71)&#xD;
at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:99)&#xD;
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:78)&#xD;
at org.springframework.integration.dispatcher.AbstractDispatcher.tryOptimizedDispatch(AbstractDispatcher.java:116)&#xD;
at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:101)&#xD;
at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:97)&#xD;
at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:77)&#xD;
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:277)&#xD;
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:239)&#xD;
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:115)&#xD;
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:45)&#xD;
at org.springframework.messaging.core.AbstractMessageSendingTemplate.send(AbstractMessageSendingTemplate.java:95)&#xD;
at org.springframework.integration.router.AbstractMessageRouter.handleMessageInternal(AbstractMessageRouter.java:164)&#xD;
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:78)&#xD;
at org.springframework.integration.dispatcher.AbstractDispatcher.tryOptimizedDispatch(AbstractDispatcher.java:116)&#xD;
at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:101)&#xD;
at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:97)&#xD;
at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:77)&#xD;
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:277)&#xD;
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:239)&#xD;
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:115)&#xD;
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:45)&#xD;
at org.springframework.messaging.core.AbstractMessageSendingTemplate.send(AbstractMessageSendingTemplate.java:95)&#xD;
at org.springframework.integration.handler.AbstractMessageProducingHandler.sendOutput(AbstractMessageProducingHandler.java:248)&#xD;
at org.springframework.integration.handler.AbstractMessageProducingHandler.produceOutput(AbstractMessageProducingHandler.java:171)&#xD;
at org.springframework.integration.handler.AbstractMessageProducingHandler.sendOutputs(AbstractMessageProducingHandler.java:119)&#xD;
at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:105)&#xD;
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:78)&#xD;
at org.springframework.integration.dispatcher.AbstractDispatcher.tryOptimizedDispatch(AbstractDispatcher.java:116)&#xD;
at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:101)&#xD;
at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:97)&#xD;
at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:77)&#xD;
at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:277)&#xD;
at org.springframework.messaging.core.GenericMessagingTemplate.doSend(GenericMessagingTemplate.java:115)&#xD;
at org.springframework.messaging.core.GenericMessagingTemplate.doSendAndReceive(GenericMessagingTemplate.java:150)&#xD;
at org.springframework.messaging.core.GenericMessagingTemplate.doSendAndReceive(GenericMessagingTemplate.java:45)&#xD;
at org.springframework.messaging.core.AbstractMessagingTemplate.sendAndReceive(AbstractMessagingTemplate.java:42)&#xD;
at org.springframework.integration.gateway.MessagingGatewaySupport.doSendAndReceive(MessagingGatewaySupport.java:357)&#xD;
at org.springframework.integration.gateway.MessagingGatewaySupport.sendAndReceiveMessage(MessagingGatewaySupport.java:328)&#xD;
at org.springframework.integration.http.inbound.HttpRequestHandlingEndpointSupport.actualDoHandleRequest(HttpRequestHandlingEndpointSupport.java:492)&#xD;
at org.springframework.integration.http.inbound.HttpRequestHandlingEndpointSupport.doHandleRequest(HttpRequestHandlingEndpointSupport.java:389)&#xD;
at org.springframework.integration.http.inbound.HttpRequestHandlingMessagingGateway.handleRequest(HttpRequestHandlingMessagingGateway.java:103)&#xD;
at org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter.handle(HttpRequestHandlerAdapter.java:51)&#xD;
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)&#xD;
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)&#xD;
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)&#xD;
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)&#xD;
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)&#xD;
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)&#xD;
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#xD;
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#xD;
at com.sage.crm.web.filters.ClearCurrentThreadLocalsFilter.doFilter(ClearCurrentThreadLocalsFilter.java:45)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)&#xD;
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)&#xD;
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at com.sage.crm.core.security.SIDAuthenticationFilter.doFilter(SIDAuthenticationFilter.java:90)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)&#xD;
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)&#xD;
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)&#xD;
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)&#xD;
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)&#xD;
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)&#xD;
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#xD;
at org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:150)&#xD;
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#xD;
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)&#xD;
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)&#xD;
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#xD;
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)&#xD;
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)&#xD;
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)&#xD;
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)&#xD;
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)&#xD;
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)&#xD;
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)&#xD;
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:436)&#xD;
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)&#xD;
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)&#xD;
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2517)&#xD;
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2506)&#xD;
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)&#xD;
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)&#xD;
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)&#xD;
at java.lang.Thread.run(Unknown Source)&#xD;
</sdata:stackTrace>
<sdata:sdataCode> </sdata:sdataCode>
</sdata:diagnosis>

Thank you for any help

  • 0

    here is the SQL view:

    SELECT

    Channel.Chan_Description,

    DATEPART(QUARTER, CASE WHEN Oppo_Stage LIKE 'Forecast%' THEN Oppo_ForecastDate ELSE Oppo_Opened END) AS oppo_Qtr,

    DATEPART(YEAR, CASE WHEN Oppo_Stage LIKE 'Forecast%' THEN Oppo_ForecastDate ELSE Oppo_Opened END) AS oppo_Yr,

    CASE WHEN Channel.Chan_Description LIKE 'CON Sales%' THEN Oppo_EndUser ELSE Company.Comp_Name END As oppo_Corporate,

    CASE WHEN Channel.Chan_Description LIKE 'CON Sales%' THEN Oppo_EndUser ELSE Company.Comp_Name END As oppo_EndUser,

    SUM(CASE WHEN Oppo_Stage LIKE 'Forecast%' THEN Oppo_Forecast ELSE 0 END) AS oppo_Forecast,

    SUM(CASE WHEN Oppo_Stage LIKE 'Plan%' THEN Oppo_Total ELSE 0 END) AS oppo_Plan,

    SUM(CASE WHEN Oppo_Stage LIKE 'Quoted%' THEN Oppo_Total ELSE 0 END) AS oppo_Quoted,

    SUM(CASE WHEN Oppo_Stage LIKE 'Verbal%' THEN Oppo_Total ELSE 0 END) AS oppo_Verbal,

    SUM(CASE WHEN Oppo_Stage LIKE 'Booked%' THEN Oppo_Total ELSE 0 END) AS oppo_Booked,

    COUNT(CASE WHEN Oppo_Stage LIKE 'Booked%' THEN Oppo_Total ELSE 0 END) AS oppo_Count

    FROM Opportunity

    INNER JOIN Channel ON Opportunity.Oppo_ChannelId = Channel.Chan_ChannelId

    INNER JOIN Company ON Opportunity.Oppo_PrimaryCompanyId = Company.Comp_CompanyId

    WHERE Oppo_Deleted IS NULL

    AND oppo_EndUser IS NOT NULL

    GROUP BY CASE WHEN Channel.Chan_Description LIKE 'CON Sales%' THEN Oppo_EndUser ELSE Company.Comp_Name END, Channel.Chan_Description, DATEPART(QUARTER, CASE WHEN Oppo_Stage LIKE 'Forecast%' THEN Oppo_ForecastDate ELSE Oppo_Opened END), DATEPART(YEAR, CASE WHEN Oppo_Stage LIKE 'Forecast%' THEN Oppo_ForecastDate ELSE Oppo_Opened END), oppo_EndUser

  • 0

    Joe

    I am not in a position to test this. But you may want to create a simple view that abstracts this view. e.g. "create view myview as Select * from myotherview".

    And expose the simple view to SData.

  • 0

    Hi Jeff,

    Thanks for the idea. I tried that, but it still through the same error. For testing, I'm striping down the query to try to isolate the problem, and may have found something.

    Using this basic view:

    CREATE VIEW vForecastMatrixApi

    AS

    select

    (select(sum(oppo_total))) as 'testsum'

    from

    Opportunity

    If I try to hit that view via sData, i get the same error. When I check the view schema via the /{viewname}/$schema action, it does return a schema, but in the ComplexType element, it doesn't list the column(s) , in this case I would expect it to show my one column called "testsum".

    " href="http://www.w3.org/2001/XMLSchema"">www.w3.org/.../XMLSchema" xmlns:tns="">schemas.sage.com/scrm" xmlns:sme="">schemas.sage.com/.../2007">

    Thanks for any help!

    Joe

  • 0

    Joe

    NB: I haven't tested this... this is coming straight out of my keyboard and mind. So I could be wrong!

    One of the things that I would expect is that you would get a problem in SData for views that alias columns but do not use a column prefix.

    (select(sum(oppo_total))) as 'testsum'

    should be (in my mind)

    (select(sum(oppo_total))) as 'oppo_testsum'

    Try it with the column prefix and see if it changes!

  • 0

    Jeff,

    No luck, still produces the error i mentioned in original post, and the view $schema contains no elements. I've refreshed metadata and restarted tomcat. I can create views without using SUM just fine. SData seems to not like that function. The view works fine if I just use it to create a Report from within Sage CRM.

    My goal here is to make ajax requests from an external web page, and render the results into reports,charts, etc. I would really like to use sdata, because it's easier to connect to via javascript. (as opposed to making a custom table that references the view, and hit the table via soap web services).

  • 0

    Jeff,

    It seems maybe I am misunderstanding the restrictions of custom views. It looks like the column names must be actual columns in the entity's table. For instance, if I do a calculated column with the SUM function, the result alias must be a column name actually in the opportunity table. I can't just do (select SUM(oppo_total) as "oppo_whatever"). The column alias must be an actual column on the database table. Is this assumption correct?

  • 0

    Thanks Jeff. I'm getting closer. I have the custom captions set up, and the alias fields also show up in custom_edits table now. Now all the columns are returned correctly when I retrieve the $schema on the view via sData. However, I still get the error when trying to get the view results from sdata:

    Fatal

    java.lang.NullPointerException&#xD;

    at java.lang.String.concat(Unknown Source)&#xD;

    at com.sage.crm.sdata.classes.SDataUrlBuilder.getSingleResourceUrl(SDataUrlBuilder.java:122)&#xD;

    at com.sage.crm.sdata.classes.SDataUrlBuilder.getSingleResourceUrl(SDataUrlBuilder.java:117)&#xD;

    at com.sage.crm.sdata.endpoint.SDataEndpoint.getResourceLinks(SDataEndpoint.java:592)&#xD;

    at com.sage.crm.sdata.endpoint.SDataEndpoint.processResourceEntry(SDataEndpoint.java:352)&#xD;

    at com.sage.crm.sdata.endpoint.SDataEndpoint.processResourceFeed(SDataEndpoint.java:223)&#xD;

    at com.sage.crm.sdata.endpoint.SDataEndpoint.handleMessage(SDataEndpoint.java:140)&#xD;

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)&#xD;

    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)&#xD;

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)&#xD;

    at java.lang.reflect.Method.invoke(Unknown Source)&#xD;

  • 0

    Jeff,

    Just thought of something. The error states "getSingleResourceUrl". This view is NOT single resource capable, if I understand what that means correctly. My custom view just returns sums and counts, so it's not possible to do this: /vCustomView('123') 123 being the entity id. Am I missing a setting somewhere that lets sData know that this view is not single resource capable?

  • 0

    Jeff,

    I don't know if this is the correct way to get around this or not. But I was able to get it to work by adding a dummy column to the view:

    '0' as "Oppo_OpportunityId",

    Is there a way to NOT have to include the entity id in the results? Not a big deal, i can obviously just disregard this column on the client side.