Fehler beim Select mit PLSQL-Aggregate Function MIN

[hansemar]

Hallo,

ich bekomme beim öffnen eines DBStorage einen Fehler wenn ich die PLSQL-Funktion Min im select enthalten habe. Diese benötige ich aber um ein Group-By auf die selektierten Daten zu machen.

Hier der Auszug aus dem Code:

Code: Select all

private final static String ART_IMP_EMPF_DETAILS = "(select para1, (select name from orgas where nr=para1) as orga, count(1) as anzahl, min(anlage_datum) from art_imp_empf\r\n" +
         "where fk_status='A1' group by para1 order by count(1) desc)";
   
   /**
    * Gets the ART_IMP_EMPF database storage.
    *
    * @throws Exception if the DBStorage couldn't initialized.
    * @return the artImpEmpf2 DBStorage.
    */
   public DBStorage getArtImpEmpfDetails() throws Exception
   {
      DBStorage dbsArtImpEmpf2 = (DBStorage)get("rdbArtImpEmpfDetails");
      if (dbsArtImpEmpf2 == null)
      {
         dbsArtImpEmpf2 = new DBStorage();
         dbsArtImpEmpf2.setWritebackTable(ART_IMP_EMPF_DETAILS);
         dbsArtImpEmpf2.setDBAccess(getDBAccessBs());
         dbsArtImpEmpf2.open();

         put("rdbArtImpEmpfDetails", dbsArtImpEmpf2);
      }
      return dbsArtImpEmpf2;
         }


und hier die Fehlermeldung dazu:

Code: Select all

