Board index » jbuilder » error saving data back to database.

2005-07-09 05:48:29 AM
jbuilder17
Hi there,
I received the following error when I tried to save changed value back to
database. I am using
Jbuilder X and build-in tableScrollPane1, jdbTable1, queryDataSet1 standard
stuffs.
com.borland.dx.sql.dataset.ResolutionException: An error occurred saving a
change to the "Ressim_hourly_view" table
table: ressim_hourly
sensor_id number(5) not null,
date_time date not null,
run_time date not null,
value number(12,3),
flag char(1),
queryDataSet1 contains the following SQL query.
select date_time, max(decode(sensor_id, 6, value, NULL)) as value1,
max(decode(sensor_id,76, value, NULL)) as value2,
max(decode(sensor_id, 20, value, NULL)) as value3
from ressim_hourly
where (sensor_id, run_time) in (select sensor_id, max(run_time)
from ressim_hourly
where sensor_id in (6, 76, 20)
group by sensor_id
)
group by date_time
This will produce the following output:
date_time value1 value2 value3
07-02-2005 01:00 1000 2000 3000
...
07-02-2005 23:00 1000 2000 3000
Thanks
Tom
 

Tom,
I take it that "value1", "value2", and "value3" are not fields in the
database.. Therefore you have to set them as "non-resolvable"
My first guess is that is{*word*154} up the resolution..
tom wrote:
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/paperFAQ.html
====================================================
 

If I set the column to non-resolvable. I can't save any of the changes back
to the database at all.
Any cue as to why that's the case?
Tom
"tom" < XXXX@XXXXX.COM >wrote in message

{smallsort}

That makes sense..
What does not make sense is what you expect to "save" back from a query
that gets the Max() from those columns.
I honestly don't see what it is you are saving back and to what record
(primary key field) you are expecting it to be saved to...????
John..
tom wrote:
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/paperFAQ.html
====================================================
 

John,
Basically, I am trying to do a self-join on the database table
(ressim_hourly).
I would like to be able to save any changes that made to value1, value2 and
value3 back to the database table.
each one of the values is associated with a sensor_id key, date_time and
run_time ; therefore, each value should be
retrievable via lookup of sensor_id, date_time and run_time.
The max() from those columns were to retrieve the latest copy of data. If I
were to write a SQL query with out the max() function, it would return many
duplicate records.
table: ressim_hourly
sensor_id number(5) not null,
date_time date not null,
run_time date not null,
value number(12,3),
flag char(1));
raw data:
There are 24 entries per 24 hours daily.
sensor_id date_time run_time value flag
6 1/1/2005 1:00 1/1/2005 14:00 1000 Y
6 1/1/2005 2:00 1/1/2005 14:00 1000
6 1/1/2005 3:00 1/1/2005 14:00 1000
6 1/1/2005 4:00 1/1/2005 14:00 1000
..
6 1/1/2005 23:00 1/1/2005 15:00 1000 Y <-----this
value will be retrieved by the query
76 1/1/2005 1:00 1/1/2005 23:00 2000 Y
...
76 1/1/2005 23:00 1/2/2005 14:00 2000 Y <-----this
value will be retrieved by the query
20 1/1/2005 1:00 1/1/2005 12:00 3000 Y
....
20 1/1/2005 23:00 1/2/2005 14:00 3000 Y <-----this
value will be retrieved by the query
Parameterized query:
:sensor1 = 6
:sensor2 = 76
:sensor3 = 20
select sensor_id, date_time, max(decode(sensor_id, :sensor1, value,
NULL)) as value1,
max(decode(sensor_id,:sensor2, value, NULL)) as value2,
max(decode(sensor_id, sensor3, value, NULL)) as value3
from ressim_hourly
where (sensor_id, run_time) in (select sensor_id, max(run_time)
from ressim_hourly
where sensor_id in (:sensor1, :sensor2,
:sensor3)
group by sensor_id
)
group by date_time
Tom
"John Moore" < XXXX@XXXXX.COM >wrote in message

Well, your SQL ends up with a table structure as..
sensor_id, date_time, value1, value2, value3
...and you are expecting it to be saved back to a table structure as
sensor_id, date_time run_time value flag
Do you see the problem...??? (hint, names and fields don't match..)
John...
tom wrote:
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/paperFAQ.html
====================================================