How to select DISTINCT(CONCAT(??? in the 'fields to retrieve' section

How to use DISTINCT with CONCAT in a CF database read field selection.

Overview

The issue occurs when writing the SQL function without proper spacing between elements in the 'Fields to retrieve' section.
Add spaces between the DISTINCT, CONCAT, and field names to ensure the query is parsed correctly.

Ni NickOg 03 Dec, 2018

Hi

I want to use

DISTINCT(CONCAT(mdlPublishedTerms.intYear,mdlPublishedTerms.txtTerm)):mdlPublishedTerms.publishedTerm

in a db read 'Fields to retrieve' section.

Or better still in select only records where CONCAT(mdlPublishedTerms.intYear,mdlPublishedTerms.txtTerm) is distinct

Any suggestions gratefully received.

Regards

Nick

he healyhatman 03 Dec, 2018

Answer

Spaces between the elements.

DISTINCT( CONCAT( mdlPublishedTerms.intYear , mdlPublishedTerms.txtTerm)):mdlPublishedTerms.publishedTerm

Ni NickOg 03 Dec, 2018

Ye gods! That easy! Many thanks. My question is - how did you know that? Is there some general rule that I am missing??

Regards

Nick

he healyhatman 03 Dec, 2018

from a glance at a forum post a year ago.

Ni NickOg 07 Dec, 2018

Pushing a bit further - how can I set up WHERE to seelct records with the same id as a previous read??

mdlConvenersInfo.id/IN: ( {var:read_data96.mdlConveners})

That mdlConveners looks like this

How to select DISTINCT(CONCAT(??? in the 'fields to retrieve' section image 1 [array] 0 [array] mdlConveners [string] user_id = "587" [array] 1 [array] mdlConveners [string] user_id = "600" [array] 2 [array] mdlConveners [string] user_id = "605"

I have a feeling that mdlConveners is wrong for the purpose.

Regards

Nick

he healyhatman 07 Dec, 2018

mdlConvenersInfo.id/IN:{var:read_data96.model.field}

Should do it

This topic is locked and no more replies can be posted.