Solved

sqlGetInt queryDef Equivalent

Forum|Forum|4 years ago
March 24, 2021
1 reply
1162 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Jonathon_wodnicki

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

Jonathon_wodnicki

Accepted solution

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

J

JamesAlioAuthor

Thank you so much

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded