?
Solved

Insert into Oracle table column with permanent value

Posted on 2011-10-03
10
Medium Priority
?
656 Views
Last Modified: 2013-11-11
I need to add column which will have permanent string value.
I have tables which have two columns each one, when I execute SELECT from all of them I need to have identification column.
Let's say this sample of query so I need to add column SYMBOL, so I can separate these data into array by this way.
 I know that I can make trigger and insert EUR/USD and other symbols each time when inserting value in specified table.
But I need to create 50 triggers by this way.
Please advice
0
Comment
Question by:gbcbr
8 Comments
 
LVL 13

Expert Comment

by:stergium
ID: 36902601
you can alter the query by adding a 'eur/usd' symbol column like this
  //          "SELECT 'whatever you want to show here' SYMBOL, x.* FROM EURUSD_BOT_SELL  x where timest = ( select max( timest ) from EURUSD_BOT_SELL ) and rownum = 1 union all " +...
hope that helps
0
 

Author Comment

by:gbcbr
ID: 36902624
No, this will complicate my code.
Main question is to create two arrays according to currency pairs and use pair symbol as identification, so I will only add to the existing query:
 
String s = data.getSymbol();
double bpx = data.getOpenBid();
String bb = data.getBotBuy();

if (s.equals("EUR/USD")) {
openBid[0] = bpx;

botBuy[0] = bb;

Open in new window

and I will have as much arrays as I need.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36921917
 if it's a value needed at insert just use column default values  - no need for triggers or default values



alternately,  use a table with lookups,  can you give an example of what you're trying to do?

inputs and expected outputs
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gbcbr
ID: 36922553
When I created these tables, I'm especially make them separated by names to see separated data in each table.
public void statistic_EURUSD_S(boolean bl0) throws SQLException,
                                                       ClassNotFoundException,
                                                       InstantiationException,
                                                       IllegalAccessException {

        if (bl0 == false) {
            ts = new java.util.Date();
            sell_px[0] = DCC.getInstance().getSellPriceEURUSD();
            System.out.println(" EUR/USD Sell signal  at  " + ts +
                               "  : with price =  " + sell_px[0]);

            lionconn.insertBotResult(sell_px, buy_px, ts);
        } else {

        }
    }

Open in new window

CREATE TABLE "LIONFX"."EURUSD_BOT_BUY"
  (
    "BUY_PX" VARCHAR2(20 BYTE),
    "TS" TIMESTAMP (6) NOT NULL ENABLE,............

Open in new window

At the moment I need to use this tables for charts creating together with with another table, but this table full and has all columns
CREATE TABLE "LIONFX"."EURUSD"
  (
    "ID"     NUMBER NOT NULL ENABLE,
    "SYMBOL" VARCHAR2(20 BYTE),
    "BIDPX" FLOAT(126),
    "ASKPX" FLOAT(126),
    "TIMEST" TIMESTAMP (0),.......

Open in new window

So, I want to use the same query
String s = data.getSymbol();
double bpx = data.getOpenBid();
String bb = data.getBotBuy();

if (s.equals("EUR/USD")) {
openBid[0] = bpx;

botBuy[0] = bb;

Open in new window

where String bb will be value from BUY_PX column
0
 

Author Comment

by:gbcbr
ID: 36922560
@sdstuber
of course I can change application code and insert "EUR/USD" value, but this is big enough job for 50 tables.
So, I just try to minimize my job. This reason why I suppose that much easy to insert in each table just one more column with permanent value.
Thank you
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 1000 total points
ID: 36922719
Hi,
You can:
 - add a column with DEFAULT and if the column is not referenced in the insert, the it will put the default value. But you must make sure that the application to not put some value (nor null) into it
- or use virtual columns in 11g
- or create a view that adds that column, and query on that view instead
Regards,
Franck.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 36923902
>>> of course I can change application code and insert "EUR/USD" value, but this is big enough job for 50 tables.

if you use default values, then there is no application code change.  that's whole point.
0
 

Author Closing Comment

by:gbcbr
ID: 36924339
this is exactly what I'm asking for. Thank you
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question