Hello!

I'm looking for a solution to display records on a multi-occ session where the record should not have any referencing records in another table.
I have a multi-occ session displaying info from table A. That table can have referencing records in table B and my point is to show only those records from A that doesn't have any referencing records in B.
Tried with skip.io("") = no luck and I don't see any possibility to solve it with query.extension - or am I wrong?

BR, Kenny

Hi,

Can you post your code? query extension should work fine.

I have only the code for skip.io("")-way that i tried.
For query extension I have no idea how to solve it - i would go with:

query.extend.select("tdpur401.*, tdpur406.* ", EXTEND_APPEND)
query.extend.from("tdpur401, tdpur406 ", EXTEND_APPEND)
query.extend.where("tdpur406.orno refers to tdpur401.orno ", EXTEND_APPEND)
query.extend.where("tdpur406.pono refers to tdpur401.pono ", EXTEND_APPEND)
query.extend.where("tdpur406.sqnb refers to tdpur401.sqnb ", EXTEND_APPEND)

But then at the end I need those that don't exist in tdpur406 so I would need records from tdpur401 that would get in selectempty in a usual SQL query.
So that's the point where I don't know how to complete the query extension to work as expected. Or did I start completely wrong? :)

Hope that I clearly expressed what I would like. :D

BR, David

Hi,

Try this -

query.extend.select("tdpur406.*")
query.extend.from("tdpur406")
query.extend.where("not exists (select tdpur401.orno from tdpur401 where tdpur401._index1 = {tdpur406.orno,tdpur406.pono,tdpur406.sqnb})",EXTEND_OVERWRITE)

Hi,

Try this -

query.extend.select("tdpur406.*")
query.extend.from("tdpur406")
query.extend.where("not exists (select tdpur401.orno from tdpur401 where tdpur401._index1 = {tdpur406.orno,tdpur406.pono,tdpur406.sqnb})",EXTEND_OVERWRITE)

Here tdpur401 is main table, and so query.extend.select and query.extend.from is not required. - What do you think Bhushan?

where condition should be something like below

not exists (select tdpur406.orno from tdpur406 where tdpur406._index1 = {:tdpur401.orno,:tdpur401.pono,:tdpur401.sqnb}

Oh yes,

I thought he wanted the reverse way. Correct -

not exists (select tdpur406.orno from tdpur406 where tdpur406._index1 = {:tdpur401.orno,:tdpur401.pono,:tdpur401.sqnb}

should do the job.

Oh, what a dumb@$$ I am... :)
Totally forgot the "not exists" query - used only once thought.

It's working like a charm with:
query.extend.where("not exists (select tdpur406.orno from tdpur406 where tdpur406._index1 = {:tdpur401.orno,:tdpur401.pono,:tdpur401.sqnb})",EXTEND_OVERWRITE)

Thanks guys!