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

sqlGetInt queryDef Equivalent

JamesAlio
Level 2
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
wodnicki
Correct answer by
Community Advisor
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
wodnicki
Correct answer by
Community Advisor
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