Match web application url id with two columns

Avatar

Avatar
Validate 1
Level 2
tejashriw155148
Level 2

Likes

12 likes

Total Posts

70 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Give Back 5
Give Back 3
Give Back
Boost 5
View profile

Avatar
Validate 1
Level 2
tejashriw155148
Level 2

Likes

12 likes

Total Posts

70 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Give Back 5
Give Back 3
Give Back
Boost 5
View profile
tejashriw155148
Level 2

02-07-2020

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

Accepted Solutions (0)

Answers (4)

Answers (4)

Avatar

Avatar
Give Back 50
Employee
Sukrity_Wadhwa
Employee

Likes

181 likes

Total Posts

955 posts

Correct reply

35 solutions
Top badges earned
Give Back 50
Validate 1000
Validate 500
Validate 250
Validate 100
View profile

Avatar
Give Back 50
Employee
Sukrity_Wadhwa
Employee

Likes

181 likes

Total Posts

955 posts

Correct reply

35 solutions
Top badges earned
Give Back 50
Validate 1000
Validate 500
Validate 250
Validate 100
View profile
Sukrity_Wadhwa
Employee

15-07-2020

Hi @tejashriw155148 

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

Avatar

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

129 likes

Total Posts

329 posts

Correct reply

104 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

129 likes

Total Posts

329 posts

Correct reply

104 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile
_Manoj_Kumar
MVP

02-07-2020

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.

Avatar

Avatar
Validate 1
Level 2
tejashriw155148
Level 2

Likes

12 likes

Total Posts

70 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Give Back 5
Give Back 3
Give Back
Boost 5
View profile

Avatar
Validate 1
Level 2
tejashriw155148
Level 2

Likes

12 likes

Total Posts

70 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Give Back 5
Give Back 3
Give Back
Boost 5
View profile
tejashriw155148
Level 2

02-07-2020

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
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

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
boolOperator will come only once in the statement
tejashriw155148

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
Can you please share the structure of the table?
tejashriw155148

Hi @_Manoj_Kumar 

 

Structure of recipient table is as -

 

tejashriw155148_0-1593697479517.png

 

Thanks.

Avatar

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

129 likes

Total Posts

329 posts

Correct reply

104 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

129 likes

Total Posts

329 posts

Correct reply

104 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile
_Manoj_Kumar
MVP

02-07-2020

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