Hi,

How can I defined this sql statement in in query.extend commands


SELECT shl.t_shpm, shh.t_pddt, shh.t_stco, shh.t_carr, c.t_dsca, ct.t_dsca [City], a.t_cste, a.t_ccty,
sl.t_pono, sl.t_sqnb, shl.t_worn, shl.t_wpon,shl.t_wset, shl.t_wseq, shl.t_item , sl.t_oamt, sh.t_ccur
FROM twhinh431600 shl
INNER JOIN twhinh430600 shh ON shl.t_shpm = shh.t_shpm
--INNER JOIN ttdsls401600 sl ON shl.t_worn = sl.t_orno AND shl.t_wpon = sl.t_pono AND shl.t_wseq = sl.t_sqnb
LEFT OUTER JOIN ttdsls401600 sl ON (shl.t_worn = sl.t_orno AND shl.t_wpon = sl.t_pono AND shl.t_rseq = sl.t_sqnb)
INNER JOIN ttdsls400600 sh ON sl.t_orno = sh.t_orno
LEFT OUTER JOIN ttccom130600 a ON shl.t_stad = a.t_cadr
LEFT OUTER JOIN ttcmcs080100 c ON shh.t_carr = c.t_cfrw
LEFT OUTER JOIN ttccom139600 ct ON a.t_ccit = ct.t_city


I am trying out this but the tdsls401.oamt is duplicating and not showing correctly in in my multi-occ window.

query.extend.select("whinh430.carr, whinh431.item, whinh431.shpm, whinh431.worn, whinh431.wpon, whinh431.rseq, tdsls401.oamt, tdsls400.ccur ")
query.extend.from("whinh431, whinh430, tdsls401, tdsls400") |, tcmcs080")
query.extend.where( "whinh431.shpm = whinh430.shpm AND " &
"(whinh431.worn REFERS TO tdsls401 AND " &
"whinh431.wpon = tdsls401.pono AND " &
"whinh431.rseq = tdsls401.sqnb ) AND " &
|"tdsls401.orno = tdsls400.orno AND " &
"whinh431.worn = " & quoted.string("NWS000031"))


Regards,
eric

Here is what is happening on my form. Please see attachment.

5398

Any help is greatly appreciated...

I would change the join between whinh431 and tdsls401 to:

"(whinh431.worn = tdsls401.orno AND " &
"whinh431.wpon = tdsls401.pono AND " &
"whinh431.rseq = tdsls401.sqnb)"

And be careful: you have tdsls400 in the FROM clause, but no join on it. This will give you a carthesian product!

Thanks Jaap, I try what you suggested but still same error result on the tdsls401.oamt in my customized session. When I scroll the grid the weirdly amount changes. What might be causing that?

query.extend.select("whinh430.carr, whinh431.item, whinh431.shpm, whinh431.worn, whinh431.wpon, whinh431.rseq, tdsls401.oamt, tdsls401.orno")
query.extend.from("whinh431, whinh430, tdsls401")
query.extend.where( "whinh431.shpm = whinh430.shpm AND " &
"(whinh431.worn = tdsls401.orno AND " &
"whinh431.wpon = tdsls401.pono AND " &
"whinh431.rseq = tdsls401.sqnb ) "

When you scroll in the grid or higlight/or click on the row the amount changes in a weird way...

5399

Hi Eric, I don't understand either why it does not work. Seems to have to do with the relation between execution of the query and building the form.
If you code the following in before.display.object you will see the correct amount:

select tdsls401.oamt
from tdsls401
where tdsls401.orno = :whinh431.worn
and tdsls401.pono = :whinh431.wpon
and tdsls401.sqnb = :whinh431.rseq
as set with 1 rows
selectdo
endselect

But note that this will not always work. Not all shipment lines are for sales orders, multiple shipment lines can refer to same sales order line (in case of BOM lines), etc.

Thanks Jaap that did the trick but I cannot still figure out why it is happening to the query where it seems to look tight based on the relationship define. As always you are big help.

Hello,

I am having a related issue with the solution above. The before.display.object code I've inserted might be causing those field not to be searcheable in the Web UI interface of the code since tccom100.nama and tccom140.fuln are not part of query.extend or the custom table i've created. Is there away to make the "retrieved fields" in the before.display.object be searchable in Web UI interface. Please refer to my issue in this image.

Filtering is only working for fields that are in the main query of the session. That can either be achieved by putting the fields on the form (only possible if there is a reference) or adding them in the query.extend.select.