Oracle Numbering a dataset or a data sub set

to number a table or a sub set of data use ROW_NUMBER() with the OVER clause eg.

SELECT 
      NAME
    , ZIP_CODE

    , DATE_CHANGED
    , ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DATE_CHANGED) AS ROW_NUM

FROM LOCATIONS
WHERE NAME = ‚test1‘
ORDER BY DATE_CHANGED
;

NAME  ZIP_CODE  DATE_CHANGED  ROW_NUM
test1 5610 01.01.1998 1
test1 5610 01.04.2006 2
test1 5610 01.07.2006 3
test1 5610 31.07.2008 4
test1 5610 30.04.2009 5
test1 5610 31.08.2011 6
test1 8211 30.04.2012 7
test1 6920 31.01.2014 8

Advertisements

Oracle Data-Tree

here is a simple example for the CONNECT BY clause.

tom is the manager of peter and simon
and simon is the manager of greg (see the sub-select)

first all possible trees

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“
    , LEVEL    
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, NULL AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
CONNECT BY PRIOR EMP_ID = MAN_ID
ORDER BY EMP_ID
;

now select only the tree from root (START WITH)

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“    
    , LEVEL
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, NULL AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
START WITH EMP_ID = 1
CONNECT BY PRIOR EMP_ID = MAN_ID
ORDER BY EMP_ID
;

here with a cycle, when tom is his own manager. now the NOCYCLE clause is needed and with CONNECT_BY_ISCYCLE you can find all cycles.

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , LEVEL AS myLEVEL
    , CONNECT_BY_ISCYCLE „Cycle“
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“    
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
CONNECT BY NOCYCLE PRIOR EMP_ID = MAN_ID
ORDER BY LEVEL
;

OGD-data from wiener-linien to mysql

source files are
http://data.wien.gv.at/csv/wienerlinien-ogd-linien.csv
http://data.wien.gv.at/csv/wienerlinien-ogd-haltestellen.csv
http://data.wien.gv.at/csv/wienerlinien-ogd-steige.csv

download all the files with wget  and make a short check…
let’s start with the steige-file, containing all stations/platforms.

make a head wienerlinien-ogd-steige.csv to check the header and the first 9 lines/datasets.

log into mysql database (mysql -u -p) and paste the following sql-code

use test;

create table 
wienerlinien_ogd_steige
(
STEIG_ID INT
, FK_LINIEN_ID INT
, FK_HALTESTELLEN_ID INT
, RICHTUNG VARCHAR(1)
, REIHENFOLGE SMALLINT
, RBL_NUMMER VARCHAR(64)
, BEREICH SMALLINT
, STEIG VARCHAR(128)
, STEIG_POINT POINT
, STAND DATETIME
);

now import data using that code:

LOAD DATA
INFILE ‚/wienerlinien-ogd-steige.csv‘
INTO TABLE wienerlinien_ogd_steige
FIELDS
TERMINATED BY ‚;‘
OPTIONALLY ENCLOSED BY ‚“‚
LINES TERMINATED BY ‚\r\n‘
IGNORE 1 LINES
(
  STEIG_ID
, FK_LINIEN_ID
, FK_HALTESTELLEN_ID
, RICHTUNG
, REIHENFOLGE
, RBL_NUMMER
, BEREICH
, STEIG
, @LAT
, @LON
, STAND
)
SET STEIG_POINT = POINT(@LAT,@LON)
;

that should result in the following output
Query OK, 6052 rows affected, 557 warnings (1.56 sec)

Records: 6052  Deleted: 0  Skipped: 0  Warnings: 557
we have some errors, because some rows contain empty strings for integer-data eg.
;““;““;““;
when you write SHOW WARNINGS; after the import you get the first warnings. eg:
| Warning | 1366 | Incorrect integer value: “ for column ‚BEREICH‘ at row 4331 |

compare the inserted rows (6052) with the rows of the source file: wc -l minus the header row.

now let’s view the data

SELECT * FROM wienerlinien_ogd_steige;

SELECT * FROM wienerlinien_ogd_steige
WHERE STEIG_ID = 215037496;

to extract the geo-data do the following

SELECT
  STEIG_ID
, STEIG
, X(STEIG_POINT) AS X 
, Y(STEIG_POINT) AS Y 
FROM wienerlinien_ogd_steige
WHERE STEIG LIKE ‚%13A%‘;
SELECT
  STEIG_ID
, FK_LINIEN_ID
, FK_HALTESTELLEN_ID
, RICHTUNG
, REIHENFOLGE
, RBL_NUMMER
, BEREICH
, STEIG
, X(STEIG_POINT) AS X 
, Y(STEIG_POINT) AS Y 
, STAND
FROM wienerlinien_ogd_steige

WHERE STEIG LIKE ‚%13A%‘;


now we note the number of rows for to compare with the next import
SELECT COUNT(*), STAND FROM wienerlinien_ogd_steige GROUP BY STAND;
+———-+———————+
| COUNT(*) | STAND               |
+———-+———————+
|     3026 | 2013-08-12 10:18:58 |
+———-+———————+

CREATE FUNCTION in ORACLE

simple Oracle-function to calculate the second next day of week per month

CREATE OR REPLACE FUNCTION PROJEKTE_INDI.GET_SECOND_NEXT_DAYOFWEEK(firstofmonth IN date, dayofweek IN VARCHAR2) 
RETURN date
IS 
    second_next_dayofweek date;
