Subject Re: SQL Statement
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 18 Jul 2018 08:52:58 +0200
Newsgroups dbase.getting-started
Attachment(s) test_in.wfm

On 2018-07-18 6:30 AM, Ken Mayer wrote:
> On 7/17/2018 7:53 PM, Robin Rickard wrote:
 >> Hi All
 >> what is the best way of getting a SQL statement to only show records
>> that are contained in a field.
 >> example: i have a field for staffareas. in this there might be ADH or
>> ABD etc.
 >> A is an area that they work in, but they may also work in E, F etc
 >> Using the "$" in non sql i used to go ... for mstaff $ staffareas
 >> "Select * from staff.dbf where mstaff $ staffarea"  does not work.
 >> Can somebody please guide me right direction.
 >
> SQL does not understand the dBASE use of the dollar sign. It's a
> completely different language. (This is covered, by the way, in Volume 1
> of The dBASE Book ... there's a chapter that discusses doing things in
> SQL that you are used to doing in dBL)
 >
>     "select * from staff where mstaff like '%" + staffarea + "%'"
 >
> The nested quotes are necessary. If there are apostrophes in the data,
> reverse these -- change the double-quotes (") to single-quotes (') (and
> vice versa).
 >
> The percent signs are wildcards -- putting one on either side of the
> contents of the variable staffarea means that value in that variable can
> have anything on either side of it ...
 >

The field staffarea contains ADH or ABD or CFH and so on.  If, say,
mStaff = 'D' the select statement actually needs to be

  cmd = "select * from staff where staffarea like '%"+mstaff+"'%"
  &cmd

This will create a rowset in the selected workarea. The command USE on
its own will clear the workarea.

In a form this sort of selection is ideally met by using a parameter
driven query.  A little example is attached.

Mervyn.



if file('test_in.dbf')
// drop table test_in
endif

if not file('test_in.dbf')
   create table test_in  (id autoinc,emp character(15),staffarea character(15))
//endif

   insert into test_in  (emp,staffarea) values ("Abel","ADH")
   insert into test_in  (emp,staffarea) values ("Baker","AFG")
   insert into test_in  (emp,staffarea) values ("Charlie","ADG")
   insert into test_in  (emp,staffarea) values ("David","BDG")
   insert into test_in  (emp,staffarea) values ("Edward","BDH")
   insert into test_in  (emp,staffarea) values ("Frank","AHF")
endif


** END HEADER -- do not remove this line
//
// Generated on 2018-07-18
//
parameter bModal
local f
f = new test_inForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class test_inForm of FORM
   with (this)
      height = 16.0
      left = 7.5714
      top = 10.1818
      width = 76.1429
      text = ""
   endwith

   this.TEST_IN1 = new QUERY(this)
   with (this.TEST_IN1)
      left = 3.0
      width = 6.0
      height = 1.0
      sql = 'select * from "test_in.DBF" where staffarea like :sa'
      params["sa"] = "%"
      active = true
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.test_in1.rowset
      height = 8.6818
      left = 7.0
      top = 2.8182
      width = 62.0
   endwith

   this.COMBOBOX1 = new COMBOBOX(this)
   with (this.COMBOBOX1)
      onOpen = class::COMBOBOX1_ONOPEN
      height = 1.0
      left = 13.0
      top = 12.5
      width = 17.0
      dataSource = 'array {"A","B","C","D","E","F","G","H"}'
      style = 1        // DropDown
   endwith

   this.PUSHBUTTON1 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON1)
      onClick = class::PUSHBUTTON1_ONCLICK
      height = 1.0909
      left = 47.0
      top = 12.5
      width = 15.2857
      text = "Select"
   endwith

   this.PUSHBUTTON2 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON2)
      onClick = class::PUSHBUTTON2_ONCLICK
      height = 1.0909
      left = 47.0
      top = 14.0
      width = 15.2857
      text = "Show all"
   endwith

   this.rowset = this.test_in1.rowset

   function COMBOBOX1_onOpen()
      this.value = 'Select Area'
      return

   function PUSHBUTTON1_onClick()
       form.test_in1.params['sa'] = '%'+form.combobox1.value+'%'
       form.test_in1.requery()
      return

   function PUSHBUTTON2_onClick()
       form.combobox1.value = 'Select Area'
       form.test_in1.params['sa'] = '%'
       form.test_in1.requery()
      return

endclass