Dynamic SQL query field to order a drop-down list

[Gestor Centro Alfa1 Sevilla]

Hi all.

We want to select from a drop-down list and if the correct option doesn't exist we want to enter it in themselves. We’re trying to do it with Dynamic SQL query field:

 [family_query] = SELECT 'Other' UNION SELECT DISTINCT value FROM redcap_data WHERE project_id = [project-id] AND field_name = 'family'

If the family isn’t in the drop-down list, we must select “Other” and, then record it in another field ([family_other]).

After that, a third field combine  the value of [family_query] and the “Other” field. We do it with a @calctext:

 

@CALCTEXT(if([family_query]='Other',[family_other],[family_query]))

 

Right now, If we drop-down the list, we see first "Other" and then the list of families, but without sorting. The new families we enter are added at the end of the drop-down list.

How can I get the families to be listed in order? We have tried adding "ORDER BY VALUE" to the query but it doesn't work.

Thanks in advance.

[Andrew Arenson]

Change the other of your select statements to get 'Other' after everything else, but I'm not sure why order by isn't working. Perhaps you need to use a subselect to apply the order by:

SELECT *

FROM (

             SELECT DISTINCT value

             FROM redcap_data

             WHERE project_id = [project-id] 

             AND field_name = 'family'

             ORDER BY value ASC

             ) families

UNION

SELECT 'Other'

[Luke Stevens]

“ORDER BY” is the final step, so if you don’t explicitly apply it to the subquery it will apply to the whole lot. That means it looks for column names in the first union statement, and so here does not find one named `value`, so will produce a syntax error:

 

SELECT ‘Other’

UNION

SELECT

                …

ORDER BY value

 

So – as Andy says – your solution is to make the families bit a subquery containing the ORDER BY clause. You can then UNION the ‘Other’ on either at the top or bottom, as you prefer.

 

(And technically you also don’t need DISTINCT because UNION does that automatically.)

 

HTH

Luke

 

From: redca...@googlegroups.com <redca...@googlegroups.com> On Behalf Of Andrew Arenson
Sent: Friday, 17 February 2023 08:06
To: redcap open <redca...@googlegroups.com>
Subject: [EXTERNAL]Re: Dynamic SQL query field to order a drop-down list

 

CAUTION:  External Email. Please be cautious with attachments and clicking links

 

--
You received this message because you are subscribed to the Google Groups "redcap open" group.
To unsubscribe from this group and stop receiving emails from it, send an email to redcap_open...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/redcap_open/fb052a98-cac2-4ae6-bf6f-900bdf81a2d7n%40googlegroups.com.

This e-mail and any attachments to it (the "Communication") are, unless otherwise stated, confidential, may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Murdoch Children’s Research Institute (MCRI) ABN 21 006 566 972 or any of its related entities. MCRI does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication.

[Gestor Centro Alfa1 Sevilla]

Thank you for your help. I will talk to our administrator to make the modifications to the query, as I don't have access to this type of field.

[Gestor Centro Alfa1 Sevilla]

Finally, the administrator has been able to make the proposed changes, but unfortunately it doesn't work. I guess I'm still doing something wrong when setting up the query. These are the two queries I tried,following your instructions:

Query #1:

SELECT 'Otra' UNION (SELECT DISTINCT value FROM redcap_data WHERE project_id = [project-id] AND field_name = 'familia ORDER BY value)

Query#2:

SELECT *

FROM (

             SELECT DISTINCT value

             FROM redcap_data

             WHERE project_id = [project-id] 

             AND field_name = 'familia'

             ORDER BY value ASC

             )

UNION

SELECT 'Otra'

[Luke Stevens]

Query 1 is missing a closing quote for familia string.

Queries 1 and 2 - subqueries require an alias in MySQL

HTH

Luke 

Get Outlook for Android

---

From: redca...@googlegroups.com <redca...@googlegroups.com> on behalf of Gestor Centro Alfa1 Sevilla <gestor.alf...@gmail.com>
Sent: Friday, February 24, 2023 9:48:40 PM
To: redcap open <redca...@googlegroups.com>
Subject: Re: [EXTERNAL]Re: Dynamic SQL query field to order a drop-down list

 

To view this discussion on the web visit https://groups.google.com/d/msgid/redcap_open/9e685711-7c23-4ea9-ba09-9d8134f9994bn%40googlegroups.com.

[Gestor Centro Alfa1 Sevilla]

Yes!, I just realised the mistake in q1.
I'll check what you said about the "alias". I don't know much (almost nothing, actually) about mySQL.
Thanks for the support, Luke. Best regards.