Help about SubQuery

SQL++
1

Hi,
after sql usage, i am getting use to N1QL. But i would like to write sub query like above. When i want to try this, i usually take this error :

[
** {**
** “code”: 5010,**
** “msg”: “Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM mybucket.”**
** }**
]

this is my query. How can i change for N1QL ?

SELECT crmtransaction.rec_id
,crmtransaction.transactionday_id
,crmtransaction.explanation
,crmtransaction.transactiontimeitem_id
,crmtransaction.cast_id
,crmtransaction.transactionstatus
,erpcast.employeecode
,erpcast.employeename
,erpcast.employeesurname
,erpcast.IdCardNo
,(SELECT count(erpscaneitem) scenecount FROM mybucket erpscaneitem JOIN mybucket cast1 ON KEYS erpscaneitem.cast_id WHERE erpscaneitem.type = “ErpSceneItem” and erpscaneitem.isdeleted = false and erpscaneitem.isprocessed = false and cast1.isdeleted = false and cast1.rec_id = crmtransaction.cast_id GROUP BY erpscaneitem.cast_id) scenecount
FROM
mybucket crmtransaction
LEFT JOIN mybucket erpcast ON KEYS crmtransaction.cast_id
WHERE crmtransaction.type = "CrmTransaction"
AND crmtransaction.transactionstatus IN [0,1]
AND crmtransaction.inuse = True
AND crmtransaction.isdeleted = False
AND crmtransaction.cast_id IS NOT NULL
ORDER BY crmtransaction.transactionday_id ASC , crmtransaction.transactiontimeitem_id ASC , erpcast.employeename ASC , erpcast.employeecode ASC

Thanks for help

2

which version of Couchbase Server do you use?

3

I am using version 4.6.0-3453 Enterprise Edition (build-3453).

4

Subquery is correlated because referencing crmtransaction.cast_id from parent. Correlated subqueries in the N1QL requires USE KEYS in subquery.

Check the following query correctness and try on N1QL.

SELECT crmtransaction.rec_id, crmtransaction.transactionday_id, crmtransaction.explanation,
       crmtransaction.transactiontimeitem_id, crmtransaction.cast_id, crmtransaction.transactionstatus,
       erpcast.employeecode, erpcast.employeename, erpcast.employeesurname, erpcast.IdCardNo,
       FIRST v.scenecount FOR v IN (SELECT count(erpscaneitem) scenecount, cast1.rec_id
                                    FROM mybucket erpscaneitem JOIN mybucket cast1 ON KEYS erpscaneitem.cast_id
                                    WHERE erpscaneitem.type = "ErpSceneItem" AND erpscaneitem.isdeleted = false AND
                                          erpscaneitem.isprocessed = false AND cast1.isdeleted = false
                                    GROUP BY erpscaneitem.cast_id, cast1.rec_id) WHEN v.rec_id = crmtransaction.cast_id END AS scenecount
FROM mybucket crmtransaction
LEFT JOIN mybucket erpcast ON KEYS crmtransaction.cast_id
WHERE crmtransaction.type = "CrmTransaction" AND crmtransaction.transactionstatus IN [0,1] AND
         crmtransaction.inuse = True AND crmtransaction.isdeleted = False AND crmtransaction.cast_id IS NOT NULL
ORDER BY crmtransaction.transactionday_id ASC , crmtransaction.transactiontimeitem_id ASC ,
         erpcast.employeename ASC , erpcast.employeecode ASC;
1 Like
5

Thanks a lot @vsr1 . This is actually what i need.