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
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

sqlGetInt queryDef Equivalent

Avatar

Level 2

Hello,

im using this native query and it works fine but i want to know how i can do the Something using queryDef.

this is my native SQL: 

 

 

  var email = NLWS.xtkSession.FormatDataPolicy("lowerCase", email.trim());
  var firstName = NLWS.xtkSession.FormatDataPolicy("lowerCase", firstName);
  var lastName = NLWS.xtkSession.FormatDataPolicy("lowerCase", lastName);

var sql ="SELECT R0.iRecipientId FROM NmsRecipient R0 JOIN XtkFolder F1 ON (F1.iFolderId = R0.iFolderId) WHERE LOWER(R0.sFirstName)='"+firstName+"' AND LOWER(R0.sLastName)='"+lastName+"' AND LOWER(R0.sEmail)='"+email+"' AND F1.sName IN('folder_2','folder_1')";

var idRecipient = sqlGetInt(sql);

 

 

Thnaks in advance.

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi,

 

var recipientId = xtk.queryDef.create(
    <queryDef schema="nms:recipient" operation="getIfExists">
      <select>
      <node expr="@id" />
    </select>
    <where>
      <condition expr={"lower(@email) = '" + NLWS.xtkSession.FormatDataPolicy('lowerCase', email.trim()) + "'"}/>
      <condition expr={"lower(@firstName) = '" + NLWS.xtkSession.FormatDataPolicy('lowerCase', firstName) + "'"}/>
      <condition expr={"lower(@lastName) = '" + NLWS.xtkSession.FormatDataPolicy('lowerCase', lastName) + "'"}/>
      <condition expr="[folder/@name] IN ('folder_2', 'folder_1')"/>
    </where>
  </queryDef>
).ExecuteQuery().@id;

 

Thanks,

-Jon

View solution in original post

2 Replies

Avatar

Correct answer by
Community Advisor

Hi,

 

var recipientId = xtk.queryDef.create(
    <queryDef schema="nms:recipient" operation="getIfExists">
      <select>
      <node expr="@id" />
    </select>
    <where>
      <condition expr={"lower(@email) = '" + NLWS.xtkSession.FormatDataPolicy('lowerCase', email.trim()) + "'"}/>
      <condition expr={"lower(@firstName) = '" + NLWS.xtkSession.FormatDataPolicy('lowerCase', firstName) + "'"}/>
      <condition expr={"lower(@lastName) = '" + NLWS.xtkSession.FormatDataPolicy('lowerCase', lastName) + "'"}/>
      <condition expr="[folder/@name] IN ('folder_2', 'folder_1')"/>
    </where>
  </queryDef>
).ExecuteQuery().@id;

 

Thanks,

-Jon