You are here

Oracle

Oracle spatial SQL remark 1

select z.geom, z.pk, z.igds_level_name as tooltip from dsrlkw_data_ds.dsr_gl_dsrline_tb z
where mdsys.sdo_filter(GEOM,
mdsys.sdo_geometry(2003,25832,NULL,
mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array( 361531.1017459362,5717128.381953186,363000.313064419 ,5717800.65743528)),'querytype=window' ) = 'TRUE'

Blog: 

How to select records with given interval

How to select records with every N records' interval?
Using the rank(), this is the the snipet, for Oracle:
Table name: tableabc,
---------------------
id  |  time  |   test
---------------------
SELECT id, rank() over (partition by 1 order by time) as interval, test
                                   FROM  tableabc 

Oracel SQL, use ROW_NUMBER() to filter same records when JOIN tables

Sometimes we will retrieve repetitive records when we use JOIN to join tables.
Now distinct, group by, and Having will not work, ROW_NUMBER() is a good choice.

for example:

select a.one, b.two from A a
LEFT JOIN B b ON a.id = G.id where where a.name like '%abc%' and a.time < 1234545;

using ROW_NUMBER() after

SELECT ROWNUM, C* FROM(
SELECT a.id, a.one, b.two, b.id,
ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.time DESC) AS ROW_FLG
FROM (select * from A where name like '%abc%' and time < 1234545) a
LEFT JOIN B b ON a.id=b.id
ORDER BY a.time DESC

Blog: 

SQL code snipet, join multi-tables with one column

3 tables(CHART_TEST, CHART_TEST1, CHART_TEST2) need be joined with continious timestamb:

 ID    TIMESTAMP    VALUE
     1    115    456
     4    130    671
     5    190    135
     8    150    934
     9    160    296
     2    120    356
     3    125    556
     6    140    213
     7    145    363
     10    165    358

Blog: 

Oracle and java.sql.SQLException: No suitable driver

Strange problem, just one part of simple code, works on my XP system, but not on Windows2003.

  1. Connection connection=null;
  2. try{
  3. connection = DriverManager.getConnection ("jdbc:odbc:thin@192.168.2.107:1521:Schemaname", "username", "password");
  4. PreparedStatement pstmt = con.prepareStatement ("Select * from sometable");
  5. ResultSet rset = pstmt.executeQuery();
  6. ....................

Very simple SQL code for one servlet, but when i run it in Tomcat, i got this exception:

  1. java.sql.SQLException: No suitable driver found for

Blog: 
Subscribe to RSS - Oracle