Forum OpenACS Development: Re: Problem with the Oracle database

Collapse
Posted by Rodrigo C on
Here is an other problem, im doing the problem sets of the education section in OpenACS. A query like this runs well:

db_multirow reporte cosilla "select rm.room_name as room_name, trunc(sum(to_date(to_char(rv.end_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI') -
to_date(to_char(rv.start_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI')),4) as hours_reserved,
trunc(avg(rv.people),4) as people_avg  from reserve rv, rooms rm where rm.room_id=rv.room_id group by room_name"

I now it is a bit heavy, but it is the only way I found to work with timestamps ;)

But in the .adp I need the room_id, so I add in the query:
....select rm.room_id ,rm.room_name as .....

But it is imposible to get the room_id in the multirow, it throws this:
nsoracle.c:3904:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00918: column ambiguously defined

SQL: select !>>>!room_id,rm.room_name as room_name,  trunc(sum(to_date(to_char(rv.end_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI') -  to_date(to_char(rv.start_time,'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI')),4) as hours_reserved,  trunc(avg(rv.people),4) as people_avg  from reserve rv, rooms rm where rm.room_id=rv.room_id group by room_name
    while executing
"ns_ora select nsdb0 {select ...