You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openoffice.apache.org by ks...@apache.org on 2014/09/25 01:27:59 UTC
svn commit: r1627435 -
/openoffice/trunk/main/helpcontent2/source/text/shared/explorer/database/02010100.xhp
Author: kschenk
Date: Wed Sep 24 23:27:59 2014
New Revision: 1627435
URL: http://svn.apache.org/r1627435
Log:
Added information on date types to correspond to SQL2. Reformatted information a bit.
Modified:
openoffice/trunk/main/helpcontent2/source/text/shared/explorer/database/02010100.xhp
Modified: openoffice/trunk/main/helpcontent2/source/text/shared/explorer/database/02010100.xhp
URL: http://svn.apache.org/viewvc/openoffice/trunk/main/helpcontent2/source/text/shared/explorer/database/02010100.xhp?rev=1627435&r1=1627434&r2=1627435&view=diff
==============================================================================
--- openoffice/trunk/main/helpcontent2/source/text/shared/explorer/database/02010100.xhp (original)
+++ openoffice/trunk/main/helpcontent2/source/text/shared/explorer/database/02010100.xhp Wed Sep 24 23:27:59 2014
@@ -321,6 +321,7 @@
<paragraph role="heading" id="hd_id3148926" xml-lang="en-US" level="3" l10n="U" oldref="37">Formulating filter conditions</paragraph>
<section id="abfragekriterien">
<paragraph role="paragraph" id="par_id3153162" xml-lang="en-US" l10n="U" oldref="38">When formulating filter conditions, various operators and commands are available to you. Apart from the relational operators, there are SQL-specific commands that query the content of database fields. If you use these commands in the $[officename] syntax, $[officename] automatically converts these into the corresponding SQL syntax. You can also enter the SQL command directly. The following tables give an overview of the operators and commands:</paragraph>
+
<table id="tbl_id3152803">
<tablerow>
<tablecell colspan="" rowspan="">
@@ -401,6 +402,7 @@
</tablecell>
</tablerow>
</table>
+
<table id="tbl_id3153376">
<tablerow>
<tablecell colspan="" rowspan="">
@@ -575,6 +577,15 @@
<paragraph role="paragraph" id="par_id3150948" xml-lang="en-US" l10n="U" oldref="119">returns field names with the field content "Ms."</paragraph>
</tablecell>
</tablerow>
+
+<tablerow>
+<tablecell colspan="" rowspan="">
+<paragraph role="paragraph" id="par_id315379A" xml-lang="en-US" l10n="U" oldref="118"><'2001-01-10'</paragraph>
+</tablecell>
+<tablecell colspan="" rowspan="">
+<paragraph role="paragraph" id="par_id315094A" xml-lang="en-US" l10n="U" oldref="119">returns dates that occurred before January 10, 2001"</paragraph>
+</tablecell>
+</tablerow>
<tablerow>
<tablecell colspan="" rowspan="">
<paragraph role="paragraph" id="par_id3150333" xml-lang="en-US" l10n="U" oldref="120">LIKE 'g?ve'</paragraph>
@@ -617,73 +628,88 @@
</tablerow>
</table>
</section>
+
+<paragraph role="paragraph" id="par_id3146073" xml-lang="en-US" l10n="U" oldref="268">
+<emph>Like </emph>Escape Sequence: {escape 'escape-character'}</paragraph>
+<paragraph role="paragraph" id="par_id3150661" xml-lang="en-US" l10n="U" oldref="269">Example: select * from Item where ItemName like 'The *%' {escape '*'}</paragraph>
+<paragraph role="paragraph" id="par_id3148541" xml-lang="en-US" l10n="U" oldref="270">The example will give you all of the entries where the item name begins with 'The *'. This means that you can also search for characters that would otherwise be interpreted as placeholders, such as *, ?, _, % or the period.</paragraph>
+<paragraph role="paragraph" id="par_id3150572" xml-lang="en-US" l10n="U" oldref="271">
+<emph>Outer Join</emph> Escape Sequence: {oj outer-join}</paragraph>
+<paragraph role="paragraph" id="par_id3156052" xml-lang="en-US" l10n="U" oldref="272">Example: select Article.* from {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR}</paragraph>
+<paragraph role="heading" id="hd_id3153674" xml-lang="en-US" level="3" l10n="U" oldref="109">Querying text fields</paragraph>
+<paragraph role="paragraph" id="par_id3149134" xml-lang="en-US" l10n="U" oldref="110">To query the content of a text field, you must put the expression between single quotes. The distinction between uppercase and lowercase letters depends on the database in use. LIKE, by definition, is case-sensitive (though some databases don't see it that strict).</paragraph>
+
+<paragraph role="heading" id="hd_id3149302" xml-lang="en-US" level="3" l10n="U" oldref="111">Querying date fields</paragraph>
+<!--//kls begin edits -->
<paragraph role="paragraph" id="par_id3157998" xml-lang="en-US" l10n="U" oldref="252">
-<emph>Date fields</emph> are represented as #Date# to clearly identify them as dates. The date condition will be reproduced in the resulting SQL statement in the following ODBC - compliant way:</paragraph>
+<emph>Date fields</emph> are represented as #Date# to clearly identify them as dates. Date, time and date/time constants (literals) used in conditions can be of either the SQL Escape Syntax type, or default SQL2 syntax.</paragraph>
+
<table id="tbl_id3156186">
<tablerow>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3153734" xml-lang="en-US" l10n="U" oldref="253">Date</paragraph>
-</tablecell>
+<paragraph role="tablehead" id="par_id31537341" xml-lang="en-US" l10n="U" oldref="253">Date Type Element</paragraph>
+</tablecell>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3159131" xml-lang="en-US" l10n="U" oldref="254">{D'YYYY-MM-DD'}</paragraph>
+<paragraph role="tablehead" id="par_id31537342" xml-lang="en-US" l10n="U" oldref="253">SQL Escape syntax #1 - may be obsolete</paragraph>
</tablecell>
-</tablerow>
-<tablerow>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3153937" xml-lang="en-US" l10n="U" oldref="255">Date time</paragraph>
+<paragraph role="tablehead" id="par_id31537343" xml-lang="en-US" l10n="U" oldref="253">SQL Escape syntax #2</paragraph>
</tablecell>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3146090" xml-lang="en-US" l10n="U" oldref="256">{D'YYYY-MM-DD HH:MM:SS'}</paragraph>
+<paragraph role="tablehead" id="par_id31537344" xml-lang="en-US" l10n="U" oldref="253">SQL2 syntax</paragraph>
</tablecell>
</tablerow>
+
<tablerow>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3155947" xml-lang="en-US" l10n="U" oldref="257">Time</paragraph>
-</tablecell>
+<paragraph role="paragraph" id="par_id315913111" xml-lang="en-US" l10n="U" oldref="254">Date</paragraph>
+</tablecell>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3150964" xml-lang="en-US" l10n="U" oldref="258">{D'HH:MM:SS'}</paragraph>
+<paragraph role="paragraph" id="par_id315913112" xml-lang="en-US" l10n="U" oldref="254">{D'YYYY-MM-DD'}</paragraph>
</tablecell>
-</tablerow>
-</table>
-<paragraph role="paragraph" id="par_id3151220" xml-lang="en-US" l10n="U" oldref="260">$[officename] also supports the following <emph>Escape sequences</emph> known from ODBC and JDBC:</paragraph>
-<table id="tbl_id3154130">
-<tablerow>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3157975" xml-lang="en-US" l10n="U" oldref="261">Date</paragraph>
+<paragraph role="paragraph" id="par_id314975313" xml-lang="en-US" l10n="U" oldref="262">{d 'YYYY-MM-DD'}</paragraph>
</tablecell>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3149753" xml-lang="en-US" l10n="U" oldref="262">{d 'YYYY-MM-DD'}</paragraph>
+<paragraph role="paragraph" id="par_id314975314" xml-lang="en-US" l10n="U">'YYYY-MM-DD'</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3156318" xml-lang="en-US" l10n="U" oldref="263">Time</paragraph>
+<paragraph role="paragraph" id="par_id31559471" xml-lang="en-US" l10n="U" oldref="257">Time</paragraph>
</tablecell>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3151280" xml-lang="en-US" l10n="U" oldref="264">{t 'HH:MI:SS[.SS]'} - [ ] optional</paragraph>
+<paragraph role="paragraph" id="par_id31559472" xml-lang="en-US" l10n="U" oldref="258">{D'HH:MM:SS'}</paragraph>
</tablecell>
-</tablerow>
+<tablecell colspan="" rowspan="">
+<paragraph role="paragraph" id="par_id31559473" xml-lang="en-US" l10n="U" oldref="264">{t 'HH:MI:SS[.SS]'} </paragraph>
+</tablecell>
+<tablecell colspan="" rowspan="">
+<paragraph role="paragraph" id="par_id31559474" xml-lang="en-US" l10n="U" oldref="264">'HH:MI:SS[.SS]' </paragraph>
+</tablecell>
+</tablerow>
<tablerow>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3153264" xml-lang="en-US" l10n="U" oldref="265">DateTime</paragraph>
+<paragraph role="paragraph" id="par_id31509641" xml-lang="en-US" l10n="U" oldref="257">DateTime</paragraph>
</tablecell>
<tablecell colspan="" rowspan="">
-<paragraph role="paragraph" id="par_id3153981" xml-lang="en-US" l10n="U" oldref="266">{ts 'YYYY-MM-DD HH:MI:SS[.SS]'} - [ ] optional</paragraph>
+<paragraph role="paragraph" id="par_id31509642" xml-lang="en-US" l10n="U" oldref="258">{D'YYYY-MM-DD HH:MM:SS'}</paragraph>
</tablecell>
-</tablerow>
+<tablecell colspan="" rowspan="">
+<paragraph role="paragraph" id="par_id31509643" xml-lang="en-US" l10n="U" oldref="264">{ts 'YYYY-MM-DD HH:MI:SS[.SS]'} </paragraph>
+</tablecell>
+<tablecell colspan="" rowspan="">
+<paragraph role="paragraph" id="par_id31509644" xml-lang="en-US" l10n="U" oldref="264">'YYYY-MM-DD HH:MI:SS[.SS]' </paragraph>
+</tablecell>
+</tablerow>
</table>
-<paragraph role="paragraph" id="par_id3149539" xml-lang="en-US" l10n="U" oldref="267">Example: select {d '1999-12-31'} from world.years</paragraph>
-<paragraph role="paragraph" id="par_id3146073" xml-lang="en-US" l10n="U" oldref="268">
-<emph>Like </emph>Escape Sequence: {escape 'escape-character'}</paragraph>
-<paragraph role="paragraph" id="par_id3150661" xml-lang="en-US" l10n="U" oldref="269">Example: select * from Item where ItemName like 'The *%' {escape '*'}</paragraph>
-<paragraph role="paragraph" id="par_id3148541" xml-lang="en-US" l10n="U" oldref="270">The example will give you all of the entries where the item name begins with 'The *'. This means that you can also search for characters that would otherwise be interpreted as placeholders, such as *, ?, _, % or the period.</paragraph>
-<paragraph role="paragraph" id="par_id3150572" xml-lang="en-US" l10n="U" oldref="271">
-<emph>Outer Join</emph> Escape Sequence: {oj outer-join}</paragraph>
-<paragraph role="paragraph" id="par_id3156052" xml-lang="en-US" l10n="U" oldref="272">Example: select Article.* from {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR}</paragraph>
-<paragraph role="heading" id="hd_id3153674" xml-lang="en-US" level="3" l10n="U" oldref="109">Querying text fields</paragraph>
-<paragraph role="paragraph" id="par_id3149134" xml-lang="en-US" l10n="U" oldref="110">To query the content of a text field, you must put the expression between single quotes. The distinction between uppercase and lowercase letters depends on the database in use. LIKE, by definition, is case-sensitive (though some databases don't see it that strict).</paragraph>
-<paragraph role="heading" id="hd_id3149302" xml-lang="en-US" level="3" l10n="U" oldref="111">Querying date fields</paragraph>
-<paragraph role="paragraph" id="par_id3150510" xml-lang="en-US" l10n="U" oldref="112">Even if you want to filter by a date, you must place the expression between single quotation marks. The following formats are valid: YYYY-MM-DD HH:MM:SS and YYYY/MM/DD HH:MM:SS as well as YYYY.MM.DD HH:MM:SS</paragraph>
+
+
+ <paragraph role="paragraph" id="par_id3149539" xml-lang="en-US" l10n="U" oldref="267">Example: select {d '1999-12-31'} from world.years</paragraph>
+ <paragraph role="paragraph" xml-lang="en-US" l10n="U">Example: select * from mytable where years='1999-12-31' </paragraph>
+<paragraph role="paragraph" id="par_id3150510" xml-lang="en-US" l10n="U" oldref="112">All date expressions (literals) must be enclosed with single quotation marks. (Consult the reference for the particular database and connnector you are using for more details.)</paragraph>
+
+ <!--//kls end moved -->
<paragraph role="heading" id="hd_id3150427" xml-lang="en-US" level="3" l10n="U" oldref="228">Querying Yes/No fields</paragraph>
<paragraph role="paragraph" id="par_id3149523" xml-lang="en-US" l10n="CHG" oldref="229">To query Yes/No fields, use the following syntax for dBASE tables:</paragraph>
<table id="tbl_id3150146">