/*


returns the second next dayofweek in a month.
firstmonth is the first day in the month
dayofweek is the name of the day – monday, … (language depends on server installation!)


eg
SELECT GET_SECOND_NEXT_DAYOFWEEK(‚01.03.2011‘, ‚Mittwoch‘) AS DAYOFWEEK from DUAL


*/
BEGIN
    SELECT NEXT_DAY(NEXT_DAY(firstofmonth – 1,dayofweek),dayofweek) AS DAYOFWEEK 
    INTO second_next_dayofweek
    FROM DUAL;
    RETURN(second_next_dayofweek);
END
;



general mysql-server infos

login to sql-server
mysql -u -p

overview
SHOW SCHEMAS;
SHOW DATABASES;
SHOW TABLES;

get server variables
SHOW VARIABLES;

get server version
SHOW VARIABLES LIKE "%version%";

select values without a from section
SELECT ‚blub‘;

table infos – table size

SELECT 
    TABLE_NAME AS `Table Name`, 
    TABLE_ROWS AS `Rows`,
    (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2)) AS `Size Mb`
FROM 
    information_schema.TABLES
WHERE 
    TABLES.TABLE_SCHEMA = ‚my_schema‘
    AND TABLES.TABLE_TYPE = ‚BASE TABLE‘
;



MySQL and the Command Line
Sample with the test.sql:
    SELECT COUNT(*) FROM a.testtable;

    mysql test.log

if you want to run scripts inside mysql do
    source


LOAD DATA
One problem can be importing from a data file but the output from the loader is:
Query OK, 0 rows affected (0.00 sec)
The solution can be to define the correct LINE TERMINATOR

LINES TERMINATED BY ‚\r‘ — CR – Line Terminator (OS X)
LINES TERMINATED BY ‚\n‘ — LF – Line Terminator (UNIX)
LINES TERMINATED BY ‚\r\n‘ — CR+LF – Line Terminator (WINDOWS)
To indicate the Line Terminator use eg Notepad++ 

sample:
use test;

create table test1
(
id int,
nr int
)
;

truncate table test1;

/* nano test1.csv
id,nr
2,1
1,2
3,3
*/

load data infile ‚/tmp/test1.csv‘
into table test.test1
fields terminated by ‚,‘ 
optionally enclosed by ‚“‚ 
lines terminated by ‚\n‘ ignore 1 lines
(
`id`
, `nr`
)
;

SELECT * FROM test1;
SELECT COUNT(*) FROM test1;

EXPORT DATA
To export data to CSV-files use:

select * 
INTO OUTFILE ‚/tmp/sqlout.csv‘ 
FIELDS TERMINATED BY ‚;‘ 
OPTIONALLY ENCLOSED BY ‚“‚ 
ESCAPED BY ‚\\‘ 
LINES TERMINATED BY ‚\n‘ 
FROM v_locationdata;

PYTHON
install python connector – first search the correct package name
sudo apt-cache search MySQLdb
then install the package
sudo apt-get install python_mysqldb

now go to python
import _mysql
create a connection to your mysql-server and database
db=_mysql.connect(db=“test“)
now set the query
db.query(„““SELECT * FROM test1″““)
temp save the result to r
r=db.store_result()
and now read the result
r.fetch_row()

Oracle XML datahandling (import etc)

importing XML-files into an oracle database

create a table with a CLOB-column
CREATE TABLE CLOBTEST
(
  POID    NUMBER,
  POCLOB  CLOB
)

POID can/should be autoincrement…

using sqlloadr to load the XML-files into the table
with the unix find-command create a table with all your XML-files you want to import (see example below). the XML.ctl file looks like this:
LOAD DATA 
INFILE *
INTO TABLE CLOBtest TRUNCATE
FIELDS TERMINATED BY ‚,‘
(
    POID CHAR(10),
    clob_filename FILLER CHAR(100),
    POCLOB  LOBFILE(clob_filename) TERMINATED BY EOF
)
–sqlldr schema@db XML.ctl

–find /home/data/ -name *.XML
BEGINDATA
1,/home/data/DOC00001.XML
2,/home/data/DOC00001.XML
3,/home/data/DOC00001.XML

query to extract XML-data

the hard part is to query the XML-tags (elements, values etc) in the CLOB-column. here is an example. first convert the CLOB columns into a XMLType and then use extractvalue or other functions (extract…) to query your data

SELECT extractvalue(xmldata, ‚/700/710/711/‘) AS col1
FROM 
    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)
;
to query sub-tables in the XML-data
SELECT 

    t.id
    , li.ctry
FROM 

    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)    
p,
XMLTable
    (
        ‚/*‘ PASSING p.xmldata
        COLUMNS 
            id VARCHAR2(28) PATH ‚/product/@id‘,
            ctry XMLType PATH ‚/product/soldctry‘
    ) t
    ,
XMLTable
    (
        ‚ctry‘ PASSING t.ctry
        COLUMNS 
            ctry        VARCHAR2(2)  PATH ‚/‘
    ) li
ORDER BY
    t.id
    , li.ctry
;

the p-table is the conversion of the CLOB-datatype into an XML-document.
the t-table extracts the ID of the product-element and the country/countries inside the product.
the li-table extracts all country-names of the product.
the result is a list with all countries of a product.
ID CTRY
15A1 AT
15A1 BE
15A1 BG
15A1 CH
15A1 CY
01A2 AT
01A2 BE