javax.rad.persist.DataSourceException: Meta data couldn't load from database! - SELECT m.PARA1,
       m.ORGA,
       m.ANZAHL,
       m.MIN(ANLAGE_DATUM)
  FROM (select para1, (select name from orgas where nr=para1) as orga, count(1) as anzahl, min(anlage_datum) from art_imp_empf
where fk_status='A1' group by para1 order by count(1) desc) m
 WHERE 1=2
   at com.sibvisions.rad.persist.jdbc.DBAccess.getAndStoreMetaDataIntern(DBAccess.java:5264)
   at com.sibvisions.rad.persist.jdbc.DBAccess.getColumnMetaDataIntern(DBAccess.java:5295)
   at com.sibvisions.rad.persist.jdbc.DBAccess.getColumnMetaData(DBAccess.java:5548)
   at com.sibvisions.rad.persist.jdbc.DBStorage.createMetaData(DBStorage.java:1998)
   at com.sibvisions.rad.persist.jdbc.DBStorage.openInternal(DBStorage.java:825)
   at com.sibvisions.rad.persist.jdbc.DBStorage.open(DBStorage.java:803)
   at de.libri.apps.l2kx.screens.DashboardArtikelEmpfaengersaetze.getArtImpEmpfDetails(DashboardArtikelEmpfaengersaetze.java:28)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at com.sibvisions.rad.server.DefaultObjectProvider.invokeMethod(DefaultObjectProvider.java:1271)
   at com.sibvisions.rad.server.DefaultObjectProvider.getObject(DefaultObjectProvider.java:260)
   at com.sibvisions.rad.server.DefaultObjectProvider.invoke(DefaultObjectProvider.java:401)
   at de.libri.apps.l2kx.screens.DashboardArtikelEmpfaengersaetze.<artImpEmpfDetails>.getMetaData(Unknown Source)
   at com.sibvisions.rad.server.AbstractSession.executeWithSessionContext(AbstractSession.java:767)
   at com.sibvisions.rad.server.AbstractSession.executeIntern(AbstractSession.java:719)
   at com.sibvisions.rad.server.AbstractSession.execute(AbstractSession.java:687)
   at com.sibvisions.rad.server.Server.executeIntern(Server.java:2343)
   at com.sibvisions.rad.server.Server.execute(Server.java:648)
   at com.sibvisions.rad.server.DirectServerConnection.call(DirectServerConnection.java:305)
   at javax.rad.remote.AbstractConnection.call(AbstractConnection.java:612)
   at com.sibvisions.rad.model.remote.RemoteDataBook.initServerMetaData(RemoteDataBook.java:905)
   at com.sibvisions.rad.model.remote.RemoteDataBook.open(RemoteDataBook.java:224)
   at de.libri.apps.l2kx.screens.DashboardArtikelEmpfaengersaetzeWorkScreen.initializeModel(DashboardArtikelEmpfaengersaetzeWorkScreen.java:120)
   at de.libri.apps.l2kx.screens.DashboardArtikelEmpfaengersaetzeWorkScreen.<init>(DashboardArtikelEmpfaengersaetzeWorkScreen.java:97)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
   at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
   at com.sibvisions.apps.projx.ProjX.loadWorkScreen(ProjX.java:4387)
   at com.sibvisions.apps.projx.ProjX.loadWorkScreen(ProjX.java:1167)
   at com.sibvisions.apps.projx.ProjX.openWorkScreen(ProjX.java:2019)
   at de.libri.apps.l2kx.LibriERPApplication.openWorkScreen(LibriERPApplication.java:134)
   at com.sibvisions.apps.projx.ProjX.openWorkScreen(ProjX.java:750)
   at javax.rad.application.genui.RemoteWorkScreenApplication.openWorkScreen(RemoteWorkScreenApplication.java:82)
   at com.sibvisions.apps.projx.ProjX.doOpenWorkScreen(ProjX.java:6440)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at javax.rad.util.EventHandler$ListenerHandler.dispatchEvent(EventHandler.java:1029)
   at javax.rad.util.EventHandler.dispatchEvent(EventHandler.java:606)
   at javax.rad.util.RuntimeEventHandler.dispatchEvent(RuntimeEventHandler.java:73)
   at com.sibvisions.rad.ui.swing.impl.component.SwingAbstractButton.actionPerformed(SwingAbstractButton.java:396)
   at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
   at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
   at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
   at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
   at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
   at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:833)
   at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:877)
   at java.awt.Component.processMouseEvent(Component.java:6533)
   at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
   at java.awt.Component.processEvent(Component.java:6298)
   at java.awt.Container.processEvent(Container.java:2236)
   at java.awt.Component.dispatchEventImpl(Component.java:4889)
   at java.awt.Container.dispatchEventImpl(Container.java:2294)
   at java.awt.Component.dispatchEvent(Component.java:4711)
   at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
   at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
   at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
   at java.awt.Container.dispatchEventImpl(Container.java:2280)
   at java.awt.Window.dispatchEventImpl(Window.java:2746)
   at java.awt.Component.dispatchEvent(Component.java:4711)
   at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
   at java.awt.EventQueue.access$500(EventQueue.java:97)
   at java.awt.EventQueue$3.run(EventQueue.java:709)
   at java.awt.EventQueue$3.run(EventQueue.java:703)
   at java.security.AccessController.doPrivileged(Native Method)
   at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
   at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
   at java.awt.EventQueue$4.run(EventQueue.java:731)
   at java.awt.EventQueue$4.run(EventQueue.java:729)
   at java.security.AccessController.doPrivileged(Native Method)
   at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
   at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
   at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
   at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
   at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
   at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
   at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
   at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)


Ist es prinzipiell nicht möglich diese Funktion im Selekt zu verwenden? Kann das irgendwie anders gelöst werden?

[Support@SIB]

Vergeben Sie mal einen Alias für die min(anlage_datum) Spalte. Damit sollte es dann funktionieren. Das Problem ist die Ermittlung des Spaltennamens.

Sie können natürlich alle Features der Datenbank verwenden. Es wäre im konkreten Fall auch möglich, die Query Columns manuell zu setzen, dann klappt die Abfrage auch.

[hansemar]

Hallo,

danke für die Hilfe. Das vergeben eines Aliases war die Lösung für das Problem.

Gruß Mark