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