Forum @ SIB Visions

Simplify IT business | SIB Visions
http://forum.sibvisions.com/

Fehler beim Select mit PLSQL-Aggregate Function MIN

http://forum.sibvisions.com/viewtopic.php?f=2&t=3451

Page 1 of 1

Fehler beim Select mit PLSQL-Aggregate Function MIN

PostPosted: Fri Oct 12, 2018 12:05 pm
by 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?

Re: Fehler beim Select mit PLSQL-Aggregate Function MIN

PostPosted: Sun Oct 14, 2018 8:25 am
by 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.

Re: Fehler beim Select mit PLSQL-Aggregate Function MIN

PostPosted: Mon Oct 15, 2018 9:41 am
by hansemar
Hallo,

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

Gruß Mark
All times are UTC + 1 hour [ DST ]
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/