Tuesday, August 26, 2014

Launch the default or a particular web browser in Java

A. Launch the default web browser

1. Launch in Windows

import java.awt.Desktop;
import java.io.IOException;
import java.net.URI;
import java.net.URISyntaxException;

public class LaunchBrowserTest {
    public static void main(String args[]) {
        String url = "http://www.google.com";

         //method 1 of launching web browser in Windows
         if (Desktop.isDesktopSupported()) {
                Desktop desktop = Desktop.getDesktop();
                try {
                    desktop.browse(new URI(url));
                } catch (IOException | URISyntaxException e) {
                    e.printStackTrace();
                }
            }

            //method 2 of launching web browser in Windows
            String os = System.getProperty("os.name").toLowerCase();
             Runtime rt = Runtime.getRuntime();
             if (os.indexOf("win") >= 0) {
                    try {
                            rt.exec("rundll32 url.dll,FileProtocolHandler " + url);
                     } catch (IOException  e) {
                             e.printStackTrace();
                      }
                }
         }
}

2. Launch in MAC

public class LaunchBrowserTest {
    public static void main(String args[]) {
        String url = "http://www.google.com";
        String os = System.getProperty("os.name").toLowerCase();
        Runtime rt = Runtime.getRuntime();

         if (os.indexOf("mac") >= 0) {
                  try {
                          rt.exec("open " + url);
                   } catch (IOException  e) {
                             e.printStackTrace();
                      }
                }
         }
}

3. Launch in other systems

public class LaunchBrowserTest {
    public static void main(String args[]) {
           String url = "http://www.google.com";
            Runtime rt = Runtime.getRuntime();
     
            try {
                    rt.exec("xdg-open " + url);
                } catch (IOException e) {
                    e.printStackTrace();
                }
       }
}

B. Launch a particular web browser

1. Launch in Windows

public class LaunchBrowserTest {
    public static void main(String args[]) {
        String url = "http://www.google.com";
        String os = System.getProperty("os.name").toLowerCase();
        Runtime rt = Runtime.getRuntime();

        try {
            //launch the google chrome
            String[] cmds = new String[] {"C:/Program Files (x86)/Google/Chrome/Application/chrome.exe", url};
             //launch the internet explorer
             // String[] cmds = new String[] {"C:/Program Files (x86)/Internet Explorer/iexplore.exe", url};                      //launch the firefox
             //String[] cmds = new String[] {"<your path>/firefox.exe", url};

            rt.exec(cmds);
        } catch (IOException e) {
                    e.printStackTrace();
                }
       }
}

2. Launch in Unix/Linux

public class LaunchBrowserTest {
    public static void main(String args[]) {
        String url = "http://www.google.com";
        String os = System.getProperty("os.name").toLowerCase();
        Runtime rt = Runtime.getRuntime();

        if (os.indexOf("nix") >= 0 || os.indexOf("nux") >= 0) {
              try {
                  //launch the google chrome
                   rt.exec(new String[] {"chrome", url});
                  //or
                   // rt.exec(new String[]{"sh", "-c", "chrome\"+url+"\"});
               
                  //launch the firefox
                   rt.exec(new String[] {"firefox", url});
                  //or
                  // rt.exec(new String[]{"sh", "-c", "firefox\"+url+"\"});
              } catch (IOException e) {
                    e.printStackTrace();
                }
       }
}

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

                        
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.

Run .jar file in Windows

1. Specify the Application entry point
      Method 1
            A. Create the manifest.mf file with the following content. This file must ends with an empty line after the last line, otherwise, it will not be processed properly.


           Main-Class: <package>.<file>.class

            B. Create the jar file and specify the application entry point by executing one of the following commands.

