Java Solutions: July 2014

Friday, July 25, 2014

SQL convert a Long or integer millisecond to a date string

Assume that you have an Order table in your database. The table has an OrderDate column whose data type is integer or long representing the millisecond the order is placed :-).

On your report, you of course would like the OrderDate to be displayed as a date string instead of an integer or long. The following SQL would get it done for you.

SELECT productName, orderAmount, price, to_char(to_date('1970-01-01 00', 'yyyy-mm-dd hh24') + orderDate/1000/60/60/24, 'MM/DD/YY') orderDate from Oder order by orderDate desc;

You may replace the 'MM/DD/YY' with any format that meets your purpose, such as 'MM/DD/YYYY HH:mm:ss' and 'YYYY-MM-DD HH24:MI:SS am'

---------------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

NetBeans split the editor window /open file in new window /open one file in two tabs

A. To open one file in two tabs

Double click the file or right-click the file and select Open to open the file in the editor window.
Right-click the tab title and select Clone or click Window in the top menu, select "Configure Window", then select "Clone Document".

B. To Open one file in two windows, do one of the followings

Right-click the title of the tab that has the file open, select Split, and then choose how you would like to split the file, vertically or horizontally.
Click Window in the top menu, select "Configure Window", then select "Split Document" and finally choose how your would like to split the file.

To close one of the windows, repeat one of the above two procedures and at the last step select Clear.

C. To open a file in a new editor window

Drag the title of the tab that has the file open to the bottom of the editor window until you see a rectangle and drop it. To close the window, right-click on the tab title and select "Collapse Document Tab Group".
Right-click the title of the tab that has this document open, select "New Document Tab Group". To close the window, right-click on the tab title and select "Collapse Document Tab Group".
Click Window in the top menu, select "Configure Window", then select "New Document Tab Group". To close the new window, select "Collapse Document Tab Group"

-------------------------------------------------------------------------------------------------------

Thursday, July 24, 2014

net.sf.dynamicreports.report.exception.DRException: net.sf.jasperreports.engine.JRException: Error retrieving field value from bean

The stack trace of the exception has the following lines.

net.sf.dynamicreports.report.exception.DRException: net.sf.jasperreports.engine.JRException: Error retrieving field value from bean

Caused by: java.lang.NoSuchMethodException: Property '<bean property>' has no getter method in class

The following code throws such an exception.

public class DynamicReportsTest {
public DynamicReportsTest() {
build();
}

private void build() {
JasperReportBuilder report = DynamicReports.report();
report.columns(col.column("Name", "name", DataTypes.stringType()),
col.column("Price", "price", DataTypes.doubleType()));

report.dataDataSource(createDataSource());
report.show();
}

private JRBeanCollectionDataSource createDataSource() {
List<ReportData> dataSource = new ArrayList<ReportData>();
ReportData data = new ReportData();

data.setName("Apple");
data.setPrice(1.89d);

dataSource.add(data);
data = new ReportData();
data.setName("Papaya");
data.setPrice(2.35d);

dataSource.add(data)

return new JRBeanCollectionDataSource(dataSource);
}

private class ReportData {
private String name;
private double price;

public String getName() {
return name;
}
public void setName(String n) {
name = n;
}
public double getPrice() {
return price;
}
public void setPrice(double p) {
price = p;
}
}

public static void main(String[] args) {
new DynamicReportsTest();
}
}

When the program is laying out the report, the data source is accessed by other code to retrieve the data. Since the ReportData class is private, its methods is invisible to other code.

To fix the exception simply change the ReportData inner class to public.

public class ReportData{
. . . . . .
}

----------------------------------------------------------------------------------------------------------

NetBeans low memory/not enough memory to compile

The NetBeans gives a "low memory" or "not enough memory to compile" warning when it needs more memory to compile your project.

A. Delete NetBeans cache to release memory.
Close NetBeans and delete the cache folder.

Location of NetBeans caches:
Windows: C:\Users\<user>\AppData\Local\NetBeans\Cache\<cache folder should be deleted>
MAC OS X: /Users//Library/Caches/NetBeans/<cache folder>
UNIX: /home//.cache/netbeans/<cache folder>

