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

Match web application url id with two columns

tejashriw155148
Level 4
Level 4

In my existing web application, when I hit the web application url with id it check id in partyPartId column and display all data related to partyPartId on the dashboard.
Now I want, when I hit the url with id it should check the presence of id in two columns(like partyPartId and soleProperietorPartId) and display data related to that id.
Here partyPartId and soleProperietorPartId are from the same table nms:recipient and id in url would be either partyPartId or soleProperietorPartId.

 

Below is the part of code which currently working in web application-

 

var companyPartId = ctx.recipient.@partyPartId;
var query = NLWS.xtkQueryDef.create(
{queryDef: {schema: "nms:broadLogRcp", operation: "select",
select: {
node:[{expr: "[delivery/@messageType]"},{expr: "[@address]"},{expr: "[@status]"},{expr: "[recipient/@soleProprietorPartId]"},{expr: "[delivery/@label]"},{expr: "[@tempCompanyPartId]"},{expr: "[delivery/content/sms/source]", alias:"@content"},{expr: "[delivery/@deleteStatus]"},{expr: "[delivery/@lastModified]"},{expr: "[delivery/@id]"},{expr: "@eventDate"},{expr: "[@recipient-id]"}]
},
where: {
condition:[{expr: "[@tempCompanyPartId] = " + companyPartId}]
},
orderBy: {
node:{expr: "@eventDate", sortDesc: "true"}
}
}});

 

Please help to solve the query.

 

Regards,

Tejashri

11 Replies
Manoj_Kumar_
Community Advisor
Community Advisor

Hello @tejashriw155148 ,

 

To make this work, Change your where statement to 

 

where: {
condition:[
{boolOperator: "OR",expr: "[@tempCompanyPartId] = " + companyPartId },
{expr: "[@soleProperiterPartId] = " + soleProperiterPartId}
]
},

 

OR

 

where: {
condition:[
{boolOperator: "OR",expr: "[recipient/@tempCompanyPartId] = " + companyPartId },
{expr: "[recipient/@soleProperiterPartId] = " + soleProperiterPartId}
]
},

 

i am not sure about the xpath of these fields in your table. So you might have to change the xpath as per your table structure.

Let me know if that helps.

Thanks

tejashriw155148
Level 4
Level 4

Hi @Manoj_Kumar_ 

 

I tried that logic but its working only for partyPartId. When I put the soleProprietorPartId in the web url its throwing below error-


2020-07-02 11:11:06 [nms:webApp Error] Failed to preload data: WDB-200001 SQL statement 'SELECT R0.iRecipientId, R0.sEmail, R0.sLastName, R0.sFirstName, R0.iPartyPartId, R0.sPartyFullName, R0.iSoleProprietorPartId FROM NmsRecipient R0 WHERE (R0.iPartyPartId = ?)' could not be executed.\n Param(0)=22655467\nWDB-200011 The requested database record does not exist.\nCannot load document of type 'Recipients (nms:recipient)' satisfying condition '([/@partyPartId] = '22655467')'.\nSOP-330011 Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'.
2020-07-02 11:11:06 SOP-330011 Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'. (iRc=-53)
2020-07-02 11:11:06 Cannot load document of type 'Recipients (nms:recipient)' satisfying condition '([/@partyPartId] = '22655467')'. (iRc=-53)
2020-07-02 11:10:51 WDB-200011 The requested database record does not exist. (iRc=-2003)
2020-07-02 11:10:51 WDB-200001 SQL statement 'SELECT R0.iRecipientId, R0.sEmail, R0.sLastName, R0.sFirstName, R0.iPartyPartId, R0.sPartyFullName, R0.iSoleProprietorPartId FROM NmsRecipient R0 WHERE (R0.iPartyPartId = ?)' could not be executed.\n Param(0)=22655467 (iRc=-2003)

 

 

Thanks,

Manoj_Kumar_
Community Advisor
Community Advisor
Did you check the xpath. If you are querying the BroadLogRcp and want put a columns from recipient in where condition then the xpath of the where condition will look something like this [recipient/@COLUMN_INTERNAL_NAME]. But you are directly checking the column name without referencing the recipient table.
tejashriw155148
Level 4
Level 4

Hi @Manoj_Kumar_ 

 

Yes I checked xpath its correct.

I added below logic.

 

var companyPartId = ctx.recipient.@partyPartId;//ctx.recipient.@partyPartId;
var sppId = ctx.recipient.@soleProprietorPartId;

 

