Syntax highlighter

There was a time when I wanted to give up blogger because poor code formating options.

Google to the rescue:

http://code.google.com/p/syntaxhighlighter/

Dangerous SQL usage of "NOT IN"

Here is example structure:

CREATE TABLE CUSTOMER
(
  ID                           INTEGER          NOT NULL,  
  FIRSTNAME                    VARCHAR          NOT NULL,
  LASTNAME                     VARCHAR          NOT NULL
);
CREATE TABLE CARD
(
  ID                          INTEGER           NOT NULL,
  CUSTOMERID                  INTEGER,
  SOMEDATA                    VARCHAR           DEFAULT 'ffff'
);
insert into card (id, customerid, somedata)
values (1, null, 'abcd');
insert into card (id, customerid, somedata)
values (2, null, 'efgh');
insert into card (id, customerid, somedata)
values (3, null, 'ijkl');
insert into customer (id, firstname, lastname)
values (1, 'John', 'Doe');
insert into customer (id, firstname, lastname)
values (2, 'Jane', 'Doe');
insert into customer (id, firstname, lastname)
values (3, 'Jim', 'Doe');
update card set customerid = 1 where id = 2;

SELECT
    MIN (customer.ID)
FROM customer
WHERE customer.ID NOT IN 
    (SELECT customerId FROM card WHERE customerId IS NOT NULL)

Last SELECT statement returns 2 which is ok but if you remove this part "WHERE customerId IS NOT NULL" all you get is NULL.
Database recognizes NULL value as "unknown".
It can't tell "I'm sure there IS NO customer.ID in that SELECT." because there are unknowns!!!
:)

Oracle analytic functions

Simple example:
My firm with employees :)

-- Simple table
CREATE TABLE Emp
(
dept VARCHAR2(20) NOT NULL,
nick VARCHAR2(20) NOT NULL,
pay  NUMBER NOT NULL
);

-- All with different pays
INSERT INTO Emp
(dept, nick, pay)VALUES
('Admins' ,'Mike' ,5000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Admins' ,'Michael' ,4000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Admins' ,'Wellington' ,3000 );

-- First two with same pay checks
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Bix' ,5000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Pierre' ,5000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Ivanhoehoe' ,3000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Boldrick' ,2000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Payson' ,1000 );

-- 3. and 4. with same checks
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Maush' ,50000);
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Pierre' ,4000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Bob' ,3000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Boldrick' ,3000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Vlax' ,1000 );

row_number(), rank(), dense_rank()
Here is the difference in functions:

SELECT dept, nick, pay,
row_number() over (partition by dept ORDER BY pay desc) rnum,
rank() over (partition by dept ORDER BY pay desc) rnk,
dense_rank() over (partition by dept ORDER BY pay desc) densrnk
FROM Empdept nick pay RNUM RNK DENSRNK;

Admins	Mike	5,000	1	1	1
Admins	Michael	4,000	2	2	2
Admins	Wellington	3,000	3	3	3
CEOs	Maush	50,000	1	1	1
CEOs	Pierre	4,000	2	2	2
CEOs	Bob	3,000	3	3	3
CEOs	Boldrick	3,000	4	3	3
CEOs	Vlax	1,000	5	5	4
Programers	Bix	5,000	1	1	1
Programers	Pierre	5,000	2	1	1
Programers	Ivanhoe	3,000	3	3	2
Programers	Boldrick	2,000	4	4	3
Programers	Payson	1,000	5	5	4

Partition by
Watch what "partition by" can do:
If we need 3 most payed emps by department we surround our query with SELECT * FROM( ourQuery ) where rnum < 4

