Remove Record from Query Buffer without delete record

Jochen0911

New Member
Hi,

is there a possibility to use query-buffer and delete buffer records without delete the physical records?

I want to filter my dynamic result in query buffer.

query don´t support 'can-find'. I want to go through my records in query buffer, execute a can-find of buffer record somewhere else, and if it´s true, i want to remove buffer record in query without delete record in database.

Thank you
 

TomBascom

Curmudgeon
It's just code. You can do anything with code.

If you show some actual code we can probably make actual suggestions for accomplishing your desire.
 

Jochen0911

New Member
I ´am looking forward :cool:



The code snippet shows only the important parts to build query with subquerys. At the bottom you see to build the buffers for the results. I support only two subqueries. In the project i can avoid the subqueries, when it´s a 'can-find'. That means i have the main table for the query like 'for each customer where ....'. My subquery will be 'and not can-find (first <table> where ....)



I tried to replace my part with 'and not can-find ...' instead ', each <table>'. But than i got an progress error. So i have the idea to go thruw my buffer with the main query 'lvh_buffer' and make it somehow shorter (filter with ... and not can-find ...)

(I must short the code of 1.000 chars..)

Thank you

Jochen







Code:
 /*---  Grundselektion fuer Regel aufbauen  ---*/

    assign

      lvc_basistabelle = entry(lookup(string(gb_mdm06_yh_mdm_regel.bereich),{&YH_Bereich_Tabellen}) + 1, {&YH_Bereich_Tabellen})

      lvc_basis_obj    = entry(lookup(string(gb_mdm06_yh_mdm_regel.bereich),{&YH_Bereich_Tabellen_OBJ}) + 1, {&YH_Bereich_Tabellen_OBJ}).



    if lvc_basistabelle = gb_mdm06_yh_mdm_regelwerte.tabellenname

      /* Haupttabelle kann oefters vorkommen. Wir brauchen aber nur einmal */

      and not can-find (first ttyquery where ttyquery.haupt > '':U)

    then do:



      /*---  Query fuer den Regelverstoss aufbauen  ---*/

      lvc_query = 'for each ':U + lvc_basistabelle

                + ' where ':U   + lvc_basistabelle

                + '.firma = ':U + '"':U + pa-firma + '"':U.



      /*---  Generische Selektion der Uebergabewerte aufbauen  ---*/



      /* Hole Basis */

      /* lvc_query = lvc_query + ' no-lock':U. */



      /* Merke mir die Selektion */

      create ttyquery.

      assign

        ttyquery.haupt    = lvc_query

        ttyquery.tabelle  = lvc_basistabelle

        ttyquery.feld     = gb_mdm06_yh_mdm_regelwerte.feldname

        ttyquery.soll     = gb_mdm06_yh_mdm_regelwerte.wert

        ttyquery.operator = entry(gb_mdm06_yh_mdm_regelwerte.vergleichsoperator,{&YH_Operator}).



    end. /* if lvc_basistabelle = gb_mdm06_yh_mdm_regelwerte.tabellenname */







    /*---  Wenn Nebentabelle, dann Baisis suchen und Subquery aufbauen  ---*/



    if lvc_basistabelle <> gb_mdm06_yh_mdm_regelwerte.tabellenname

      /* Die Relation zur Nebentabelle wird nur einmal benoetigt */

      and not can-find (first ttyquery where ttyquery.tabelle = gb_mdm06_yh_mdm_regelwerte.tabellenname)

    then do:



      assign

        lvc_tabelle  = gb_mdm06_yh_mdm_regelwerte.tabellenname

        lvc_subquery = '':U.



      &IF {&PA_VERSION} LT '6':U

      &THEN     



      /* Ueber die Relationsonfos die Zusammenhaenge aufbauen */

      for each  yd_relspalte

          where yd_relspalte.tabellenname_1 = lvc_basistabelle

          and   yd_relspalte.tabellenname_2 = gb_mdm06_yh_mdm_regelwerte.tabellenname

          no-lock

          on error  undo, return error

          on endkey undo, return error:



        assign

          lvc_subquery = (if   lvc_subquery = '':U

                          then ' where ':U

                          else lvc_subquery + ' and ':U)

          lvc_subquery = lvc_subquery

                       +  yd_relspalte.tabellenname_2 + '.':U + yd_relspalte.spaltenname_2

                       + ' = ':U + yd_relspalte.tabellenname_1 + '.':U + yd_relspalte.spaltenname_1.



      end. /* for each d_relspalte */



      &ELSE

        find first ttDRC_Relation

             where ttDRC_Relation.Parent_DRC_Table_ID = lvc_basistabelle

             and   ttDRC_Relation.Child_DRC_Table_ID  = gb_mdm06_yh_mdm_regelwerte.tabellenname

             no-lock no-error.

 

        /* Alles ist moeglich */

        if available ttDRC_Relation

        then

        for each  ttDRC_RelationField

            where ttDRC_RelationField.DRC_Relation_Obj = ttDRC_Relation.DRC_Relation_Obj

            no-lock,

            first DRC_Field

            where DRC_Field.DRC_Field_Obj              = ttDRC_RelationField.Parent_DRC_Field_Obj

            no-lock,

            first ygb_mdm01_DRC_Field

            where ygb_mdm01_DRC_Field.DRC_Field_Obj    = ttDRC_RelationField.Child_DRC_Field_Obj

            no-lock

            on error  undo, return error

            on endkey undo, return error:

 

          assign

            lvc_subquery = (if   lvc_subquery = '':U

                            then ' where ':U

                            else lvc_subquery + ' and ':U)

            lvc_subquery = lvc_subquery

                         + ttDRC_Relation.child_drc_table_id + '.':U + ygb_mdm01_DRC_Field.drc_field_id

                         + ' = ':U + ttDRC_Relation.parent_drc_table_id + '.':U + DRC_Field.drc_field_id.

                        

        end. /* for each  ttDRC_RelationField */



      &ENDIF



      /* Unterstuetze s_adresse */

      if   gb_mdm06_yh_mdm_regelwerte.tabellenname = 's_adresse':U

      then assign

             lvc_subquery = (if   lvc_subquery = '':U

                             then ' where ':U

                             else lvc_subquery + ' and ':U)

             lvc_subquery = lvc_subquery + ' s_adresse.adressnr = ':U + lvc_basistabelle + '.adressnr ':U.



      lvc_query = lvc_query + ', each ' + gb_mdm06_yh_mdm_regelwerte.tabellenname + ' ':U + lvc_subquery.



      /* Abfrage merken */

      create ttyquery.

      assign

        ttyquery.neben    = ', each '

                          + gb_mdm06_yh_mdm_regelwerte.tabellenname

                          + (if lvl_outer_join = yes

                             then ' outer-join ':U

                             else '':U)

                          + lvc_subquery

        ttyquery.tabelle  = gb_mdm06_yh_mdm_regelwerte.tabellenname

        ttyquery.feld     = gb_mdm06_yh_mdm_regelwerte.feldname

        ttyquery.soll     = gb_mdm06_yh_mdm_regelwerte.wert

        ttyquery.operator = (if gb_mdm06_yh_mdm_regelwerte.ergebnisfeld = yes

                             then '>':U

                             else entry(gb_mdm06_yh_mdm_regelwerte.vergleichsoperator,{&YH_Operator})).



    end. /* if lvc_basistabelle <> gb_mdm06_yh_mdm_regelwerte.tabellenname */



    /* Checkbox OUTER aktivieren */

    &if '{&pa-ProgrammTyp}':U <> 'JobProcedure':U

    &then

    utl_outer:sensitive in frame {&frame-name} = (if lvc_subquery > '':U

                                                  and index(lvc_subquery,'s_adresse':U) = 0

                                                  then yes

                                                  else no).

    &endif



  end. /* if first-of (gb_mdm06_yh_mdm_regelwerte.tabellenname) */









