This website uses cookies for visitor traffic analysis. By using the website, you agree with storing the cookies on your computer.More information

Fehler beim Select mit PLSQL-Aggregate Function MIN

Allgemeine Fragen zur Entwicklung von und mit JVx.

Fehler beim Select mit PLSQL-Aggregate Function MIN

Postby hansemar » Fri Oct 12, 2018 12:05 pm

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?
hansemar
 
Posts: 15
Joined: Tue Jun 26, 2018 10:46 am

Re: Fehler beim Select mit PLSQL-Aggregate Function MIN

Postby Support@SIB » Sun Oct 14, 2018 8:25 am

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.
User avatar
Support@SIB
 
Posts: 257
Joined: Mon Sep 28, 2009 1:56 pm

Re: Fehler beim Select mit PLSQL-Aggregate Function MIN

Postby hansemar » Mon Oct 15, 2018 9:41 am

Hallo,

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

Gruß Mark
hansemar
 
Posts: 15
Joined: Tue Jun 26, 2018 10:46 am


Return to Development (DE)