Thursday, October 16, 2008

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!!!
:)

Thursday, August 14, 2008

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;









































AdminsMike5,000111
AdminsMichael4,000222
AdminsWellington3,000333
CEOsMaush50,000111
CEOsPierre4,000222
CEOsBob3,000333
CEOsBoldrick3,000433
CEOsVlax1,000554
ProgramersBix5,000111
ProgramersPierre5,000211
ProgramersIvanhoe3,000332
ProgramersBoldrick2,000443
ProgramersPayson1,000554

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!

Monday, March 3, 2008

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;
}