/* Later get query results */



find first ttyquery where ttyquery.haupt > '':U.



lvc_query = lvc_hauptquery + ' no-lock ':U.



  create buffer lvh_buffer for table lvc_basistabelle.

  create query lvh_query.



  if {&YH_JOB_Protokoll} = yes

  then put stream gs_prot unformatted skip 'Hauptquery: ':U lvc_hauptquery skip

                                           'Nebenquery: ':U lvc_nebenquery skip.



  if lvc_nebenquery > '':U

  then do:



    lvc_query = lvc_query + lvc_nebenquery + ' no-lock':U.



      if num-entries(lvc_tabellenliste) = 1

      then do:

       lvc_tabellenname = entry(1,lvc_tabellenliste).

       create buffer lvh2_buffer for table lvc_tabellenname.

      end.

 

      if num-entries(lvc_tabellenliste) = 2

      then do:

        lvc_tabellenname = entry(1,lvc_tabellenliste).

        create buffer lvh2_buffer for table lvc_tabellenname.

        lvc_tabellenname = entry(2,lvc_tabellenliste).

        create buffer lvh3_buffer for table lvc_tabellenname.

      end.

 

      if num-entries(lvc_tabellenliste) = 1

      then lvh_query:set-buffers(lvh_buffer,lvh2_buffer).

      if num-entries(lvc_tabellenliste) = 2

      then lvh_query:set-buffers(lvh_buffer,lvh2_buffer,lvh3_buffer).



  end.

  else lvh_query:set-buffers(lvh_buffer).



  lvh_query:query-prepare(lvc_query).

  lvh_query:query-open.

  lvh_query:get-first.
 

