I have the following query :
SELECT i.id, i.docId, i.proId, i.invRecipientType
FROM pulse i
WHERE i.type = 'pulseInvoice'
AND i.invRecipientType IN ['cpam', 'b2b', 'b2c']
AND i.orgId = 'pulseOrganization::S00001'
AND i.ageId = 'pulseAgency::S00001'
AND i.invStatus IN ['pending', 'error']
AND i.sysActive = true
AND i.invRecordingDate >= '2024-06-01'
AND i.invRecordingDate <= '2024-06-07'
It returns 9 rows as follows :
[
{
"id": "pulseInvoice::isl98829abd50231111efa73e4311e980c8e3",
"docId": null,
"proId": null,
"invRecipientType": "b2c"
},
{
"id": "pulseInvoice::enugl9e03791024ab11efa035dffe4b27df14",
"docId": "pulseDoctor::FRMS10002116811",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::z0rty6df5e850f58211eeac6b8d2531aa0da6",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::s4r3249dc43c0cbe711eeb25455d694fb3241",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::a2jic16022bd0226f11ef9c075de3aecc5009",
"docId": null,
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::uw2o015f07890226f11ef9c075de3aecc5009",
"docId": null,
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::wnw7o6dd806c0927f11eeb9215f9ff36c9f4c",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::bpvi8c99ce980180d11efa0cf07229265c17d",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::q556cbc7bcea024ac11efad1c19ce67df01f1",
"docId": "pulseDoctor::FRMS10002116811",
"proId": null,
"invRecipientType": "cpam"
}
]
If I add a left join with a CASE WHEN it seems to act as an inner join and excludes some rows :
SELECT i.id, i.docId, i.proId, i.invRecipientType
FROM pulse i
LEFT JOIN pulse p ON META(p).id = (CASE WHEN i.proId THEN i.proId ELSE i.docId END)
WHERE i.type = 'pulseInvoice'
AND i.invRecipientType IN ['cpam', 'b2b', 'b2c']
AND i.orgId = 'pulseOrganization::S00001'
AND i.ageId = 'pulseAgency::S00001'
AND i.invStatus IN ['pending', 'error']
AND i.sysActive = true
AND i.invRecordingDate >= '2024-06-01'
AND i.invRecordingDate <= '2024-06-07'
I now get only 6 results, those with null docId and proId are excluded.
[
{
"id": "pulseInvoice::enugl9e03791024ab11efa035dffe4b27df14",
"docId": "pulseDoctor::FRMS10002116811",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::z0rty6df5e850f58211eeac6b8d2531aa0da6",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::s4r3249dc43c0cbe711eeb25455d694fb3241",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::wnw7o6dd806c0927f11eeb9215f9ff36c9f4c",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::bpvi8c99ce980180d11efa0cf07229265c17d",
"docId": "pulseDoctor::dpslc5f92b910a3b511e9a792a35b05d738a1",
"proId": null,
"invRecipientType": "cpam"
},
{
"id": "pulseInvoice::q556cbc7bcea024ac11efad1c19ce67df01f1",
"docId": "pulseDoctor::FRMS10002116811",
"proId": null,
"invRecipientType": "cpam"
}
]
Am I missing something ? If I specify the left join on i.docId or i.proId only, it returns the 9 rows, but combining it with the CASE filters them out.
Thanks
There seems some bug. In mean time try this
select i.id, i.docId, i.proId, i.invRecipientType
FROM pulse i
LEFT JOIN pulse p ON KEYS IFMISSINGORNULL(i.proId, i.docId)
WHERE i.type = 'pulseInvoice'
and i.invRecipientType IN ['cpam', 'b2b', 'b2c']
and i.orgId = 'pulseOrganization::S00001'
and i.ageId = 'pulseAgency::S00001'
and i.invStatus IN ['pending', 'error']
and i.sysActive = true
and i.invRecordingDate >= '2024-06-01'
and i.invRecordingDate <= '2024-06-07';