Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Adobe Campaign: How to SELECT DISTINCT on queryDef

sachind75865377
Level 1
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
Sumit_Saha
Correct answer by
Level 2
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
Philippe_Ferdin
Employee
Employee

Set the attribute district to "true".

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

bianca78
Level 1
Level 1

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!

Sumit_Saha
Correct answer by
Level 2
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