Re: FORMS3 block where clause on same table -> help!

Try it with a 'where exists (select ... )'

-- 
------------
Kind reGards
     \ /   |
      X    |
     / \   x
     Gerard

Chris Eastwood <ua.ude.ug_at_doowtsae.c> schreef in artikel
<62pnav$hg6$1_at_griffin.itc.gu.edu.au>...


> HiYa

> 

> 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?

> 

> 

> ---

> 

> Chris Eastwood                           email the mirror image above

> Software Engineer                          ie->  doowtsae | eastwood

> ITS                                      its a pain I know, but I think

 it'll


> Griffith University                      fool the robot mailers (I


 hope;-)


> Queensland                               if its all too hard, try the


 most


> AUSTRALIA                                obvious address ;-)


> 

> ************************************************************************

> 

> '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!'

>