                      jar -cfm <file>.jar manifest.mf  <package>/*.class

      Method 2
            Set the entry point to <package>.<file>.class by running the following command.

                   jar -cfe <file>.jar <package>.<file> <package>/<file>.class

2. Put all the jar files and other required files such as properties files if they are not included in the jar already in the same directory.

   To run from the command line, type: java -jar <file>.jar


  To run by double click the jar file, the .jar file type has to be associated with the java execution program. To set the association, do the following.

     A. Open the Control Panel
     B. Go to Programs\Default Programs
     C. Click Associate a file type or protocol with a program
     D. Scroll down the list, double click on the .jar entry
     E. Click the Browse and choose C:\Program Files\Java\jre7\bin\javaw or wherever your java is installed
     F. Click Open and then OK

3. Follow the steps here to create a desktop icon to launch the application by double clicking on the icon.
          A. Right click on desktop, choose New, then Shortcut.
          B. Enter java -jar <file>.jar in the text box, and click Next
          C. Enter a <name> for your shortcut, then click Finish
          D. Right click on the <name> shortcut on your desktop and choose Properties
          E. In the Start in: box, enter the path of the directory where the <file>.jar is located. click Apply.
          F. Click the Change Icon button and browse to select an image to use for your shortcut. Click OK and OK.

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

                        

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.

Friday, August 22, 2014

Bit And in Java and SQL

The Bit And compares each digits of two numbers in their binary format.

Decimal format                   Binary format
2                                        00000010
16                                      00010000
18                                      00010010

The Bit And works in the way that if two numbers both have 1 at a digit position in their binary formats, the result number has a 1 for that digit position, otherwise the result has a 0 for that digit position.

For numbers 2 and 16, since there has no position in their binary format that both numbers have 1, so the result of Bit And of 2 and 16 is 0 (00000000).

The result of Bit And of numbers 2 and 18, however, is 2 (00000010) due to the second position is 1 for both numbers.

In Java the operation symbol for Bit And is "&". For example,

             if ((theNumber & 2) == 2) {
                     . . . . . .
             }

In SQL the operation symbol for Bit And is "bitand". For example,

              SELECT name, price
              FROM fruit
              WHERE bitand (criteria, 8) = 8;

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

                        
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.

net.sf.dynamicreports.report.exception.DRException: net.sf.jasperreports.engine.design.JRValidationException: Report design not valid : 1. java.lang.ClassNotFoundException: [L;

The following code throws such an exception. The line of code that causes the exception is marked in yellow. The reason that this exception occurs is that the program uses a list of beans instead an array of beans to hold the data of the subreport. Therefore, to fix this problem, you just need to change the "exp.subDatasourceBeanArray" to "exp.subDatasourceBeanCollection" in the marked code below.

import java.util.ArrayList;
import java.util.List;
import net.sf.dynamicreports.jasper.builder.JasperReportBuilder;
import static net.sf.dynamicreports.report.builder.DynamicReports.cmp;
import static net.sf.dynamicreports.report.builder.DynamicReports.col;
import static net.sf.dynamicreports.report.builder.DynamicReports.exp;
import static net.sf.dynamicreports.report.builder.DynamicReports.report;
import static net.sf.dynamicreports.report.builder.DynamicReports.stl;
import net.sf.dynamicreports.report.builder.component.SubreportBuilder;
import net.sf.dynamicreports.report.builder.datatype.DataTypes;
import net.sf.dynamicreports.report.constant.HorizontalAlignment;
import net.sf.dynamicreports.report.constant.StretchType;
import net.sf.dynamicreports.report.exception.DRException;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;

public class PageSubreportTest {
    public static void main(String[] args){
        new PageSubreportTest();
    }
 
    public PageSubreportTest() {
        JasperReportBuilder report = build();
        try {
            report.show();
        }catch (DRException e){
            e.printStackTrace();
        }
    }
 
    private JasperReportBuilder build() {
        JasperReportBuilder report = report();
        report.setDataSource(createDataSource());
     
        SubreportBuilder storeSubreport = cmp.subreport(buildStoreSubReport())
                .setDataSource(exp.subDatasourceBeanCollection("storeData"));
        SubreportBuilder fruitSubreport = cmp.subreport(buildFruitSubReport())
                .setDataSource(exp.subDatasourceBeanArray("fruitData"));
     
        report.detail(cmp.horizontalList(cmp.horizontalGap(50),storeSubreport, cmp.horizontalGap(50)),
                cmp.verticalGap(15),
                //To set the border for the fruitSubreport
                //put it in a horizontallist and then set the border
                cmp.horizontalList(fruitSubreport)
                        .setStretchType(StretchType.RELATIVE_TO_TALLEST_OBJECT)
                        .setStyle(stl.style().setBorder(stl.pen1Point())));
                //For page subreport, set the page break at the end.
//                cmp.pageBreak());
        report.title(cmp.text("Fuit Stores Report")
            .setStyle(stl.style().bold()
                    .setHorizontalAlignment(HorizontalAlignment.CENTER)));
        report.pageFooter(cmp.pageXofY());
     
        return report;
    }
 
    private JasperReportBuilder buildStoreSubReport(){
        JasperReportBuilder storeReport = report();
        storeReport.columns(col.column("", "storeInfo", DataTypes.stringType()));
     
        return storeReport;
    }
 
    private JasperReportBuilder buildFruitSubReport(){
        JasperReportBuilder fruitReport = report();
        fruitReport.columns(col.column("Name", "fruitName", DataTypes.stringType()),
                col.column("Price", "fruitPrice", DataTypes.doubleType())
                    .setPattern("$#,###,##0.00")
                    .setHorizontalAlignment(HorizontalAlignment.LEFT),
                col.column("Description", "des", DataTypes.stringType()));
     
        fruitReport.setColumnTitleStyle(stl.style().bold());
        return fruitReport;
    }
 
    private JRBeanCollectionDataSource createDataSource() {
        List<ReportData> dataSource = new ArrayList<ReportData>();

        ReportData data = new ReportData();
        List<StoreData> storeList = new ArrayList<StoreData>();
        List<FruitData> fruitList = new ArrayList<FruitData>();
        //first set of data
        StoreData storeData = new StoreData();
        storeData.setStoreInfo("Delicious Fruit");
        storeList.add(storeData);
        storeData = new StoreData();
        storeData.setStoreInfo("554 Main Street, Burlington, MN 33596");
        storeList.add(storeData);
        data.setStoreData(storeList);

        FruitData fruitData = new FruitData();
        fruitData.setFruitName("Apple");
        fruitData.setFruitPrice(1.98d);
        fruitData.setDes("Medium juicy");
        fruitList.add(fruitData);
        fruitData = new FruitData();
        fruitData.setFruitName("Orange");
        fruitData.setFruitPrice(0.98d);
        fruitData.setDes("Very juicy");
        fruitList.add(fruitData);
        data.setFruitData(fruitList);
        dataSource.add(data);

        //second set of data
        data = new ReportData();
        storeList = new ArrayList<StoreData>();
        fruitList = new ArrayList<FruitData>();

        storeData = new StoreData();
        storeData.setStoreInfo("Fruit Outlet");
        storeList.add(storeData);
        storeData = new StoreData();
        storeData.setStoreInfo("99 Uphill Ave., Rochester, MN 33596");
        storeList.add(storeData);
        data.setStoreData(storeList);

        fruitData = new FruitData();
        fruitData.setFruitName("Papaya");
        fruitData.setFruitPrice(2.98d);
        fruitData.setDes("Medium juicy");
        fruitList.add(fruitData);
        fruitData = new FruitData();
        fruitData.setFruitName("Mango");
        fruitData.setFruitPrice(3.98d);
        fruitData.setDes("Medium juicy");
        fruitList.add(fruitData);
        data.setFruitData(fruitList);
        dataSource.add(data);

        return new JRBeanCollectionDataSource(dataSource);
    }
 
    public class ReportData {
        private List<StoreData> storeData;
        private List<FruitData> fruitData;
     
        public List<StoreData> getStoreData() { return storeData; }
        public void setStoreData(List<StoreData> s) { storeData = s; }
        public List<FruitData> getFruitData() { return fruitData; }
        public void setFruitData (List<FruitData> s) { fruitData = s; }
    }
 
    public class StoreData {
        private String storeInfo;
     
        public String getStoreInfo() { return storeInfo; }
        public void setStoreInfo(String s) { storeInfo = s; }
    }
 
    public class FruitData {
        private String fruitName;
        private Double fruitPrice;
        private String des;
     
        public String getFruitName() { return fruitName; }
        public void setFruitName(String s) { fruitName = s; }
        public Double getFruitPrice() { return fruitPrice; }
        public void setFruitPrice(Double s) { fruitPrice = s; }
        public String getDes() { return des; }
        public void setDes(String s) { des = s; }
    }
}

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

                        
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.

Thursday, August 21, 2014

DynamicReports Page subreport - create subreport in a page of the parent report and set report/subreport/component border

One way to create a page with different subreports is to list the subreports in the detail of the report and add a page break at the end of the list.

To create a border of a subreport, you can put the subreport in a HorizontalList or a VerticalList and add a border to this component.

import java.util.ArrayList;
import java.util.List;
import net.sf.dynamicreports.jasper.builder.JasperReportBuilder;
import static net.sf.dynamicreports.report.builder.DynamicReports.cmp;
import static net.sf.dynamicreports.report.builder.DynamicReports.col;
import static net.sf.dynamicreports.report.builder.DynamicReports.exp;
import static net.sf.dynamicreports.report.builder.DynamicReports.report;
import static net.sf.dynamicreports.report.builder.DynamicReports.stl;
import net.sf.dynamicreports.report.builder.component.SubreportBuilder;
import net.sf.dynamicreports.report.builder.datatype.DataTypes;
import net.sf.dynamicreports.report.constant.HorizontalAlignment;
import net.sf.dynamicreports.report.constant.StretchType;
import net.sf.dynamicreports.report.exception.DRException;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;

public class PageSubreportTest {
    public static void main(String[] args){
        new PageSubreportTest();
    }
 
    public PageSubreportTest() {
        JasperReportBuilder report = build();
        try {
            report.show();
        }catch (DRException e){
            e.printStackTrace();
        }
    }
 
    private JasperReportBuilder build() {
        JasperReportBuilder report = report();
        report.setDataSource(createDataSource());
     
        SubreportBuilder storeSubreport = cmp.subreport(buildStoreSubReport())
                .setDataSource(exp.subDatasourceBeanCollection("storeData"));
        SubreportBuilder fruitSubreport = cmp.subreport(buildFruitSubReport())
                .setDataSource(exp.subDatasourceBeanCollection("fruitData"));
     
        report.detail(cmp.horizontalList(cmp.horizontalGap(50),storeSubreport, cmp.horizontalGap(50)),
                cmp.verticalGap(15),
                //To set the border for the fruitSubreport
                //put it in a horizontallist and then set the border
                cmp.horizontalList(fruitSubreport)
                        .setStretchType(StretchType.RELATIVE_TO_TALLEST_OBJECT)
                        .setStyle(stl.style().setBorder(stl.pen1Point())),
                //For page subreport, set the page break at the end.
                //Otherwise, it will become detail subreport.
              cmp.pageBreak());
        report.title(cmp.text("Fuit Stores Report")
            .setStyle(stl.style().bold()
                    .setHorizontalAlignment(HorizontalAlignment.CENTER)));
        report.pageFooter(cmp.pageXofY());
     
        return report;
    }
 
    private JasperReportBuilder buildStoreSubReport(){
        JasperReportBuilder storeReport = report();
        storeReport.columns(col.column("", "storeInfo", DataTypes.stringType()));
     
        return storeReport;
    }
 
    private JasperReportBuilder buildFruitSubReport(){
        JasperReportBuilder fruitReport = report();
        fruitReport.columns(col.column("Name", "fruitName", DataTypes.stringType()),
                col.column("Price", "fruitPrice", DataTypes.doubleType())
                    .setPattern("$#,###,##0.00")
                    .setHorizontalAlignment(HorizontalAlignment.LEFT),
                col.column("Description", "des", DataTypes.stringType()));
     
        fruitReport.setColumnTitleStyle(stl.style().bold());
        return fruitReport;
    }
 
    private JRBeanCollectionDataSource createDataSource() {
            List<ReportData> dataSource = new ArrayList<ReportData>();
     
            ReportData data = new ReportData();
            List<StoreData> storeList = new ArrayList<StoreData>();
            List<FruitData> fruitList = new ArrayList<FruitData>();

            //first set of data
            StoreData storeData = new StoreData();
            storeData.setStoreInfo("Delicious Fruit");
            storeList.add(storeData);
            storeData = new StoreData();
            storeData.setStoreInfo("554 Main Street, Burlington, MN 33596");
            storeList.add(storeData);
            data.setStoreData(storeList);
         
            FruitData fruitData = new FruitData();
            fruitData.setFruitName("Apple");
            fruitData.setFruitPrice(1.98d);
            fruitData.setDes("Medium juicy");
            fruitList.add(fruitData);
            fruitData = new FruitData();
            fruitData.setFruitName("Orange");
            fruitData.setFruitPrice(0.98d);
            fruitData.setDes("Very juicy");
            fruitList.add(fruitData);
            data.setFruitData(fruitList);
            dataSource.add(data);
         
            //second set of data
            data = new ReportData();
            storeList = new ArrayList<StoreData>();
            fruitList = new ArrayList<FruitData>();
         
            storeData = new StoreData();
            storeData.setStoreInfo("Fruit Outlet");
            storeList.add(storeData);
            storeData = new StoreData();
            storeData.setStoreInfo("99 Uphill Ave., Rochester, MN 33596");
            storeList.add(storeData);
            data.setStoreData(storeList);
         
            fruitData = new FruitData();
            fruitData.setFruitName("Papaya");
            fruitData.setFruitPrice(2.98d);
            fruitData.setDes("Medium juicy");
            fruitList.add(fruitData);
            fruitData = new FruitData();
            fruitData.setFruitName("Mango");
            fruitData.setFruitPrice(3.98d);
            fruitData.setDes("Medium juicy");
            fruitList.add(fruitData);
            data.setFruitData(fruitList);
            dataSource.add(data);
           
            return new JRBeanCollectionDataSource(dataSource);
    }
 
    public class ReportData {
        private List<StoreData> storeData;
        private List<FruitData> fruitData;
     
        public List<StoreData> getStoreData() { return storeData; }
        public void setStoreData(List<StoreData> s) { storeData = s; }
        public List<FruitData> getFruitData() { return fruitData; }
        public void setFruitData (List<FruitData> s) { fruitData = s; }
    }
 
    public class StoreData {
        private String storeInfo;
     
        public String getStoreInfo() { return storeInfo; }
        public void setStoreInfo(String s) { storeInfo = s; }
    }
 
    public class FruitData {
        private String fruitName;
        private Double fruitPrice;
        private String des;
     
        public String getFruitName() { return fruitName; }
        public void setFruitName(String s) { fruitName = s; }
        public Double getFruitPrice() { return fruitPrice; }
        public void setFruitPrice(Double s) { fruitPrice = s; }
        public String getDes() { return des; }
        public void setDes(String s) { des = s; }
    }
}

           Previous <
       
-----------------------------------------------------------------------------------------------------

                        
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.

References:
1. DynamicReports: Row Subreport - create a subreport in each row of the parent report
2. DynamicReports: Column Subreport - create subreport in a column of the parent report

Wednesday, August 20, 2014

How to add jars to the class path of your main application when you are doing a distribution/software release

The main application contains the main method that serves as the entry point for running your application. In the top directory of your main application, there is a file named manifest.mf. The file looks like the following.

          Manifest-Version: 1.0
          Class-Path:  . ojdbc14.jar
          Created-By: NetBeans IDE
          Main-Class: com.goodfeeling.Application

List all the jars required by your application in the Class-Path entry in the manifest.mf file. The jar file names are separated by a space, which would look like this.
 
         Class-Path:  . ojdbc14.jar  commons-logging.jar log/log4j.jar your-application-helper.jar

The path entered here corresponding to the location of the jar file relative to your main application. For example if your main application is at C:/Application/MainApplication.jar, the ojdbc14.jar would be located at C:/Application/ojdbc14.jar, and the log4j.jar would be located at C:/Application/log/log4j.jar.

Save this manifest.mf file and make a jar for your main application which would have this file included in the jar file. Distribute the jar together with all the other jars required for running your application and listed in the above Class-Path to the users.

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

                        
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.

Tuesday, August 19, 2014

ORA-00979: not a GROUP BY expression

This error happens when the GROUP BY clause does not include all the fields in the SELECT and ORDER BY clauses.

For example, the following queries throw the "not a GROUP BY expression" error.

1. SELECT name, cost, customerID from ORDER GROUP BY customerID;

2. SELECT name, cost, customerID from ORDER GROUP BY customerID, name, cost ORDER BY orderID;

To fix this error, you may either add all the fields in the SELECT and ORDER BY clauses to the GROUP BY clause or completely remove the GROUP BY clause from the query or remove whatever is not in the GROUP BY clause from the SELECT and the ORDER BY clauses whichever serves you better.

To fix the above two queries, you may do the following modification.

1. SELECT name, cost, customerID from ORDER GROUP BY customerID, name, cost;

2. SELECT name, cost, customerID from ORDER GROUP BY customerID, name, cost, orderID ORDER BY orderID;

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

                        
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.

Wednesday, August 6, 2014

java.sql.SQLException: ORA-00904: "column": invalid identifier

This exception occurs in several situations.

1. When GROUP BY clause is used in a SQL, the column names in the GROUP BY clause have to be real column names in the selected table even though alias are used.

For example, the following SQL gives the "invalid identifier" exception.

SELECT nvl(p1.NAMELAST, ' ') "NAMELAST1", nvl(p1.NAMEFIRST, ' ') "NAMEFIRST1", nvl(p1.NAMEMIDDLE, ' ') "NAMEMIDDLE1" FROM PERSON p1 GROUP BY NAMELAST1, NAMEFIRST1, NAMEMIDDLE1;

ERROR at line 1:
ORA-00904: "NAMEMIDDLE1": invalid identifier

The following 2 SQLs both work fine.

SELECT nvl(p1.NAMELAST, ' ') "p1.NAMELAST", nvl(p1.NAMEFIRST, ' ') "p1.NAMEFIRST", nvl(p1.NAMEMIDDLE, ' ') "p1.NAMEMIDDLE" FROM PERSON p1 GROUP BY p1.NAMELAST, p1.NAMEFIRST, p1.NAMEMIDDLE;

SELECT nvl(p1.NAMELAST, ' ') NAMELAST, nvl(p1.NAMEFIRST, ' ') NAMEFIRST, nvl(p1.NAMEMIDDLE, ' ') NAMEMIDDLE FROM PERSON p1 GROUP BY NAMELAST, NAMEFIRST, NAMEMIDDLE;

2. The column name used is invalid.

A valid column name must meet the following criteria

  • Cannot be a reserved word such as ALL, DATE, and COMMENT.
  • Must start with a letter
  • Consists only alphanumeric characters and these special characters: $, _, #
  • The column name must be enclosed in double quotation marks if other characters are used.


For example the following SQL may generate the "invalid identifier" exception.

CREATE table person (
          personID          NUMBER,
          NAME             VARCHAR(30),
          BORN@          DATE
);

3. Mix INNER JOIN and WHERE clauses.

The following SQL will throw such an exception.

SELECT count(*)
FROM PERSON p, SINGER a
INNER JOIN DANCER b on b.ID = p.PERSONID
WHERE a.ID = p.PERSONID;

The following is odd but works.

SELECT count(*)
FROM PERSON p
INNER JOIN DANCER b on b.ID = p.PERSONID,
SINGER a
WHERE a.ID = p.PERSONID;

4. When double quotation marks are used for column names.

SQL by default is case insensitive for column names. However, if the column names are enclosed in double quotation marks and contains lower case characters, you have to use the double quotation marks and the lower case characters whenever the column name is referenced.

For example, the following throws such an "invalid identifier" exception.

CREATE table person (
          "person_ID"          NUMBER,
          NAME             VARCHAR(30),
          "BORN@"          DATE
);

SELECT NAME from PERSON where person_ID = 5;

ERROR at line 1:
ORA-00904: "PERSON_ID": invalid identifier

The following works fine.

          SELECT NAME from PERSON where "person_ID" = 5;

However, it is better not to use double quotation marks for column names.

          Previous <

-----------------------------------------------------------------------------------------------------
          
                        
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.