B. Allocate more memory for NetBeans.

1. Go to your NetBeans home directory.
2. Open the netbeans.conf file in the etc directory.
3. Find the netbeans_default_options parameter in the file.
4. Set the -J-Xms and -J-XX to larger values.

C. Allocate more memory for your project, follow the steps below.

1. Perform a clean and build of your project. You can get it done by performing one of the followings.
a. Right click your project and select "Clean and Build".
b. Select your project in the Project Pane, then type Shift+F11.
c. Select your project in the Project Pane, then click the Run button in the top menu of the frame and select "Clean and Build Project".

2. Watch the Output pane carefully to make sure that your project compiles successfully.

3. Right click your project and select "Properties". In the pop up window select "Run" in the left Categories. Then, set the VM Options on the right side of the window. For example, setting the VM Option to -Xms64m -Xmx1024m tells your JVM to allocate minimum 64 megabytes of memory to your project up to maximum of 1024 megabytes of memory. However, the memory set here cannot exceed the RAM your machine has.

-----------------------------------------------------------------------------------------------------------

DynamicReports set the title, zoom ratio, page fit to frame width of JasperViewer

When report.show() is called, the JasperViewer will display your report on the screen. By default, the title of the JasperViewer is "JasperViewer", and the zoom ratio is 100%. Follwing is some sample code for setting the title of the JasperViewer and setting the zoom ratio of the report to allow it to fit the width of the frame.