LEAD/LAG
-- LEAD/LAG
SELECT dept, nick, pay,
LEAD(pay, 1, 0) OVER (PARTITION BY dept ORDER BY pay DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(pay, 1, 0) OVER (PARTITION BY dept ORDER BY pay DESC NULLS LAST) PREV_HIGHER_SAL
FROM Emp
ORDER BY dept, pay DESC;dept nick pay NEXT_LOWER_SAL PREV_HIGHER_SAL

Admins Mike 5,000 4,000 0
Admins Michael 4,000 3,000 5,000
Admins Wellington 3,000 0 4,000
CEOs Maush 50,000 4,000 0
CEOs Pierre 4,000 3,000 50,000
CEOs Bob 3,000 3,000 4,000
CEOs Boldrick 3,000 1,000 3,000
CEOs Vlax 1,000 0 3,000
Programers Bix 5,000 5,000 0
Programers Pierre 5,000 3,000 5,000
Programers Ivanhoe 3,000 2,000 5,000
Programers Boldrick 2,000 1,000 3,000
Programers Payson 1,000 0 2,000

FIRST_VALUE and LAST_VALUE
FIRST_VALUE and LAST_VALUE work on first and last record in table

-- FIRST_VALUE/LAST_VALUE
SELECT nick, dept, pay - FIRST_VALUE(pay)
OVER (PARTITION BY dept ORDER BY pay DESC) pay_GAP
FROM Emp
ORDER BY dept, pay_GAP DESCnick dept pay_GAP

Mike Admins 0
Michael Admins -1,000
Wellington Admins -2,000
Maush CEOs 0
Pierre CEOs -46,000
Bob CEOs -47,000
Boldrick CEOs -47,000
Vlax CEOs -49,000
Bix Programers 0
Pierre Programers 0
Ivanhoe Programers -2,000
Boldrick Programers -3,000
Payson Programers -4,000
KEEP
Dunno what this does.

--FIRST/LAST
SELECT dept,
nick,
pay,
MIN(pay) KEEP (DENSE_RANK FIRST ORDER BY pay) OVER (PARTITION BY dept) "Lowest",
MAX(pay) KEEP (DENSE_RANK LAST ORDER BY pay) OVER (PARTITION BY dept) "Highest"
FROM Emp
ORDER BY dept, pay;dept nick pay Lowest Highest

Admins Wellington 3,000 3,000 5,000
Admins Michael 4,000 3,000 5,000
Admins Mike 5,000 3,000 5,000
CEOs Vlax 1,000 1,000 50,000
CEOs Boldrick 3,000 1,000 50,000
CEOs Bob 3,000 1,000 50,000
CEOs Pierre 4,000 1,000 50,000
CEOs Maush 50,000 1,000 50,000
Programers Payson 1,000 1,000 5,000
Programers Boldrick 2,000 1,000 5,000
Programers Ivanhoe 3,000 1,000 5,000
Programers Pierre 5,000 1,000 5,000
Programers Bix 5,000 1,000 5,000

Have Fun!

Getting serious (Exporting PDF from Jasper)

Ok, ok... Blogging is good for your health :) 
My boss asked me today did I use JFlex and can I provide quick tutorial.
I used JFLex, JCUp... made some compiling efforts back then. But I can't remember a thing.
So, my first effort in getting serious :) will be my work in progress, using JasperReports and IReport.


IReport crash course
Ok, what is important.
Go to View->Fields. Field names are in my example "myObjectAttribute1" and "myObjectAttribute2". I'll use them later in Java for my data source method "getFieldValue(JRField jrField)". And thats all for simple report! :)
Put some static text, some pictures... and your text fields for "myObjectAttributes". Something like this:

Now save as "MyTemplateName.jrxml".
Compile.
Put it where your Java code can find it.
That's all.
What you need in your Java code
First you need a DataSource for your report. I needed swing table in my report so I'm getting my table model  ArrayList and making MyDataSource:

 
public class CustomJasperDataSource implements JRDataSource {

MyObjectInMyTableClass chartEntry;

Iterator iterator;

ArrayList chartEntries;

public CustomJasperDataSource(ArrayList chartEntries) {
this.chartEntries = chartEntries;
iterator = chartEntries.iterator();
}

public Object getFieldValue(JRField jrField) throws JRException {
if (jrField.getName().equalsIgnoreCase("myObjectAttribute1")) {
 return chartEntry.getMyObjectAttribute1();
}
if (jrField.getName().equalsIgnoreCase("myObjectAttribute2")){
 return chartEntry.getMyObjectAttribute2();
}
...
return null;
}

public boolean next() throws JRException {
boolean hasNext = iterator.hasNext();
if (hasNext) {
 chartEntry = (MyObjectInMyTableClass) iterator.next();
}
return hasNext;
}

}

Invoking Jasper
This is called when you press some swing button:

 
protected void jButtonCreatePdfActionPerformed(ActionEvent e) {

 JasperPrint jp = generateReport(myArrayListOfData);
 JasperViewer jasperViewer = new JasperViewer(jp, false);
 jasperViewer.setVisible(true);

}

And this is how generateReport method looks like:

 
private JasperPrint generateReport(ArrayList data) {
    JasperPrint jasperPrint = null;
    String templateName = "MyTemplateName.jasper";
    try {
        jasperPrint = JasperFillManager.fillReport(templateName,
                new HashMap(), new CustomJasperDataSource(data));
    } catch (JRException e) {
                  doSOmeExcCatching();
    }
    return jasperPrint;
}

Some nice thoughts

Martin Luther King:"History will record that the greatest tragedy of this period of social transition, was not the vitriolic words and actions of the bad people – but the appalling silence and indifference of the good people. Our generation will not only have to repent for the words and acts of the children of darkness, but also for the fears and apathy of the children of light."

Some nice thoughts

http://devizen.com/blog/2007/09/11/ruin/
Blaise Pascal: “We never keep to the present. We … anticipate the future as if we found it too slow in coming and were trying to hurry it up, or we recall the past as if to stay its too rapid flight. We are so unwise that we wander about in times that do not belong to us and do not think of the only one that does; so vain that we dream of times that are not and blindly flee the only one that is… [We] think of how we are going to arrange things over which we have no control for a time we can never be sure of reaching… Thus we never actually live, but hope to live, and since we are always planning how to be happy, it is inevitable that we should never be so.”