FORMS3 block where clause on same table -> help!

From: Chris Eastwood <ua.ude.ug_at_doowtsae.c>
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 ;-)


************************************************************************
[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!'
Received on Sat Oct 25 1997 - 00:00:00 CEST

Original text of this message