Hi,
I have developed one display session with main table tipcs025. As per my knoweldge main.table.io section of this session will disable because main table of this session has DAL2. To filter record of this table I have used query.extend in before.program. I am not able to use group by command with query.extend. Kindly suggest me What I have to do for this proble. I am also attaching code .
before.program:
| import("gbpcs030.cprj",cprj)
cprj="8100"

item.qskt = 0
select gbpcs000.whof
from gbpcs000
selectdo
selectempty
endselect
query.extend.select("sum(whina112.qskt):item.qskt,whina112.item")
query.extend.from("whina112")
query.extend.where("whina112.item alike '" & strip$(shiftl$(cprj)) & "%' and "&
"whina112.cwar =:gbpcs000.whof and "&
"whina112.qskt <> 0"&
"group by whina112.item")
query.extension = "tipcs025.item =:whina112.item"


Regards,
Sanjay Kathuria

Sanjay,

You cannot use the 'group by' clause in query extensions.

Here is an alternative example from our developers in our application code-

e.g. envision sales order lines as the main table and you want to only display the lowest position in a list (grouped in a list window). Can this be done in a query.extension? Tried with a nested select but does not appear to be able to select from the same table itself).

It uses a secondary condition to only read the lowest position number line. You have to be really careful of the right index and hint that it will use.

query.extend.where(" fmfoc200._index1 = {fmfoc201.orno} "&
" and fmzoc201._index1 = {fmfoc201.orno,fmfoc201.pono} " &
" and (fmlbd300.load = :e.load and "&
"fmlbd350.shpm = fmlbd300.shpm and "&
"fmfoc201.orno = fmlbd350.orno and "&
"fmfoc201.pono = fmlbd350.fono) and "&
"(fmzoc201.oorg = fmfoc.oorg.man or "&
" ((fmzoc201.oorg = fmfoc.oorg.sls or "&
" fmzoc201.oorg = fmfoc.oorg.pur) "&
" and fmzoc201.orpo = (select min(afmzoc201.orpo) "&
" from fmzoc201 afmzoc201 where "&
" afmzoc201.oorg = fmzoc201.oorg and "&
" afmzoc201.oror = fmzoc201.oror "&
" hint use index 1,3 on afmzoc201)))")

P.S. - Be specific to mention the Baan version, everyone might not be aware of the DAL2 concepts and use code tags to highlight your code snippets.

Hi,

I have solved my problem on the basis of idea which is given by NP Rao. I am aso attachcing code for further reference.

query.extend.select("tipcs025.*")
query.extend.from("tipcs025")
query.extend.where("tipcs025.cprj =:cprj and "&
"tipcs025.item in (select whina112.item from whina112 where whina112.item alike '"&
strip$(shiftl$(cprj)) & "%' and "&
"whina112.cwar=:gbpcs000.whof and "&
"whina112.qskt <> 0 group by whina112.item)")

Regards,
Sanajy

Hi Gurus,

I've a similar situation, where I wish to filter records on the basis of 2 fields in a customized table, say Project and Item, I've multiple records for the same Item under the project and need to show only one entry per Item under the project on Maintain/display multi occurrence session.
Its been working with skip.io("") but now due to some other requirement, I'm using DAL2 and not able to filter the records anymore.
Tried query extension for group by in before.program, but no success.
Can some one please help me to achieve this?
Please let me know if I'm not clear with question.

thanks.
Amit

Hi,

It will be better if you post your code.

What was in after.read before you had the DAL, can be coded in after.get.object() in the DAL.

Hi JaapJD ,

that would effect for all the session with main table. say xyz ( if coded in xyz dal) rt ?
( now i got it ... you can use Session name .. and according code with in DAL) .. ;)


if you have any ... integer or float field ( Item , Project & seqn) then you can use sub-query concept.

" seqn = ( a.max(seqn) from a where a.item = a.item and a.project = a.project)"

in queryextensions

Dear JaapJD and Vamsi,

Thanks for your input, it worked for me.
I've used procesinfo$ inside after.get.object() for session specific result with my after.read of main.io section code in DAL2.

Thanks again.
Amit