Forum OpenACS Q&A: Subject Administration

Collapse
Posted by hau wan lin on
I want to department administrator to take care of the subject
administration, but it always fail.

I guess the error come from this sql statment in file

/education/subject/admin/index.tcl

select count(dept.department_id)
                                from user_group_map map,
                                    edu_departments dept
                              where map.group_id = dept.department_id
                                and map.user_id = $user_id
                                and dept.department_id not in (select
                                          sdmap.department_id
                                          from
edu_subject_department_map sdmap
                                          where sdmap.subject_id =
$subject_id)
                            order by lower(department_name)

Then, I change the order by lower(....) to group by .... But, I still
face the problem with different error message.

This is the error message for order by:

Error: Ns_PgExec: result status: 7 message: ERROR:  Attribute
dept.department_name must be GROUPed or used in an aggregate function

This the error message for group by:

Error: Database operation "1row" failed (exception NSINT, "Query did
not return a row.")
Database operation "1row" failed (exception NSINT, "Query did not
return a row.")
    while executing
"ns_db 1row $db [db_sql_prep $sql]"
    invoked from within
"set selection [ns_db 1row $db [db_sql_prep $sql]]..."
    invoked from within
"if { $no_prep == 1 } {
        set selection [ns_db 1row $db $sql]
    } else {
        set selection [ns_db 1row $db [db_sql_prep $sql]]
    }"
    (procedure "database_to_tcl_string" line 3)
    invoked from within
"database_to_tcl_string $db "select count(dept.department_id)
                                from user_group_map map,
                                ..."
  invoked from within
"set n_other_departments [database_to_tcl_string $db "select
count(dept.department_id)
                                from user_group_map map,
        ..."
    invoked from within
"if { $site_wide_admin_p == 1 } {
    set n_other_departments [database_to_tcl_string $db "select
count(department_id) from edu_departments"]
} else {
..."
    (file "/web/server1/www/education/subject/admin/index.tcl" line
208)
    invoked from within
"source [ns_info pageroot][ns_conn url]$file_extension"
    invoked from within
"if {[file exists [ns_info pageroot][ns_conn url]$file_extension]}
{
        source [ns_info pageroot][ns_conn url]$file_extension
        return
  } else {
        set e ..."
    (procedure "edu_serve_util_pages" line 23)
    invoked from within
"edu_serve_util_pages"

Collapse
Posted by Don Baccus on
Try something like this:

select lower(department_name) as foo ...
order by foo;

Then submit a bug report to the SDM.  You're probably the first serious user of the education module and are wandering into untested code.  We've made an effort to test ported modules (obviously), but we're just a bunch of unpaid volunteers and can't claim to have done an overly thorough job except on the most commonly used modules.

Collapse
Posted by hau wan lin on
I have tested the sql statement in pgsql promplt. it works, but it return with 0 row because no record from that sql statement. I guess something go wrong with OpenACS when a query doesn't return a row. Error: Database operation "1row" failed (exception NSINT, "Query did not return a row.") Database operation "1row" failed (exception NSINT, "Query did not return a row.") while executing "ns_db 1row $db [db_sql_prep $sql]" invoked from within "set selection [ns_db 1row $db [db_sql_prep $sql]]..." invoked from within "if { $no_prep == 1 } { set selection [ns_db 1row $db $sql] } else { set selection [ns_db 1row $db [db_sql_prep $sql]] }" (procedure "database_to_tcl_string" line 3) invoked from within "database_to_tcl_string $db "select count(dept.department_id) from user_group_map map, ..." invoked from within "set n_other_departments [database_to_tcl_string $db "select count(dept.department_id) Can I know the rigth answer? Thank you for helping me.