I am presently attempting to fetch data with JQPL. My Query appears like
SELECT NEW com.test.CustomObject(t1.name, CASE WHEN(t2 IS NOT NULL) THEN true ELSE false END) FROM table1 t1, table2 t2 WHERE t1.id = :id1 AND t2.id = :id2
Obviously the 2 organizations iteself exist an querying for one of these is working. Querying having a fixed value also works:
SELECT NEW com.test.CustomObject(t1.name, false) FROM table1 t1 WHERE t1.id = :id1
My issue is, which i receive no return value if :id2 includes a non existing value. Rather than giving false towards the CustomObject constructor, the row is overlooked completely.
What else could I actually do to achieve understanding if parameter id2 includes a row in table2 to pass through this like a boolean value towards the custom constructor?
More information can obviously get offers for.
Make use of LEFT (OUTER) JOIN for connecting from table1 to table2. This makes left hands side to participate the end result also when right hands side of join does not is available. In this situation values from the right hands side (for instance t2.id) have null values.
Plus it does not seem sensible to create id like a parameter two times (presuming what you would like is t1.id=t2.id). Only use join and hang id parameter once.
I possibly could solve the issue with Exsists:
SELECT NEW com.test.CustomObject(t1.name, CASE WHEN EXISTS(SELECT t2.id FROM table2 t2 WHERE t2.id = :id2) THEN true ELSE false END) FROM table1 t1 WHERE t1.id = :id1