Closed BernhardFW closed 6 years ago
The data source for the report can be any query you like - e.g. to bring in more than one table, you would UNION ALL the results of each into one query.
Thanks Jeff, cool, got it running ! nice work !!
Hi Jeff,
sorry to bother you again... pls send me your paypal account to send you a beer by paypal credits....
thanks and best regds Bernhard
With the circle filter I hv a "challenge" having substituted your collection with my table:
WITH prm AS ( select :P2_CENTER AS param_latlng ,CASE WHEN INSTR(:P2_CENTER,',')>0 THEN TO_NUMBER(SUBSTR(:P2_CENTER,1,INSTR(:P2_CENTER,',')-1)) END AS lat ,CASE WHEN INSTR(:P2_CENTER,',')>0 THEN TO_NUMBER(SUBSTR(:P2_CENTER,INSTR(:P2_CENTER,',')+1)) END AS lng ,TO_NUMBER(:P2_DISTANCE) AS param_dist from dual ) ,prm1 AS ( select prm.param_latlng, prm.param_dist ,CASE WHEN param_latlng IS NOT NULL THEN MDSYS.SDO_GEOMETRY (sdo_gtype => 2001 / 2-dimensional point / ,sdo_srid => 8307 / Longitude / Latitude (WGS 84) / ,sdo_point => MDSYS.SDO_POINT_TYPE(prm.lng, prm.lat, NULL) ,sdo_elem_info => NULL ,sdo_ordinates => NULL) END as ctr from prm ) ,qry1 AS ( -- select c003 as lat, c004 as lng, c002 as name, c001 as id, c002 || ' (id=' || c001 || ')' as info select latv, lngv, name, id, name as info ------------------------- --> types are alle varchar2 despite id is number ,CASE WHEN :P2_CENTER IS NOT NULL THEN MDSYS.SDO_GEOMETRY ---------------------------------> Here it throws an error stating "number of arguments or types are wrong for MDSYS.SDO_GEOMETRY" - any idea ? (sdo_gtype => 2001 / 2-dimensional point / ,sdo_srid => 8307 / Longitude / Latitude (WGS 84) / -- ,sdo_point => MDSYS.SDO_POINT_TYPE(TO_NUMBER(c004), TO_NUMBER(c003), NULL) ,sdo_point => MDSYS.SDO_POINT_TYPE(TO_NUMBER(lngv), TO_NUMBER(latv), NULL) ,sdo_elem_info => NULL ,sdo_ordinates => NULL) END AS geo -- from apex_collections -- where collection_name = 'MAP' from AK_KUNDEN_BAK_19052017 where LATV IS NOT NULL ),
qry2 as ( -- select qry1.lat, qry1.lng, qry1.name, qry1.id, qry1.info, prm1.param_latlng, prm1.param_dist select qry1.latv, qry1.lngv, qry1.name, qry1.id, prm1.param_latlng, prm1.param_dist
,CASE WHEN param_latlng IS NOT NULL THEN MDSYS.SDO_GEOM.sdo_distance (geom1 => prm1.ctr ,geom2 => qry1.geo ,tol => 0.0001 /metres/ ,unit => 'unit=KM') END AS dist from qry1, prm1 ) select latv||','||lngv AS latlng, name, dist from qry2 where (param_latlng IS NULL OR param_dist IS NULL OR dist < param_dist)
Bernhard Fischer-Wasels - Neulander Weg 25 - D-21357 Wittorf - t.04133-210888 - m.0162 193 79 75 - bfw-design.de
Am 18.05.2018 um 01:25 schrieb Jeffrey Kemp:
Closed #48 https://github.com/jeffreykemp/jk64-plugin-reportmap/issues/48.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/jeffreykemp/jk64-plugin-reportmap/issues/48#event-1633165439, or mute the thread https://github.com/notifications/unsubscribe-auth/Aa_NEHZghVmGdmqJa9rwf4NoMHLsyhFOks5tzgbVgaJpZM4UDBq4.
Sorry, I don't know what might be causing that error. I can't see any syntax issues with your code, unless the comments are interfering somehow.
Without a reproducible test case it'd be hard for me to help further. Can you reproduce the problem on apex.oracle.com?
Hi Jeff, how can I achieve the display of a 2nd table (or 3rd) as an additional layer ? How wud that work? Thanks from Lüneburg/Germany Bernhard