Is there anyway we can convert below dynamic json structure to either Query/SysDa object or SQL query using x++ ?
{
"name": "CustInvoiceJour"
"select": [
{
"name": "InvoiceID"
},
{
"name": "InvoiceDate"
}
],
"fromTable": {
"name": "CustInvoiceJour"
},
"where": [
{
"booleanOperator": "OR",
"listOfExpr": [
{
"booleanOperator": "AND",
"listOfExpr": [
{
"expr": {
"column": {
"name": "InvoiceID"
},
"operator": "=",
"value": [
"1000",
"2000"
]
}
},
{
"expr": {
"column": {
"name": "InvoiceDate"
},
"operator": "=",
"value": [
"06/04/2024"
]
}
}
]
},
{
"expr": {
"column": {
"name": "InvoiceID"
},
"operator": "IN",
"value": [
"1000",
"2000"
]
}
},
{
"booleanOperator": "AND",
"listOfExpr": [
{
"expr": {
"column": {
"name": "Voucher"
},
"operator": "NOT IN",
"value": [
"VF11"
]
}
},
{
"expr": {
"column": {
"name": "VoucherDate"
},
"operator": "=",
"value": [
"06/04/2024"
]
}
}
]
}
]
}
],
"order_by": [
{
"column": {
"name": "InvoiceID"
},
"order": "DESCENDING"
},
{
"column": {
"name": "InvoiceDate"
},
"order": "DESCENDING"
}
]
}
The sql query should be constructed something like below based on the above json via X++:-
select invoiceid,invoicedate from custinvoicejour where ((invoiceId in ("1000","2000")
and invoicedate = '06/04/2024') or invoiceid in ("1000","2000")
or (voucher not in ("VF11") and voucherDate = "06/04/2024"))
order by invoiceID,invoiceDate desc
Also curious if the query can be constructed using SysDa api / Query object in x++.
Thanks,
