Forum OpenACS Q&A: CRM subselect?

Collapse
Posted by Rocael Hernández Rizzardini on
Hi!

I have been trying to add one state trasition in the CRM, but I always
get an error saying: Database operation "1row" failed (exception NSDB,
"Query was not a statement returning rows.")

the addition to state transition is this:

user_id = (select creation_user from chat_msgs where
to_char(creation_date, 'HH24:MI:SS') > '10:30:00' and
to_char(creation_date, 'HH24:MI:SS') < '14:30:00' group by
creation_user having count(creation_user) > 10)


if I do this directly from psql:
update users
set crm_state = 'one state', crm_state_entered_date = sysdate()
where crm_state = 'other state'
and (
user_id = (select creation_user from chat_msgs where
to_char(creation_date, 'HH24:MI:SS') > '10:30:00' and
to_char(creation_date, 'HH24:MI:SS') < '14:30:00' group by
creation_user having count(creation_user) > 1)
);

I got this message:
ERROR:  More than one tuple returned by a subselect used as an
expression.

What could be wrong??

Thank you for the help!

Collapse
2: Response to CRM subselect? (response to 1)
Posted by Don Baccus on
Huh, looks like it should be "user_id in (select ... " and that this probably existed in the source ACS Classic code that we based the port  on.

Try that and see if it does what you expect and hey, Roberto, wanna take a look too?

Collapse
3: Was the IN (response to 1)
Posted by Rocael Hernández Rizzardini on
Thank you Don...
was the "in" what I needed, well not me, the query =)