BTP Learning Journey Unit 3 - BP drowdown query doesn't work

[S_benzheng]

I'm running through the Unit 3 - consume external service via API but experiencing issue with the last step.
That the BP downdown list doesn't work with filter query.
Everything else with Unit 3 working correctly.

Done this twice with code comparison of "risk-service.js", anyone have the same issue and advice? thanks

======following is the error log with on Edit screen when querying BP==============

[cds] - POST /service/risk/$batch
[cds] - > EDIT Risks(ID=20466922-7d57-4e76-b14c-e53fd97dcb12,IsActiveEntity=true)/RiskService.draftEdit {
'$select': 'HasActiveEntity,HasDraftEntity,ID,IsActiveEntity,bp_BusinessPartner,criticality,descr,impact,miti_ID,owner,prio,title',
'$expand': 'DraftAdministrativeData($select=DraftIsCreatedByMe,DraftUUID,InProcessByUser),bp($select=BusinessPartner,LastName),miti($select=ID,IsActiveEntity,descr)'
}
[
{ ref: [ 'HasActiveEntity' ] },
{ ref: [ 'HasDraftEntity' ] },
{ ref: [ 'ID' ] },
{ ref: [ 'IsActiveEntity' ] },
{ ref: [ 'bp_BusinessPartner' ] },
{ ref: [ 'criticality' ] },
{ ref: [ 'descr' ] },
{ ref: [ 'impact' ] },
{ ref: [ 'miti_ID' ] },
{ ref: [ 'owner' ] },
{ ref: [ 'prio' ] },
{ ref: [ 'title' ] },
{
ref: [ 'DraftAdministrativeData' ],
expand: [ { ref: [ 'DraftIsCreatedByMe' ] }, { ref: [ 'DraftUUID' ] }, { ref: [ 'InProcessByUser' ] } ]
},
{
ref: [ 'bp' ],
expand: [ { ref: [ 'BusinessPartner' ] }, { ref: [ 'LastName' ] } ]
},
{
ref: [ 'miti' ],
expand: [ { ref: [ 'ID' ] }, { ref: [ 'IsActiveEntity' ] }, { ref: [ 'descr' ] } ]
}
]
[cds] - POST /service/risk/$batch
[cds] - > READ BusinessPartners {
'$select': 'BusinessPartner,FirstName,LastName',
'$count': 'true',
'$orderby': 'LastName',
'$skip': '0',
'$top': '59'
}
[remote] - Error: Error during request to remote service:
Invalid system query option specified
at run (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/remote/utils/client.js:299:31)
at processTicksAndRejections (node:internal/process/task_queues:96:5)
at async RemoteService.<anonymous> (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/remote/Service.js:221:20)
at async next (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:75:17)
at async RemoteService.handle (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:73:10)
at async RemoteService.handle (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/remote/Service.js:272:22)
at async ApplicationService.<anonymous> (/home/user/projects/risk-management/srv/risk-service.js:38:10)
at async next (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:75:17)
at async ApplicationService.handle (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:73:10)
at async _readCollection (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/cds-services/adapter/odata-v4/handlers/read.js:264:19) {
statusCode: 502,
reason: [AxiosError: Error during request to remote service:
Invalid system query option specified] {
code: 'ERR_BAD_REQUEST',
config: {
timeout: 60000,
xsrfCookieName: 'XSRF-TOKEN',
xsrfHeaderName: 'X-XSRF-TOKEN',
maxContentLength: -1,
maxBodyLength: -1,
proxy: false,
method: 'get',
baseURL: 'https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap/API_BUSINESS_PARTNER/',
url: "/A_BusinessPartner?$select=BusinessPartner,FirstName,LastName&$count=true&$top=59&$orderby=LastName%20asc,BusinessPartner%20asc&$filter=LastName%20ne%20''%20and%20FirstName%20ne%20''",
data: undefined
},
request: {
method: 'GET',
url: "https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap/API_BUSINESS_PARTNER//A_BusinessPartner?$s...''",
headers: {
Accept: 'application/json,text/plain',
'accept-language': 'en-US',
apikey: '9pQcbxQE68yxCTORrMRsdsgsdgT',
'User-Agent': 'axios/0.27.2'
}
},
response: {
status: 400,
statusText: 'Bad Request',
headers: {
date: 'Sun, 01 Jan 2023 21:01:07 GMT',
'content-type': 'application/json',
'content-length': '575',
connection: 'close',
'set-cookie': [ 'sap-usercontext=sap-client=100; path=/' ],
dataserviceversion: '1.0',
'sap-server': 'true',
'sap-perf-fesrec': '50923.000000',
'x-content-type-options': 'nosniff',
'strict-transport-security': 'max-age=31536000; includeSubDomains',
'x-xss-protection': '1; mode=block',
'sap-passport-component': 'none',
'access-control-allow-origin': '*',
'access-control-allow-credentials': 'false',
'access-control-allow-headers': 'set-cookie, origin, accept, maxdataserviceversion, x-csrf-token, apikey, dataserviceversion, accept-language, x-http-method,content-type,X-Requested-With',
'access-control-max-age': '3628800',
'access-control-allow-methods': 'GET, PUT, POST, DELETE',
'access-control-expose-headers': 'set-cookie, x-csrf-token, x-http-method'
},
body: {
error: {
code: '00505692409C1ED98BC626766B3596A5',
message: { lang: 'en', value: 'Invalid system query option specified' },
innererror: {
transactionid: '25669476CF940200E0063B0B68F81F71',
timestamp: '20230101210107.8588330',
Error_Resolution: {
SAP_Transaction: 'For backend administrators: use ADT feed reader "SAP Gateway Error Log" or run transaction /IWFND/ERROR_LOG on SAP Gateway hub system and search for entries with the timestamp above for more details',
SAP_Note: 'See SAP Note 1797736 for error analysis (https://service.sap.com/sap/support/notes/1797736)'
}
}
}
}
},
correlationId: 'fe6e192d-8542-4b85-b621-73556ee1553a'
}
}
[cds] - Error: Error during request to remote service:
Invalid system query option specified
at run (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/remote/utils/client.js:299:31)
at processTicksAndRejections (node:internal/process/task_queues:96:5)
at async RemoteService.<anonymous> (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/remote/Service.js:221:20)
at async next (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:75:17)
at async RemoteService.handle (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:73:10)
at async RemoteService.handle (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/remote/Service.js:272:22)
at async ApplicationService.<anonymous> (/home/user/projects/risk-management/srv/risk-service.js:38:10)
at async next (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:75:17)
at async ApplicationService.handle (/home/user/projects/risk-management/node_modules/@sap/cds/lib/srv/srv-dispatch.js:73:10)
at async _readCollection (/home/user/projects/risk-management/node_modules/@sap/cds/libx/_runtime/cds-services/adapter/odata-v4/handlers/read.js:264:19) {
statusCode: 502,
reason: [AxiosError: Error during request to remote service:
Invalid system query option specified] {
code: 'ERR_BAD_REQUEST',
config: {
timeout: 60000,
xsrfCookieName: 'XSRF-TOKEN',
xsrfHeaderName: 'X-XSRF-TOKEN',
maxContentLength: -1,
maxBodyLength: -1,
proxy: false,
method: 'get',
baseURL: 'https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap/API_BUSINESS_PARTNER/',
url: "/A_BusinessPartner?$select=BusinessPartner,FirstName,LastName&$count=true&$top=59&$orderby=LastName%20asc,BusinessPartner%20asc&$filter=LastName%20ne%20''%20and%20FirstName%20ne%20''",
data: undefined
},
request: {
method: 'GET',
url: "https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap/API_BUSINESS_PARTNER//A_BusinessPartner?$s...''",
headers: {
Accept: 'application/json,text/plain',
'accept-language': 'en-US',
apikey: '9pQcbxQE68yxCTORrMRsdsgsdgT',
'User-Agent': 'axios/0.27.2'
}
},
response: {
status: 400,
statusText: 'Bad Request',
headers: {
date: 'Sun, 01 Jan 2023 21:01:07 GMT',
'content-type': 'application/json',
'content-length': '575',
connection: 'close',
'set-cookie': [ 'sap-usercontext=sap-client=100; path=/' ],
dataserviceversion: '1.0',
'sap-server': 'true',
'sap-perf-fesrec': '50923.000000',
'x-content-type-options': 'nosniff',
'strict-transport-security': 'max-age=31536000; includeSubDomains',
'x-xss-protection': '1; mode=block',
'sap-passport-component': 'none',
'access-control-allow-origin': '*',
'access-control-allow-credentials': 'false',
'access-control-allow-headers': 'set-cookie, origin, accept, maxdataserviceversion, x-csrf-token, apikey, dataserviceversion, accept-language, x-http-method,content-type,X-Requested-With',
'access-control-max-age': '3628800',
'access-control-allow-methods': 'GET, PUT, POST, DELETE',
'access-control-expose-headers': 'set-cookie, x-csrf-token, x-http-method'
},
body: {
error: {
code: '00505692409C1ED98BC626766B3596A5',
message: { lang: 'en', value: 'Invalid system query option specified' },
innererror: {
transactionid: '25669476CF940200E0063B0B68F81F71',
timestamp: '20230101210107.8588330',
Error_Resolution: {
SAP_Transaction: 'For backend administrators: use ADT feed reader "SAP Gateway Error Log" or run transaction /IWFND/ERROR_LOG on SAP Gateway hub system and search for entries with the timestamp above for more details',
SAP_Note: 'See SAP Note 1797736 for error analysis (https://service.sap.com/sap/support/notes/1797736)'
}
}
}
}
},
correlationId: 'fe6e192d-8542-4b85-b621-73556ee1553a'
},
id: '1448590',
level: 'ERROR',
timestamp: 1672606867940
}

