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:
  1. CREATE TABLE CUSTOMER  
  2. (  
  3.   ID                           INTEGER          NOT NULL,    
  4.   FIRSTNAME                    VARCHAR          NOT NULL,  
  5.   LASTNAME                     VARCHAR          NOT NULL  
  6. );  
  7. CREATE TABLE CARD  
  8. (  
  9.   ID                          INTEGER           NOT NULL,  
  10.   CUSTOMERID                  INTEGER,  
  11.   SOMEDATA                    VARCHAR           DEFAULT 'ffff'  
  12. );  
  13. insert into card (id, customerid, somedata)  
  14. values (1, null'abcd');  
  15. insert into card (id, customerid, somedata)  
  16. values (2, null'efgh');  
  17. insert into card (id, customerid, somedata)  
  18. values (3, null'ijkl');  
  19. insert into customer (id, firstname, lastname)  
  20. values (1, 'John''Doe');  
  21. insert into customer (id, firstname, lastname)  
  22. values (2, 'Jane''Doe');  
  23. insert into customer (id, firstname, lastname)  
  24. values (3, 'Jim''Doe');  
  25. update card set customerid = 1 where id = 2;  
  26.   
  27. SELECT  
  28.     MIN (customer.ID)  
  29. FROM customer  
  30. WHERE customer.ID NOT IN   
  31.     (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 :)

  1. -- Simple table  
  2. CREATE TABLE Emp  
  3. (  
  4. dept VARCHAR2(20) NOT NULL,  
  5. nick VARCHAR2(20) NOT NULL,  
  6. pay  NUMBER NOT NULL  
  7. );  

  1. -- All with different pays  
  2. INSERT INTO Emp  
  3. (dept, nick, pay)VALUES  
  4. ('Admins' ,'Mike' ,5000 );  
  5. INSERT INTO Emp  
  6. (dept ,nick ,pay) VALUES  
  7. ('Admins' ,'Michael' ,4000 );  
  8. INSERT INTO Emp  
  9. (dept ,nick ,pay) VALUES  
  10. ('Admins' ,'Wellington' ,3000 );  

  1. -- First two with same pay checks  
  2. INSERT INTO Emp  
  3. (dept ,nick ,pay) VALUES  
  4. ('Programers' ,'Bix' ,5000 );  
  5. INSERT INTO Emp  
  6. (dept ,nick ,pay) VALUES  
  7. ('Programers' ,'Pierre' ,5000 );  
  8. INSERT INTO Emp  
  9. (dept ,nick ,pay) VALUES  
  10. ('Programers' ,'Ivanhoehoe' ,3000 );  
  11. INSERT INTO Emp  
  12. (dept ,nick ,pay) VALUES  
  13. ('Programers' ,'Boldrick' ,2000 );  
  14. INSERT INTO Emp  
  15. (dept ,nick ,pay) VALUES  
  16. ('Programers' ,'Payson' ,1000 );  

  1. -- 3. and 4. with same checks  
  2. INSERT INTO Emp  
  3. (dept ,nick ,pay) VALUES  
  4. ('CEOs' ,'Maush' ,50000);  
  5. INSERT INTO Emp  
  6. (dept ,nick ,pay) VALUES  
  7. ('CEOs' ,'Pierre' ,4000 );  
  8. INSERT INTO Emp  
  9. (dept ,nick ,pay) VALUES  
  10. ('CEOs' ,'Bob' ,3000 );  
  11. INSERT INTO Emp  
  12. (dept ,nick ,pay) VALUES  
  13. ('CEOs' ,'Boldrick' ,3000 );  
  14. INSERT INTO Emp  
  15. (dept ,nick ,pay) VALUES  
  16. ('CEOs' ,'Vlax' ,1000 );  

row_number(), rank(), dense_rank()
Here is the difference in functions:
  1. SELECT dept, nick, pay,  
  2. row_number() over (partition by dept ORDER BY pay desc) rnum,  
  3. rank() over (partition by dept ORDER BY pay desc) rnk,  
  4. dense_rank() over (partition by dept ORDER BY pay desc) densrnk  
  5. 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
  1. "font-weight:bold;">"font-size:180%;">LEAD/LAG  
  2. -- LEAD/LAG  
  3. SELECT dept, nick, pay,  
  4. LEAD(pay, 1, 0) OVER (PARTITION BY dept ORDER BY pay DESC NULLS LAST) NEXT_LOWER_SAL,  
  5. LAG(pay, 1, 0) OVER (PARTITION BY dept ORDER BY pay DESC NULLS LAST) PREV_HIGHER_SAL  
  6. FROM Emp  
  7. 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
  1. -- FIRST_VALUE/LAST_VALUE  
  2. SELECT nick, dept, pay - FIRST_VALUE(pay)  
  3. OVER (PARTITION BY dept ORDER BY pay DESC) pay_GAP  
  4. FROM Emp  
  5. 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.
  1. --FIRST/LAST  
  2. SELECT dept,  
  3. nick,  
  4. pay,  
  5. MIN(pay) KEEP (DENSE_RANK FIRST ORDER BY pay) OVER (PARTITION BY dept) "Lowest",  
  6. MAX(pay) KEEP (DENSE_RANK LAST ORDER BY pay) OVER (PARTITION BY dept) "Highest"  
  7. FROM Emp  
  8. 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:

  1.    
  2. public class CustomJasperDataSource implements JRDataSource {  
  3.   
  4. MyObjectInMyTableClass chartEntry;  
  5.   
  6. Iterator iterator;  
  7.   
  8. ArrayList chartEntries;  
  9.   
  10. public CustomJasperDataSource(ArrayList chartEntries) {  
  11. this.chartEntries = chartEntries;  
  12. iterator = chartEntries.iterator();  
  13. }  
  14.   
  15. public Object getFieldValue(JRField jrField) throws JRException {  
  16. if (jrField.getName().equalsIgnoreCase("myObjectAttribute1")) {  
  17.  return chartEntry.getMyObjectAttribute1();  
  18. }  
  19. if (jrField.getName().equalsIgnoreCase("myObjectAttribute2")){  
  20.  return chartEntry.getMyObjectAttribute2();  
  21. }  
  22. ...  
  23. return null;  
  24. }  
  25.   
  26. public boolean next() throws JRException {  
  27. boolean hasNext = iterator.hasNext();  
  28. if (hasNext) {  
  29.  chartEntry = (MyObjectInMyTableClass) iterator.next();  
  30. }  
  31. return hasNext;  
  32. }  
  33.   
  34. }  

Invoking Jasper
This is called when you press some swing button:
  1.    
  2. protected void jButtonCreatePdfActionPerformed(ActionEvent e) {  
  3.   
  4.  JasperPrint jp = generateReport(myArrayListOfData);  
  5.  JasperViewer jasperViewer = new JasperViewer(jp, false);  
  6.  jasperViewer.setVisible(true);  
  7.   
  8. }  

And this is how generateReport method looks like:
  1.    
  2. private JasperPrint generateReport(ArrayList data) {  
  3.     JasperPrint jasperPrint = null;  
  4.     String templateName = "MyTemplateName.jasper";  
  5.     try {  
  6.         jasperPrint = JasperFillManager.fillReport(templateName,  
  7.                 new HashMap(), new CustomJasperDataSource(data));  
  8.     } catch (JRException e) {  
  9.                   doSOmeExcCatching();  
  10.     }  
  11.     return jasperPrint;  
  12. }