TomBascom

Curmudgeon
A BUFFER is a pointer to a single record. It is not the result set from a QUERY. To filter your results you need to focus on writing a WHERE clause that selects the records you want. There is no post-processing to remove records from a QUERY result set.

You seem to be comfortable with a static query using NOT CAN-FIND() to filter records and you are looking for a dynamic equivalent.

If I understand what you are trying to do it is something like:

"show me all of the customers who have never placed an order"

Is that correct?

Since there is no index for "NeverPlacedAnOrder" this is going to require a table scan of "customers" and quite a few probes of "orders". The static version of that might be:

Code:
for each customer no-lock where not can-find( first order where order.custnum = customer.custnum ):
  display customer.custnum customer.name.
end.

Personally I find WHERE clauses that include NOT to be profoundly ugly. I much prefer to phrase things in a positive manner. Probably due to my generally sunny and optimistic disposition. Thus I would write the code as:

Code:
for each customer no-lock:
  if can-find( first order where order.custnum = customer.custnum ) then next.
  display customer.custnum customer.name.
end.

Aside from pleasing my happy go lucky nature this also makes it a lot clearer what is really happening with such a query. For instance, there is no illusion that there might be a magical way around the table scan.

None the less - that doesn't help if you want a result set. Perhaps you're planning to feed it to a BROWSE widget or something along those lines. To do that we need some help.

Code:
define variable q as handle no-undo.
define variable b as handle no-undo.

/* this find trigger is the "trick" */

on find of customer do:
  if can-find( first order where order.custnum = customer.custnum ) then
    return error.
end.

create buffer b for table "customer".
create query q.

q:set-buffers( b ).
q:query-prepare( "for each customer no-lock" ).
q:query-open().

/* my silly demo code... */
q:get-next.
do while q:query-off-end = false:
  display b:buffer-field( "custnum" ):buffer-value b:buffer-field( "name" ):buffer-value.
  q:get-next().
end.

/* replace my demo code with your code... */

on find of customer revert.    /* don't forget to revert the trigger! */
 
Last edited:

Osborne

Active Member
As Tom posted "You can do anything with code", and it appears it is possible of mimicking a NOT CAN-FIND in a query. This solution using a single temp-table record as a control record was posted on Progress Community a few years back:

 

TomBascom

Curmudgeon
> (I must short the code of 1.000 chars..)

I'd start by getting rid of the ridiculous Hungarian Notation prefix gobbledygook.
 

TomBascom

Curmudgeon
As Tom posted "You can do anything with code", and it appears it is possible of mimicking a NOT CAN-FIND in a query. This solution using a single temp-table record as a control record was posted on Progress Community a few years back:


That's... um, well... on the one hand I admire it's cleverness. OTOH... it's not exactly a model of clear and obvious ;) I can see the attraction. Like a moth to the flame.
 

Osborne

Active Member
Yes, a solution that is clever but probably belongs in the category of "Even though it is allowed it does not mean you should do it".
 
Top