[Margit_Wagner]

Hi @Former Member
We have several learning journey related to BTP e.g. 
Building side-by-side extensions on SAP BTP 
Developing Applications Running on SAP BTP Using SAP HANA Cloud 
Get Started with ABAP Programming on SAP BTP 
Learn the ABCs of ABAP Programming on SAP BTP 

Could you please provide the title of the learning journey you are facing the issue, so we can analyse further.

Kind regards
Margit

[S_benzheng]

Hi Margit,

Thanks for the reply.
The issue is with learning journey "Building side-by-side extensions on SAP BTP" exercise Unit 3.
The query for BP in the last step of the exercise doesn't work.

Thanks,
BZ

[Margit_Wagner]

Hi @Former Member 

we are in the process of evaluating the problem.
Please allow for a delay in our response.
Kind regards
Margit
 

[markushaug · Accepted solution]

Hi @Former Member ,

thanks to @NitinMahajan for further investigation on this.

You can actually remove the count parameter from the OData request in the risk-service.js file:

 this.on("READ", BusinessPartners, async (req) => {
        // The API Sandbox returns alot of business partners with empty names.
        // We don't want them in our application
        req.query.where("LastName <> '' and FirstName <> '' ");
        
        // ADD THIS LINE TO REMOVE THE COUNT PARAMETER
        req.query.SELECT.count = false;

        return await BPsrv.transaction(req).send({
            query: req.query,
            headers: {
                apikey: process.env.apikey,
            },
        });
    });