JasperReportBuilder report = DynamicReports.report();
report.columns(col.column("Column 1", "col1", DataTypes.stringType(),
. . . . . .
);
report.setDataSource(<dataSource>);

//Create the JasperViewer frame
JasperViewer reportViewer = new JasperViewer(report.toJasperPrint(), false);
//Without the second boolean argument, parent frame closes when the JasperViewer is closed
//JasperViewer reportViewer = new JasperViewer(report.toJasperPrint();

//Set the title to your choice
reportViewer.setTitle("<your title>");

//Set the zoom ratio, here it is 110%
//reportViewer.setZoomRatio(new Float(1.10));

//The page fit frame width zoom ration is 89.49%
reportViewer.setZoomRatio(new Float(0.8949)); //the frame fit ratio

reportViewer.setVisible(true);

---------------------------------------------------------------------------------------------------------

Wednesday, July 16, 2014

DynamicReports: DRDataSource, JRBeanCollectionDataSource, and JRMapCollectionDataSource

There are several ways to create data source for a Dynamic Report.

1. DRDataSource

This is the most straightforward one. You create a DRDataSource with a list of the field names of the columns in the report. You then add data row by row to the data source.

//The fieldNames here must be the same as in the report columns
DRDataSource dataSource = new DRDataSource("fieldName1', "fieldName2", ....);

//Add a row of values corresponding to the fields listed above
dataSource.add(value1, value2, value3......);

report.setDataSource(dataSource);

2. JRBeanCollectionDataSource

You need a Bean class to hold each row of the data in your report. This class must be public even though it is most likely an inner class. Otherwise a method not available error may occur.

//Create the bean
public class ReportData {
//the data type and field name must be the same as the corresponding column in the report
private String fieldName1;
private Integer fieldName2;

............

//getters and setters for all the fields
public String getFieldName1() {
return fieldName1
}
public void setFieldName1(String f1) {
fieldName1 = f1;
}

.............
}

//Create a Collection of the Bean with your data
List<ReportData> sourceData = new ArrayList<ReportData>();
ReportData data = new ReportData();

for (i=0; i<5; i++) {
data.setFieldName1("field one value");
data.setFieldName2(49);

............

sourceData.add(data);
data = new ReportData();
}

//Create the data source
JRDataSource dataSource = new JRBeanCollectionDataSource(sourceData);
report.setDataSource(dataSource);

3. JRMapCollectionDataSource

You need a map to hold each row of the data in your report. The key is the fieldName of a column and value is your data.

//Create a Collection of the Map
List<Map<String, Object>> sourceData = new ArrayList<Map<String, Objec>>();
Map<String, Object> data = new HashMap<String, Object>();

for (int i=0; i<5; i++) {
data.put("fieldName1", "field one value");
data.put("filedName2", 49);

...........

sourceData.add(data);
data = new HashMap<String, Object>();
}

//Create the data source
JRDataSource dataSource = new JRMapCollectionDataSource(sourceData);
report.setDataSource(dataSource);

--------------------------------------------------------------------------------------------------------

DynamicReports: Row Subreport - create a subreport in each row of the parent report

Assume that you have two tables, Fruit and Sales, in your database.

Table Fruit
name price popularIndex freshDays
Apple 1.68 10 20
Orange 0,96 5 8

Table Sales
saleDate fruitName amount customer charge payment balance
01/02/2013 Apple 1000 uphill fruit store 1680 1680 0
05/12/2014 Orange 2000 delicious 1920 1000 920

Now, you would like to have a report to list all the data in the Fruit table, and under each fruit list 10 of the most recent sales

Following is a sample code for creating the report.

public class FruitReportTest {
private JasperReport buildFruitReport() {
JasperReportBuilder report = DynamicReports.report();
report.columns(col.column("Name", "name", type.stringType()),
col.column("Price", "price", type.doubleType()),
col.columns("Popular Index", "popIndx", type.integerType()),
col.columns("Fresh Days", "freshDays", type.integerType()));
return report;
}

private JasperReportBuilder buildSalesReport() {
JasperReportBuilder report = DynamicReports.report();
report.columns(col.column("Sale Date", "saleDate", type.stringType()),
col.column("Amount", "amount", type.doubleType()),
col.columns("Customer", "customer", type.stringType()),
col.columns("Charge", "charge", type.doubleType()),
col.columns("Payment", "payment", type.doubleType()),
col.columns("Balance", "balance", type.doubleType()));
return report;
}

public class ReportData {
private String name;
private Double price;
private Integer popIndx;
private Integer freshDays;
private List<Map<String, Object>> salesData;

//getters and setters for all the above fields
.............
}

private JRDataSource createDataSource() {
Connection conn = <your method of getting database connection>
String sql1 = "Select * from Fruit";
PreparedStatement ps1 = conn.prepareStatement(sql1);
ResultSet rs = ps1.executeQuery();

String sql2 = "select * from Sales where fruitName = ?";
preparedStatement ps2 = conn.prepareStatement(sql2)

 List<ReportData> datasource = new ArrayList<ReportData>();
try {
while (rs.next()) {
ReportData data = new ReportData();
data.setName(rs.getString(1));
data.setPrice(rs.getDouble(2));
data.setPopIndx(rs.getInt(3));
data.setFreshDays(rs.getInt(4));

ps2.setString(1, rs.getString(1));
ResultSet rs2 = ps2.executeQuery();

List<Map<String, Object>> salesData = new ArrayList<Map<String, Object>>();
Map<String, Object> sd = new HashMap<String, Object>();
while (rs2.next()){
ds.put("saleDate", rs2.getString(1));
ds.put("amount", rs2.getDouble(3));
ds.put("customer", rs2.getString(4));
ds.put("charge", rs2.getDouble(5));
ds.put("payment", rs2.getDouble(6));
ds.put("balance", rs2.getDouble(7));

salesData.add(ds);
}
data.setSalesData(salesData);
rs2.close();
}
ps2.close();
rs.close();
ps1.close();
} catch (SQLException se) {
se.printStackTrace();
}
conn.close();

 return new JRBeanCollectionDataSource(datasource);
}

public static void main(String[] args) {
FruitReportTest test = new FruitReportTest();

JasperReportBuilder report = test.buildFruitReport();

 SubreportBuilder subreport = cmp.subreport(test.buildSalesReport())
 .setDataSource(exp.subDatasourceBeanCollection("salesData"));
report.detailFooter(cmp.horizontalList(cmp.horizontalGap(10), subreport, cmp.horizontalGap(10)));

report.setDataSource(createDataSource());
report.show();
}
}

Previous < > Next

Reference:

1. DynamicReports: Column Subreport - create subreport in a column of the parent report
2. DynamicReports Page subreport - create subreport in a page of the parent report and set report/subreport/component border

Friday, July 11, 2014

SQL: select another column if the column value is null/0/certain value

How to construct a sql to select from a column only when the value of the column is not a certain value (e.g. null, 0, etc), otherwise select from another column?

Lets assume that you have two tables, Fruit and Apple, in your database.

table Fruit
name price
Apple 1.69
Orange 0.98
Pear 1.29

table Apple
name price
Golden Delicious 1.49
Fuji 1.89
Gala 0

The following sql gets the price for each type of apple. If the price in the Apple table for that type of apple is 0, then use the Apple price in the Fruit table.

SELECT a.name name
CASE a.price //the column
WHEN 0 THEN f.price //if a.price=0, gets f.price
ELSE a.price //else gets a.price
END AS price //the resulting field name is price
FROM Apple a, Fruit f
WHERE f.name='Apple';

The general form of the sql for getting value from another column if value is a certain known value is as below.

SELECT
CASE <columnA>
WHEN <value1> THEN <columnB> //e.g. WHEN NULL or WHEN LIKE '%ard%'
WHEN <value2> THEN <columnC>
......
ELSE <columnA>
END AS <field name in the result set>
FROM <list of tables>

-----------------------------------------------------------------------------------------------------------------

Thursday, July 10, 2014

java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found, too many open files

This exception occurs when too many Connections are open at the same time. The following code generates such an SQLException.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
String url = "jdbc:oracle:thin:@<host>:<port>";
String driver = "oracle.jdbc.OracleDriver";
String user = "<userId>";
String passwd = "<password>";

public Connection getConnection() throws Exception {
System.out.println("getting Connection");
Connection conn = null;

Class.forName(driver);
conn = DriverManager.getConnection(url, user, passwd);

return conn;
}

public static void main(String[] args) {
Test test = new Test();
Connection conn = null;
Statement stm = null;
String sql = "select distinct NAME from FRUIT";

try {
conn = test.getConnection();

//Exception occurs in this loop
while(true){
conn = test.getConnection();
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
}
} catch (Exception e) {
e.printStackTrace();
}

}
}

The while(true) loop above keeps creating Connections and not closing them, therefore, gives such a SQLException.

To fix this problem

Move the "conn = test.getConnection();" out of the while(true) loop and close it after quering database is finished. The code would look like this.

conn = test.getConnection();
while(true){
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
}
conn.close();

If you have to create the Connection inside the loop and are not able to move it out, you, then, have to close it each time when it's job is finished.

while(true){
conn = test.getConnection();
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
conn.close();
}

----------------------------------------------------------------------------------------------------------

java.sql.SQLException: ORA-01000: maximum open cursors exceeded / too many open cursors

This exception is caused by leaving too many open Statements and ResultSets. For example the following code throws such an SQLException.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
String url = "jdbc:oracle:thin:@<host>:<port>";
String driver = "oracle.jdbc.OracleDriver";
String user = "<userId>";
String passwd = "<password>";

public Connection getConnection() throws Exception {
System.out.println("getting Connection");
Connection conn = null;

Class.forName(driver);
conn = DriverManager.getConnection(url, user, passwd);

return conn;
}

public static void main(String[] args) {
Test test = new Test();
Connection conn = null;
Statement stm = null;
String sql = "select distinct NAME from FRUIT";

try {
conn = test.getConnection();

//Exception occurs in this loop
while(true){
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}

}
}

The while(true) loop in the above code keeps creating new Statement and ResultSet without closing them, therefore generates such an SQLException.

To Fix the Problem, add the following code at the end of the while(true) loop.

rs.close();
stm.close();

So, the while(true) loop looks like this.

while(true){
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
}

-------------------------------------------------------------------------------------------------------------

Tuesday, July 8, 2014

DynamicReports: Column Subreport - create subreport in a column of the parent report

Following is a sample code of creating a subreport that includes the name and price in the parent column Objects.

import static net.sf.dynamicreports.report.builder.DynamicReports.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import net.sf.dynamicreports.examples.Templates;
import net.sf.dynamicreports.jasper.builder.JasperReportBuilder;
import net.sf.dynamicreports.report.base.expression.AbstractSimpleExpression;
import net.sf.dynamicreports.report.builder.DynamicReports;
import net.sf.dynamicreports.report.builder.component.SubreportBuilder;
import net.sf.dynamicreports.report.builder.style.Styles;
import net.sf.dynamicreports.report.constant.HorizontalAlignment;
import net.sf.dynamicreports.report.definition.ReportParameters;
import net.sf.dynamicreports.report.exception.DRException;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.engine.data.JRMapCollectionDataSource;

public class ColumnSubreportTest {

public ColumnSubreportTest() {
SubreportBuilder subreport = cmp.subreport(new SubreportDesign())
.setDataSource(new SubreportData());

JasperReportBuilder report = DynamicReports.report();
try {
report.fields(field("objects", List.class))
.columns(
col.column("Category", "category", type.stringType()),
col.column("Health Index", "healthIndex", type.integerType()),
col.componentColumn("Objects", subreport)
.setFixedWidth(240)
.setStyle(Styles.style()
.setBottomPadding(15))
.setTitleStyle(Styles.style()
.bold().setHorizontalAlignment(HorizontalAlignment.LEFT)));
report.setColumnTitleStyle(Styles.style().bold()
.setHorizontalAlignment(HorizontalAlignment.LEFT));
report.setColumnStyle(Styles.style()
.setHorizontalAlignment(HorizontalAlignment.LEFT));
report.title(cmp.text("List of real food")
.setStyle(Styles.style().bold().setFontSize(14)
.setHorizontalAlignment(HorizontalAlignment.CENTER)
.setBottomPadding(20)))
.pageFooter(Templates.footerComponent);

report.setDataSource(createDataSource());
report.show();
} catch (DRException e) {
e.printStackTrace();
}
}

private class SubreportDesign extends AbstractSimpleExpression<JasperReportBuilder> {
private static final long serialVersionUID = 1L;

@Override
public JasperReportBuilder evaluate(ReportParameters reportParameters) {
JasperReportBuilder report = report()
.columns(col.column("name", type.stringType()),
col.column("price", type.stringType()));
return report;
}
}

private class SubreportData extends AbstractSimpleExpression<JRDataSource> {

@Override
public JRDataSource evaluate(ReportParameters reportParameters) {
Collection<Map<String, ?>> value = reportParameters.getValue("objects");
return new JRMapCollectionDataSource(value);
}
}

private JRDataSource createDataSource() {
ArrayList<ReportData> datasource = new ArrayList<ReportData>();

ReportData data = new ReportData();
data.setCategory("Fruit");
data.setHealthIndex(95);

List<Map<String, Object>> objects = new ArrayList<Map<String, Object>>();
Map<String, Object> values = new HashMap<String, Object>();
values.put("name", "Apple");
values.put("price", "$1.65/lb");
objects.add(values);
values = new HashMap<String, Object>();
values.put("name", "Papaya");
values.put("price", "$1.98/lb");
objects.add(values);
data.setObjects(objects);

datasource.add(data);

data = new ReportData();
data.setCategory("Rice");
data.setHealthIndex(70);

objects = new ArrayList<Map<String, Object>>();
values = new HashMap<String, Object>();
values.put("name", "Brown Rice");
values.put("price", "$0.35/lb");
objects.add(values);
values = new HashMap<String, Object>();
values.put("name", "White Rice");
values.put("price", "$0.15/lb");
objects.add(values);
data.setObjects(objects);

datasource.add(data);

return new JRBeanCollectionDataSource(datasource);
}

//It is important to let this inner class to be public
public class ReportData {
private String category;
private Integer hi;
private List<Map<String, Object>> objects;

public String getCategory() {
return category;
}

public void setCategory(String c) {
category = c;
}

public Integer getHealthIndex() {
return hi;
}

public void setHealthIndex(Integer i) {
hi = i;
}

public List<Map<String, Object>> getObjects() {
return objects;
}

public void setObjects(List<Map<String, Object>> o) {
objects = o;
}
}

public static void main(String[] args) {
new ColumnSubreportTest();
}
}

> Next

------------------------------------------------------------------------------------------------------------------

References:

1. Row Subreport - create subreport in a row of the parent report
2. DynamicReports Page subreport - create subreport in a page of the parent report and set report/subreport/component border

Thursday, July 3, 2014

Set text style, color, size, font family and print text in multiple lines of JComponent (JLabel, JCheckBox, JRadioButton ...) (2): html

import java.awt.FlowLayout;
import javax.swing.JFrame;
import javax.swing.JLabel;

public class JLabelTest extends JFrame {

public JLabelTest() {

StringBuilder text = new StringBuilder("<html>")
.append("My appearance can be ")
.append(" very colorful</html>");

JLabel label = new JLabel(text.toString());

setLayout(new FlowLayout());
add(label);
setSize(700, 100);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setVisible(true);
}

public static void main(String[] args){
javax.swing.SwingUtilities.invokeLater(new Runnable() {
public void run() {
new JLabelTest();
}
});
}
}

------------------------------------------------------------------------------------------------------------------

Set text style, color, size and font family of JComponents (JLabel, JCheckBox, JRadioButton ....) (1): attributes and JComponent methods

import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.font.TextAttribute;
import java.util.HashMap;
import javax.swing.JFrame;
import javax.swing.JLabel;

public class JLabelTest extends JFrame {

public JLabelTest(boolean attri) {
JLabel label = new JLabel("My appearance can be very coloful");

if (!attri){
//set the font, font style, and font size
label.setFont(new Font(Font.SERIF, Font.ITALIC, 18));

//set the text color
label.setForeground(Color.red);

//set the background color
label.setBackground(Color.yellow);

this.setTitle("Using JComponent methods");
this.setLocation(100, 100);

} else {

HashMap<TextAttribute, Object> attributes = new HashMap<TextAttribute, Object>();

attributes.put(TextAttribute.FAMILY, "Monospaced");
//set text to be bold
attributes.put(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD);
//set text to be Italic
attributes.put(TextAttribute.POSTURE, TextAttribute.POSTURE_OBLIQUE);
attributes.put(TextAttribute.FOREGROUND, Color.BLUE);
attributes.put(TextAttribute.BACKGROUND, Color.green);
attributes.put(TextAttribute.SIZE, 18);

label.setFont(new Font(attributes));
this.setTitle("Using text attributes");
this.setLocation(100, 300);
}

setLayout(new FlowLayout());
add(label);
setSize(500, 100);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setVisible(true);
}

public static void main(String[] args){
javax.swing.SwingUtilities.invokeLater(new Runnable() {
public void run() {
new JLabelTest(false);
new JLabelTest(true);
}
});
}
}

-------------------------------------------------------------------------------------------------------------

Wednesday, July 2, 2014

java.sql.SQLException: ORA-01791: not a SELECTed expression - resolved

When the unique or distinct key word is used in the SELECT clause and at the same time an ORDER BY clause is attached to the end of a query, it sometimes returns the SQLException that says "not a selected expression" , remove either the ORDER BY clause or the unique/distinct key word from the query stops the exception.

The following two queries throw such a SQLException.

1. SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
FROM person ORDER BY lastname, firstname;

2. SELECT unique personId, firstname, lastname, ssn
FROM person ORDER BY lastname, firstname, middlename;

The real cause of the exception is that for the ORDER BY to work properly when a unique/distinct key word is used, it has to be able to clearly identify the columns put in the ORDER BY clause from the SELECT clause. If a column in the ORDER BY clause is missing or unable to be identified, the exception is generated.

In the above first query, both the lastname and firstname in the ORDER BY are not clearly specified in the SELECT clause. In the above second query, the middlename in the ORDER BY clause is missing from the SELECT clause.

If you don't mind of duplicate or incorrectly ordered results, you may either remove the unique/distinct key word or the column causing the problem from the ORDER BY clause. Otherwise, you need to modify your query to make it work.

The following modifications will fix the above two queries,

1. SELECT unique personId, nvl (firstname, ' ') firstname, nvl (lastname, ' ') lastname, ssn
FROM person ORDER BY lastname, firstname;

OR

SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
FROM person ORDER BY nvl (lastname, ' '), nvl (firstname, ' ');

2. SELECT personId, firstname, lastname, ssn FROM (
SELECT unique personId, firstname, lastname, middlename, ssn
FROM person ORDER BY lastname, firstname, middlename);

-------------------------------------------------------------------------------------------------------------