FORMS3 block where clause on same table -> help!
Date: 1997/10/25
Message-ID: <62pnav$hg6$1_at_griffin.itc.gu.edu.au>#1/1
HiYa
[Quoted] I have a (I thought) very simple FORM 3 where I'm trying to filter rows from a table that are to be displayed to the user.
In enter query the user is to input a pay period and accept the query
Now, I don't just wan't to select every row for that period, I also need to further filter the selection.
Here's where I loose it the selected rows I want are those rows where the %_CNTRB of the _current period_ are different to the one in the _previous_ pay period (or there is already an identified exception code (EXPT_CD is not null) too easy).
So to get the records that I'm after from the table in SQL I can:
select blah, blah, balh
from FN_SUPER_DTL a, FN_SUPER_DTL b
where a.PAY_PRD_NR = b.PAY_PRD_NR -1 and a.EMP_IDN = b.EMP_IDN and a.REC_TYP = b.REC_TYP and ( (a.EMP_CNTRB != b.EMP_CNTRB or a.PRE_TAX_CNTRB != b.PRE_TAX_CNTRB or a.EMP_VLNTR_CNTRB != b.EMP_VLNTR_CNTRB or a.CMPY_CNTRB != b.CMPY_CNTRB ) or (a.EXPT_CD is not null)
)
/
the pertinate parts of the table are:
FN_SUPER_DTL
Name Null? Type ------------------------------- -------- ----
*PAY_PRD_NR NOT NULL NUMBER(8)
*REC_TYP NOT NULL NUMBER(2)
*EMP_IDN NOT NULL NUMBER(10)
PRE_TAX_CNTRB NOT NULL NUMBER(8) EMP_VLNTR_CNTRB NOT NULL NUMBER(8)
so how can I do this in my form?
I thought of a Default Where/Order By field, but how do I refernce another part of the same block????
Another thought was to have an explicit select that operated like the SQL to select into the BLOCK, but I'm not so sure of how to do this, my first attempt (selecting into the BLOCK.VAR) fails with too many rows ... predictable really
anyone?
--- [Quoted] Chris Eastwood email the mirror image above Software Engineer ie-> doowtsae | eastwood ITS its a pain I know, but I think it'll [Quoted] Griffith University fool the robot mailers (I hope;-) Queensland if its all too hard, try the most AUSTRALIA obvious address ;-)Received on Sat Oct 25 1997 - 00:00:00 CEST
************************************************************************
[Quoted] 'But oh, beamish nephew, beware of the day, If your Snark be a Boojum! For then You will softly and suddenly vanish away, And never be met with again!'