Please let me know if this solved the issue for you as well.

Thanks,

Markus

[NitinMahajan]

Hi,

The problem seems to come from $count=true getting appended to the query. Ran the query without this (via Postman) and it worked.

Not sure how it gets added and how can one remove it as this seems to be getting added automatically.

It is coming from standard learning content at Github: https://github.com/SAP-samples/btp-side-by-side-extension-learning-journey

@markushaug , @Margit_Wagner , Please let us know if there is a fix coming soon?

Regards,

Nitin

[Margit_Wagner]

Hi @NitinMahajan

Thank you for sharing your idea - We are in the process of evaluating the problem.
Please allow for a delay in our response.
Kind regards
Margit
 

[markushaug]

Thank you! As mentioned above, you can remove it with "req.query.SELECT.count = false;"

[markushaug]

Hi benzheng6,

thanks for reaching out to us.

Could you kindly share your project via GitHub so that I could have a quick look into it?

Thanks,

Markus

[S_benzheng]

Thank Markus, here is my git link:

https://github.com/robbiezheng/RiskManagement.git

[Mohit_Agarwal]

Hi @markushaug ,

I was also facing the same problem. As suggested in previous comments, I tried setting count=false. It seems to have solved the problem partially. 

Although, the problem doesn't occur when opening valueList now, it is creating an issue when I use the search button to search for a BP instead of explicitly using the filters (such as 'Menon for Last Name').

Issue: 

reason: [AxiosError: Error during request to remote service:
  Invalid system query option specified] {
    code: 'ERR_BAD_REQUEST',
    config: {
      timeout: 60000,
      xsrfCookieName: 'XSRF-TOKEN',
      xsrfHeaderName: 'X-XSRF-TOKEN',
      maxContentLength: -1,
      maxBodyLength: -1,
      proxy: false,
      method: 'get',
      baseURL: 'https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap/API_BUSINESS_PARTNER',
      url: `/A_BusinessPartner?$select=BusinessPartner,FirstName,LastName&$search="Alice"&$top=49&$orderby=BusinessPartner%20asc&$filter=LastName%20ne%20''%20and%20FirstName%20ne%20''`,
      data: undefined
    },
    request: {
      method: 'GET',
      url: `https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartner?$select=BusinessPartner,FirstName,LastName&$search="Alice"&$top=49&$orderby=BusinessPartner%20asc&$filter=LastName%20ne%20''%20and%20FirstName%20ne%20''`,
      headers: {
        Accept: 'application/json,text/plain',
        'accept-language': 'en-US',
        apikey: 'dASAlivXs19CsAmAA9sNVo2A2FgrvmVi',
        'User-Agent': 'axios/0.27.2'
      }
    }

GitHub Link: https://github.tools.sap/I553616/extension-suite