Match web application url id with two columns

tejashriw155148

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)

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

tejashriw155148

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,

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.

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,

boolOperator will come only once in the statement

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)

Can you please share the structure of the table?

Hi @_Manoj_Kumar 

 

Structure of recipient table is as -

 

tejashriw155148_0-1593697479517.png

 

Thanks.

_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