WHERE

go to: HOME, METHODS or PLUGINS

Where Clause

We belive that the most basic situations is supported.

The Where Clause is be defined by "array_ref" values, no others options exists. We must remember that some engines explore the sequence in the Where Clause to establish de best way to extract data (index and match condition), basd on this definition, the best choice is the "array_ref" format.

If any item (or sub-item) specifies "hash_ref", the process is aborted with "SQL_SIMPLE_RC_SYNTAX" as the return code.

Operators: The follow operators was been builtin:

  Operator      Description
  undef         "is null"
  =             "equal" condition (default)
  !             "not equal"
  !=            "not equal"
  <>            "not equal"
  >             "greater than"
  <             "less than"
  >=            "greater or equal than"
  <=            "less or equal than"
  !>            "less or equal than"
  !<            "greater or equal than"
  %%            "like %[value]%"
  ^%            "like [value]%"
  %^            "like %[value]"
  ^^            "like [value]"          # no '%' will be added
  !%%           "not like %[value]%"
  !^%           "not like [value]%"
  !%^           "not like %[value]"
  !^^           "not like [value]"      # no '%' will be added
  \             "escape value"          # no quote will be applied

Example1:

  SQL::SimpleOps->Select
  (
     where =>
     [
       fld000 => undef,                                 # ... fld000 IS NULL

       fld010 => 1,                                     # ... fld010 = '1'

       fld020 => [ '>', 1 ],                            # ... fld020 > '1'

       fld021 => [ '!', undef ],                        # ... fld021 NOT NULL

       fld030 => [ 1, 2 ],                              # ... fld030 IN ('1','2')

       fld031 => [ '!', 1, 2 ],                         # ... fld031 NOT IN ('1','2')

       fld040 => [ 1, '..', 3 ],                        # ... fld040 BETWEEN ('1','3')

       fld041 => [ '!', 1, '..', 3 ],                   # ... fld041 NOT BETWEEN ('1','3')

     [ fld050 => 1, fld051 => 2 ],                      # ... ( fld050 = '1' and fld051 = '2' )

     [ fld060 => 1, 'or', fld061 => 2 ],                # ... ( fld060 = '1' or fld061 = '2' )

     [ fld070 => 1, 'and', fld071 => 2 ],               # ... ( fld070 = '1' and fld071 = '2' )

       fld080 => 1, 'or', [ fld081 => 2, fld082 => 3 ], # ... fld080 = '1' or ( fld081 = '2' and fld082 = '3' )

       fld090 => 1, [ fld091 => 2, 'or', fld092 => 3 ], # ... fld090 = '1' and ( fld091 = '2' or fld092 = '3' )

       fld100 => [ '%%', 'abc' ],                       # ... fld100 LIKE '%abc%'

       fld110 => [ '^%', 'abc' ],                       # ... fld110 LIKE 'abc%'

       fld120 => [ '%^', 'abc' ],                       # ... fld120 LIKE '%abc'

       fld121 => [ '^^', 'ab%de' ],                     # ... fld121 LIKE 'ab%de'               # use '%' if required

       fld130 => '\\concat(fld130,"xxxx")',             # ... fld130 = concat(fld130,"xxxx")    # without quote

       fld200 => '\\fld210',                            # ... fld200 = fld210           # without quote

       fld220 => [ "!", '\\fld230' ],                   # ... fld220 != fld240          # without quote

     ],

  );

IMPORTANT: Do not use aliases column name on the right side. The translation for this side does not apply.

BEWARE: The operators (if used) must be the first field on the "array_ref".

ENDED