Expand my Community achievements bar.

SOLVED

Adobe Campaign: How to SELECT DISTINCT on queryDef

Avatar

Level 1

Hi,

We are selecting values from two columns which does not include primary key or unique & binding it to drop down and thus getting multiple values in dropdown.

The code snippet is:

ctx.students = xtk.queryDef.create(

  <queryDef schema="stf:student" operation="select">

    <select>

      <node expr="@firstname"/>

      <node expr="@lastname"/>

    </select>

    <orderBy>

      <node expr="@firstname" sortDesc="false"/>

    </orderBy>

  </queryDef>).ExecuteQuery();

How can we can fetch only DISTINCT records?

Thanks

Sachin

1 Accepted Solution

Avatar

Correct answer by
Level 2

Hi Scahin,

Use the method Countdistinct()

for example:

var query = xtk.queryDef.create(<queryDef schema="nms:broadLogRcp" operation="select">

  <select>        

    <node expr="Countdistinct(@account_id)" alias="total"/>

   </select>

   <where>   

    <condition boolOperator="AND" expr={("@status = '1'")}/> 

   </where>

</queryDef>).ExecuteQuery();

var results = query.broadLogRcp;

logInfo("results count= " +results.total);

This should work...

Happy campaigning

Sumit

View solution in original post

5 Replies

Avatar

Employee

Set the attribute district to "true".

<queryDef schema="stf:student" operation="select" distinct="true">

Avatar

Former Community Member

For me is not working distinct="true" !!!

var query = xtk.queryDef.create(<queryDef schema="nms:broadLogRcp" distinct="true" operation="select">

  <select>         

    <node expr="count(@account_id)" alias="total"/>

   </select>

   <where>   

    <condition boolOperator="AND" expr={("@status = '1'")}/>  

   </where>

</queryDef>).ExecuteQuery();

var results = query.broadLogRcp;

logInfo("results count= " +results.total);

I have the same number for "totalC"  if I run the query with distinct="true" or without it.

Can somebody explain why?

Thank you!

Avatar

Correct answer by
Level 2

Hi Scahin,

Use the method Countdistinct()

for example:

var query = xtk.queryDef.create(<queryDef schema="nms:broadLogRcp" operation="select">

  <select>        

    <node expr="Countdistinct(@account_id)" alias="total"/>

   </select>

   <where>   

    <condition boolOperator="AND" expr={("@status = '1'")}/> 

   </where>

</queryDef>).ExecuteQuery();

var results = query.broadLogRcp;

logInfo("results count= " +results.total);

This should work...

Happy campaigning

Sumit