Below is the existing query which works fine

query.extend.select(" XXXX.* ", EXTEND_APPEND)

query.extend.from(" XXXX ", EXTEND_APPEND)

query.extend.where(" (:YYYY._index1 refers to XXXX.cmbb unref clear) ",
EXTEND_APPEND)



I have added one condition which makes the query as

query.extend.select(" XXXX.* ", EXTEND_APPEND)

query.extend.from(" XXXX ", EXTEND_APPEND)

query.extend.where( " XXXX.status <> open ",
EXTEND_APPEND)

query.extend.where(" (:YYYY._index1 refers to XXXX.cmbb unref clear) ",
EXTEND_APPEND)


Now the issue is the above query works fine if there is data in XXXX table
but if there is no data in XXXX it displays nothing ,which means unref clear is failing if I add my condition.I will be very much grateful if someone can guide me to the solution for the above issue

What do you mean by "if there is no data in XXXX"? There should be rows, where XXXX.status <> open, at least.

Hi ,

No XXXX table is just used for some information ... so even if data is not there in XXXX it should show the data of YYYY

What is the domain of status field? If it is string then you have to quote "open", you can't have a bare word like that.

I think what giggty is saying is XXXX.status <> Open would probably be something like XXXX.status <> tcxxx.open or XXXX.status <> "Open". So now assuming that the actual code is correct(and I am not an expert on this) but isn't the query.extend.where(" (:YYYY._index1 refers to XXXX.cmbb unref clear) " for this statement only for the index that does the refers to. Does it no include the additional statement for xxxx.status. So if you removed the second statement xxxx.status <> open - does it return data for yyyy no matter what data is in xxxx. The way I read the original post it works - but does not work when you add the second xxxx.status<>open. Is that correct?

Yes mark your understanding is correct .. The problem is my <> open condition works if data is there in XXXX table but if there is no data in XXXX tble then even YYYY fields are not visible which means unref clear is not working

I think it should work (provided your extend statement "XXXX.status <> Open" is pseudocode and your actual code works as you say). As I don't know how "unref clear" works under the hood I suggest you maybe add an extra condition: query.extend.where( " XXXX.status <> open or XXXX.status is null ",EXTEND_APPEND) and see how it goes.

Hi giggty,

I had tried this one but did nt succeed

So if it works without the XXXX.status <> Open - why not remove it from the query and then just do a test for xxxx.status outside the query? As a work around.

Looking at the help it seems like what you are doing should work.

SELECT ttadv100.*
FROM ttadv100, ttadv101
WHERE ttadv101.cmod BETWEEN "aaa" and "azz"
AND ttadv101 REFERS TO ttadv100 UNREF <UNREF_mode>
AND ttadv100.cpac BETWEEN " " and "zz"

The evaluation order of this query may be as follows:
1.Find all rows / records in ttadv101 that match the BETWEEN "aaa" and "azz" condition.
2.Find all references from each row from step 1 in table ttadv100.
3.Find all rows selected in step 2 which match the BETWEEN " " and "zz" condition

When some reference does not exist in ttadv100 (step 2 fails), because of the condition on the referenced table, the whole row is rejected

Agree with Mark.

As another workaround you could try using outer join clause instead of refers to.