var query = NLWS.xtkQueryDef.create(
{queryDef: {schema: "nms:broadLogRcp", operation: "select",
select: {
node:[{expr: "[delivery/@messageType]"},{expr: "[@address]"},{expr: "[@status]"},{expr: "[recipient/@soleProprietorPartId]"},{expr: "[delivery/@label]"},{expr: "[@tempCompanyPartId]"},{expr: "[delivery/content/sms/source]", alias:"@content"},{expr: "[delivery/@deleteStatus]"},{expr: "[delivery/@lastModified]"},{expr: "[delivery/@id]"},{expr: "@eventDate"},{expr: "[@recipient-id]"}]
},
where: {
condition:[{boolOperator:"OR",expr: "[@tempCompanyPartId] = " + companyPartId},
{boolOperator:"OR",expr: "[recipient/@soleProprietorPartId] = " + sppId}]
},
orderBy: {
node:{expr: "@eventDate", sortDesc: "true"}
}
}});

 

Here the id in the url only looking into partyPartId column and not in soleProprietorPartId, hence throwing error.

What else I can do?

 

Thanks,

Manoj_Kumar_
Community Advisor
Community Advisor
boolOperator will come only once in the statement
tejashriw155148
Level 4
Level 4

Hi @Manoj_Kumar_ 

 

With single boolOperator also same error coming. Error is related to SoleProprietorPartId.

Web url id not showing data of SoleProprietorPartId. Below is the error-

 

2020-07-02 11:11:06 [nms:webApp Error] Failed to preload data: WDB-200001 SQL statement 'SELECT R0.iRecipientId, R0.sEmail, R0.sLastName, R0.sFirstName, R0.iPartyPartId, R0.sPartyFullName, R0.iSoleProprietorPartId FROM NmsRecipient R0 WHERE (R0.iPartyPartId = ?)' could not be executed.\n Param(0)=22655467\nWDB-200011 The requested database record does not exist.\nCannot load document of type 'Recipients (nms:recipient)' satisfying condition '([/@partyPartId] = '22655467')'.\nSOP-330011 Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'.
2020-07-02 11:11:06 SOP-330011 Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'. (iRc=-53)
2020-07-02 11:11:06 Cannot load document of type 'Recipients (nms:recipient)' satisfying condition '([/@partyPartId] = '22655467')'. (iRc=-53)
2020-07-02 11:10:51 WDB-200011 The requested database record does not exist. (iRc=-2003)
2020-07-02 11:10:51 WDB-200001 SQL statement 'SELECT R0.iRecipientId, R0.sEmail, R0.sLastName, R0.sFirstName, R0.iPartyPartId, R0.sPartyFullName, R0.iSoleProprietorPartId FROM NmsRecipient R0 WHERE (R0.iPartyPartId = ?)' could not be executed.\n Param(0)=22655467 (iRc=-2003)

Manoj_Kumar_
Community Advisor
Community Advisor
Can you please share the structure of the table?
Manoj_Kumar_
Community Advisor
Community Advisor

Hello @tejashriw155148 

 

Can you try with this code?

 

 

    
	  var companyPartId = ctx.recipient.@partyPartId;//ctx.recipient.@partyPartId;
var sppId = ctx.recipient.@soleProprietorPartId;
	  var query=xtk.queryDef.create(
  <queryDef schema="nms:broadLogRcp" operation="select" >
	   <where>
          <condition boolOperator="OR"   expr="[recipient/@soleProprietorPartId] = '" + sppId + "'" />
          <condition expr="[recipient/@partyPartId] = '" + companyPartId + "'"/>
        </where>
         
        <select>
          <node expr="[delivery/@messageType]"/>
          <node expr="@address" />
          <node expr="@status"/>
          <node expr="[delivery/@label]"/>
          <node  expr="[recipient/@soleProprietorPartId]"/>
          <node expr="[delivery/content/sms/@source]" />
          <node expr="[delivery/@deleteStatus]"/>
          <node  expr="[delivery/@lastModified]" />
          <node  expr="[delivery/@id]" />
          <node expr="@eventDate"  />
          <node expr="[@recipient-id]"/>
        </select>
		</queryDef>  
  ).ExecuteQuery();

 

Let me know if that works.

tejashriw155148
Level 4
Level 4

Hello @Manoj_Kumar_ 

 

The whole script is in JSON format so this format is not supporting in the script.

However I tried the logic which you sent in the JSON format still getting the same error.

 

Thanks,

Sukrity_Wadhwa
Employee
Employee

Hi @tejashriw155148 

Were you able to solve this or are you still having issues?