Java Solutions: Column

Showing posts with label Column. Show all posts

Thursday, January 17, 2019

Solved - Change the data type of a column which has already had data; ORA-01439: column to be modified must be empty to change datatype

Lets say you have a column, Level, in your Product table. The original data type of the column is Varchar. Now, you want to change the data type to Number.

If you simply use "ALTER TABLE Product MODIFY Level NUMBER", you will have the following error.

ORA-01439: column to be modified must be empty to change datatype

To work around this problem, following the following steps.

1. Add a temporary column to the Product table with the required new data type.

ALTER TABLE Product ADD Level_temp NUMBER;

2. Use your data conversion logic, convert the data in column Level to NUMBER and add that value to the corresponding Level_temp column.

UPDATE Product SET Level_temp = <the converted value> where Level = <original value>;

If you don't need to convert the data, you can do the following.
UPDATE Product SET Level_temp = Level;

3. Drop the Level column.

ALTER TABLE Product DROP COLUMN Level;

4. Rename the Level_temp to Level.

ALTER TABLE Product RENAME COLUMN Level_temp TO Level;

-----------------------------------------------------------------------------------------------------------------
Watch the blessing and loving online channel: SupremeMasterTV live

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 for free here.

Monday, February 29, 2016

SQL: How to update a column with values from another table?

Lets say that you have a FRUIT table containing columns: FRUIT_ID, NAME, PRICE, and POPULARITY. The FRUIT_ID is the primary key

And you have another ORDER table having these columns: ORDER_ID, FRUIT_ID, AMOUNT, and TOTAL_PRICE, The ORDER_ID is the primary key. The FRUIT_ID is the foreign key referencing the FRUIT table.

Now, for some reason, you want the ORDER table to have a FRUIT_NAME column. You can use the following SQL to copy the NAME from the FRUIT table to the ORDER table after you add the FRUIT_NAME column to the ORDER table.

UPDATE ORDER ord
SET FRUIT_NAME = (SELECT frt.NAME
FROM FRUIT frt
WHERE frt.FRUIT_ID = ord.FRUIT_ID)
WHERE FRUIT_NAME is null;

To add the FRUIT_ID column to the ORDER table:

ALTER TABLE ORDER
ADD FRUIT_ID VARCHAR2(9);

Monday, December 16, 2013

JTable Freezing/Fixing column headers or rows while scrolling

import java.awt.Dimension;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class TestClass extends javax.swing.JFrame {
private DefaultTableModel tableModel;
private javax.swing.JScrollPane jScrollPane;
private javax.swing.JTable jTable;

public TestClass() {
jScrollPane = new javax.swing.JScrollPane();
jTable = new javax.swing.JTable();
jScrollPane.setViewportView(jTable);
getContentPane().add(jScrollPane);

setPreferredSize(new Dimension(300,120));
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

Object[][] data = {{"Item1", "Papaya", "Letus", "Cashew", "Pine"},
{"Item2", "Orange", "Carrot", "Pine nut", "Oak"},
{"Item3", "Apple", "Pepper", "Pistacho", "Cypress"},
{"Item4", "Mango", "Tomato", "Almond", "Cedar"},
{"Item5", "Pear", "Romman", "Brazil", "Palm"}};
Object[] headers = {"Item", "Fruit", "Vegetable", "Nuts", "Tree"};
tableModel = new DefaultTableModel(data, headers);
jTable.setModel(tableModel);

jTable.setAutoCreateColumnsFromModel( false );
for (int i=0; i<jTable.getColumnCount(); i++){
jTable.getColumnModel().getColumn(i).setMinWidth(100);
}
jTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

jScrollPane.setColumnHeaderView(jTable.getTableHeader());

pack();
}

public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new TestClass().setVisible(true);
}
});
}
}

See also
JTable freezing/fixing columns when scrolling

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

JTable freezing/fixing columns when scrolling

Following is the sample code for freezing one or more table columns while scrolling through the other columns.

import java.awt.Dimension;
import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JViewport;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;
import javax.swing.table.TableColumnModel;

public class TestClass extends javax.swing.JFrame {
private DefaultTableModel tableModel;
private javax.swing.JScrollPane jScrollPane;
private javax.swing.JTable jTable;
private javax.swing.JTable freezeTable;
private int fixedColumns = 1;//number of colums to be freezed

public TestClass() {
jScrollPane = new javax.swing.JScrollPane();
jTable = new javax.swing.JTable();
jScrollPane.setViewportView(jTable);
getContentPane().add(jScrollPane);

setPreferredSize(new Dimension(300,200));
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

Object[][] data = {{"Item1", "Papaya", "Letus", "Cashew", "Pine"},
{"Item2", "Orange", "Carrot", "Pine nut", "Oak"},
{"Item3", "Apple", "Pepper", "Pistacho", "Cypress"}};
Object[] headers = {"Item", "Fruit", "Vegetable", "Nuts", "Tree"};
tableModel = new DefaultTableModel(data, headers);
jTable.setModel(tableModel);

jTable.setAutoCreateColumnsFromModel( false );
for (int i=0; i<jTable.getColumnCount(); i++){
jTable.getColumnModel().getColumn(i).setMinWidth(100);
}
jTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
jTable.addPropertyChangeListener(new PropertyChangeListener() {
public void propertyChange(PropertyChangeEvent e) {
// Keep freezeTable in sync with the jTable
if ("selectionModel".equals(e.getPropertyName())) {
freezeTable.setSelectionModel(jTable.getSelectionModel());
}

if ("dataModel".equals(e.getPropertyName())) {
freezeTable.setModel(jTable.getModel());
}
}
});

freezeTable = new javax.swing.JTable();
freezeTable.setAutoCreateColumnsFromModel(false);
freezeTable.setModel(tableModel);
freezeTable.setSelectionModel(jTable.getSelectionModel());
freezeTable.setFocusable(false);

for (int i = 0; i < fixedColumns; i++) {
TableColumnModel colModel = jTable.getColumnModel();
TableColumn column = colModel.getColumn(0);
colModel.removeColumn(column);
freezeTable.getColumnModel().addColumn(column);
}

//Synchronize sorting of freezeTable with jTable
jTable.setAutoCreateRowSorter(true);
freezeTable.setRowSorter(jTable.getRowSorter());
jTable.setUpdateSelectionOnSort(true);
freezeTable.setUpdateSelectionOnSort(false);

// Add the fixed table to the scroll pane
freezeTable.setPreferredScrollableViewportSize(freezeTable.getPreferredSize());
jScrollPane.setRowHeaderView(freezeTable);
jScrollPane.setCorner(JScrollPane.UPPER_LEFT_CORNER, freezeTable.getTableHeader());

// Synchronize scrolling of the row header with the jTable
jScrollPane.getRowHeader().addChangeListener(new ChangeListener() {
public void stateChanged(ChangeEvent e) {
// Sync the scroll pane scrollbar with the row header
JViewport viewport = (JViewport) e.getSource();
jScrollPane.getVerticalScrollBar().setValue(viewport.getViewPosition().y);
}
});
pack();
}

public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new TestClass().setVisible(true);
}
});
}
}

See also
JTable Freezing/Fixing column headers while scrolling

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