dbaccess syntax

[Monte Heeren]

Trying to do a subquery and keep getting a runtime error:

select wf_model_id, wf_version, wf_key from wf_history where wf_model_id and
wf_version and wf_key in (
select wf_model_id, wf_version, wf_key
from wf_instance
where (wf_status <> 'I' and wf_status <> 'P') and (wf_start > DATE ('12/01/2006') and wf_start < DATE ('12/31/2006')) )



The error is: "A subquery has returned not exactly one column".

What am I doing wrong?

Monte

[Steve Lewis · Accepted solution]

I am pretty sure that you cannot group columns from subqueries using AND;
certainly I have never seen this syntax before in 17 years of SQL, but i may be wrong.

Anyway I think you should re-write the query using a proper join and an alias; also remove the DATE functions because they totally destroy all chance of optimisation.
Something like this:

select h.wf_model_id, h.wf_version, h.wf_key
from wf_history h, wf_instance i
where (i.wf_status <>'I' and i.wf_status<>'P') and (i.wf_start>="12/01/2006") and (i.wf_start<="12/31/2006") and
h.model_id=i.wf_model_id and
h.wf_version=i.wf_version and
h.wf_key=i.wf_key

[Steve Lewis]

Further to the above, what I think you effected was a Boolean logical AND of (wf_model_id and wf_version) similar to &&.

i.e. true if 1 and 1, false if either is zero, NULL and therefore undefined if a character.

Then it went into the subquery expecting a result just for wf_key, but you selected all 3 columns.

Try to use my query above, instead.

[Monte Heeren]

Steve,
Thank you for the syntax. Your way makes sense. The syntax I was using came from a knowledge base from a 3rd party.

When I use your syntax, I get an error:
Non-numeric character in datetime or
interval.

Monte

[Monte Heeren]

Steve,
The field wf_start has a type of datetime year to second.

Monte