How to performing a Join using CQN using fluid node.js notation

[former_member564413]

Hi.

What would the equivalent CQN be for the following SQL statement and how would one write it using fluid node.js?

//Retrieve all persons with role 'C'
SELECT Persons.*
  FROM Persons join Person_Role 
       on Person_Role.parent_id = Persons.ID
 WHERE Person_Role.role = 'C';

The documentation (https://cap.cloud.sap/docs/node.js) doesnt seem to cover joins.

Regards

Sergio

[former_member631165 · Accepted solution]

Hi Sergio,

you are right, this is missing in the documentation.

Please try

SELECT.from('Persons').join('Person_Role').on('Person_Role.parent_id', '=', 'Persons.ID').where('Person_Role.role', '=', 'C')

The corresponding CQN would look like this:

{ SELECT: {
  from: {
    join: 'inner',
    args: [ {ref:['Persons']}, {ref:['Person_Role']} ],
    on: [
      {ref:['Person_Role', 'parent_id']},
      '=',
      {ref:['Persons', 'ID']}
    ]
  },
  where: [ {ref:['Person_Role', 'role']}, '=', {val:'C'} ]
}}

You can use

.join <- inner join
.leftJoin
.rightJoin
.fullJoin

Regards

Samuel

[VoJu · Accepted solution]

Hi Samuel,

are this SQL Statements also working with external services?

I am getting "TypeError: Cannot read properties of undefined (reading 'map')" as also described here https://answers.sap.com/questions/13519313/join-query-throws-error-cannot-read-property-map-o.html

Thanks and best regards,
Julian