Re: User-Defined Variables

Don't forget to cc: the list

Daniel Futerman wrote:
>>> What is the script trying to do (in a wider sense)?
>
> The variable is used as follows:
>
> SET @OTHER_CONCEPT_ID = (SELECT `concept_id` FROM `concept_name` where name
> = 'MRO' LIMIT 1);
>
> (SELECT
> COALESCE(f2.concept_id, @OTHER_CONCEPT_ID ) as 'concept_id'
> FROM
> `field` f, `field` f2, `form_field` ff, `form_field` ff2, `form`
> WHERE
> form.form_id = ff.form_id AND
> ff.field_id = f.field_id AND
> f.concept_id = obs.concept_id AND
> ff.parent_form_field = ff2.form_field_id AND
> ff2.field_id = f2.field_id
> LIMIT 1
> );

I don't see what this gets you that a subquery / join doesn't.

SELECT
COALESCE(f2.concept_id, default_concept.concept_id) AS concept_id
FROM
...
,(SELECT concept_id FROM concept_name WHERE name='MRO') AS
default_concept
WHERE

I've left the LIMIT 1 off since I'm assuming name is unique - if not the
LIMIT 1 doesn't make any sense without an ORDER BY too.

--
Richard Huxton
Archonet Ltd