Expand my Community achievements bar.

SOLVED

Querydef using IN operator in Where clause

Avatar

Level 2

Hi,

 

I'm trying to get data from a schema using the following query but it doesn't work. Because it doesn't recognize the IN operator. Is there another way to do this?  My goal is to calculate the maximum sales from each product. So after this i want to do a calculation but first i need the data...

 

var query = xtk.queryDef.create(
  <queryDef schema={vars.targetSchema} operation="select" distinct="true">
    <select>
      <node expr="@id"/>  
    </select>
  </queryDef>);

var results = query.ExecuteQuery();

var primarykeys = []

for each (var row in results){
primarykeys.push(row.@id)
}
logInfo(vars.primarykeys)
	
	var querys = xtk.queryDef.create(
    <queryDef schema="cus:products" operation="select">
        <select>
            <node expr="@sales"/>
            <node expr="@productID"/>
        </select>
        <where>
            <condition expr={"@RecipientId " in "'"+primarykeys+"'"}/>
        </where>
    </queryDef>
    )

var result = querys.ExecuteQuery()

Thank you in advance 

 

Kind regards, 

J

1 Accepted Solution

Avatar

Correct answer by
Level 7

Hi,

Try as below, this should work

<condition expr={"@RecipientId IN ('"+primarykeys+"')"}/>

Thanks,

SSB

View solution in original post

5 Replies

Avatar

Community Advisor

Hello @J2021,

 

Your condition should be something like : 

<condition expr={"@RecipientId in ("+primarykeys+")"}/>

Br,

 

Amine

Avatar

Level 2

Hi @Amine_Abedour,

 

I tried and i'm getting this error back: 

21/07/2022 17:01:51 js23 JST-310000 Error while compiling script 'WKF26806/js23' line 39: invalid 'in' operand.

 

Kind regards,

J

Avatar

Correct answer by
Level 7

Hi,

Try as below, this should work

<condition expr={"@RecipientId IN ('"+primarykeys+"')"}/>

Thanks,

SSB

Avatar

Community Advisor

Hi,

If primarykeys is an Array, I suggest this :

<condition expr={"@RecipientId IN ('" + primarykeys.join("','") + "')"}/>

 

Cedric

Avatar

Administrator

Hi @J2021,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!



Sukrity Wadhwa