Query returns different result

SQL++
1

Hi,

The following 2 queries return different result. Is there any difference between these 2 queries?
The count supposes to be 2, and how to update the 2nd query to get the correct result?
Thanks,
Matthew

Query1:

SELECT count(*) as total_count
FROM CIR_MAIN AS pl 
WHERE pl._type='PatLink' 
 AND pl._tID='eyZoZKny'
 AND ANY v IN pl.patRecIDRefs SATISFIES v in ( SELECT RAW META(t).id as pid
     FROM CIR_MAIN t
	 WHERE t._type='PatRec'
	 AND t._tID='eyZoZKny'
	 AND ANY pName IN t.names SATISFIES LOWER(pName.family) LIKE LOWER("empi%") END
	 AND t._inactiveTime=0 ) END

total_coount: 2

Query2:

with ids as (SELECT RAW META(t).id as pid
     FROM CIR_MAIN t
	 WHERE t._type='PatRec'
	 AND t._tID='eyZoZKny'
	 AND ANY pName IN t.names SATISFIES LOWER(pName.family) LIKE LOWER("empi%") END
	 AND t._inactiveTime=0 )
SELECT count(*) as total_count
FROM CIR_MAIN AS pl 
WHERE pl._type='PatLink' 
 AND pl._tID='eyZoZKny'
 AND ANY v IN pl.patRecIDRefs SATISFIES v = ids.pid END

total_coount: 0

2

ids is ARRAY you can’t do v = ids.pid
Also SELECT RAW so ids are ARRAY of values not ARRAY of objects

Query 2:

WITH ids AS (SELECT RAW META(t).id
             FROM CIR_MAIN t
             WHERE t._type="PatRec"
                   AND t._tID="eyZoZKny"
                   AND ANY pName IN t.names SATISFIES LOWER(pName.family) LIKE LOWER("empi%") END
                   AND t._inactiveTime = 0 )
SELECT count(1) AS total_count
FROM CIR_MAIN AS pl
WHERE pl._type="PatLink"
      AND pl._tID="eyZoZKny"
      AND ANY v IN pl.patRecIDRefs SATISFIES v IN ids END;

or

AND ARRAY_LENGTH(ARRAY_INTERSECT(pl.patRecIDRefs,ids)) > 0

3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.