Executing SQL Queries via REST API in iDempiere 10

[Felipe Sousa]

Hello everyone,

I’m currently working with iDempiere 10 and I’m wondering if it’s possible to execute a custom SQL query through the REST API plugin. For example, I would like to run a query like this:

SELECT hr.c_bpartner_id
FROM hr_employee hr
WHERE hr.lcr_dtdem >= now() - interval '90 days'
  AND hr.isactive = 'Y'
  AND hr.lcr_exported = 'Y'
  AND NOT EXISTS (
    SELECT 1
    FROM hr_employee hr2
    WHERE hr2.c_bpartner_id = hr.c_bpartner_id
      AND hr2.hr_employee_id > hr.hr_employee_id
  )

Example: PostMan 
http://10.0.0.124:8081/api/v1/models/c_bpartner?filter(c_bpartner_id+in+(SELECT+hr.c_bpartner_id+FROM+hr_employee+hr+WHERE+hr.lcr_dtdem+>+=+now()+-+interval+'90days'+AND+hr.isactive+=+'Y'+AND+hr.lcr_exported+=+'Y'+AND+NOT+EXISTS+(SELECT+1+FROM+hr_employee+hr2+WHERE+hr2.c_bpartner_id+=+hr.c_bpartner_id+AND+hr2.hr_employee_id+>+hr.hr_employee_id)))

Is there a way to execute something similar via the REST API in iDempiere 10? Or should I approach this differently?

Thanks for any insights!

[Jesús Castillo]

Hi, no in that way

Maybe you can use a SQL column in the C_Bpartner table, and use that column as a filter.

 

Atte: Jesus Castillo.

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/idempiere/29404c94-ac82-4c18-bb57-6476bafdeac0n%40googlegroups.com.

[Sidik Bilardi]

Hi, maybe you can sent some flags in URL to specify which condition need to be run in your code

To view this discussion visit https://groups.google.com/d/msgid/idempiere/CAFAT%3DLRBnzKw8WnLhQ8uj1kWYUjfP_cPHF-4r2jhodeKdQ8k6w%40mail.gmail.com.

[zuhri utama]

Hi, you can create view and register on table and column window. then you can call the table using model endpoint on rest api