Examples

Example 1: 

select * from checks where bank='Comm' and account='ck'

and (dates >= '07/01/1999' or checked = 'true')

and (paidtofm like 'Sci%' or paidtofm like '___new%')

order by budget,dates,rec_no

The resulting query will contain all fields from the table checks with the conditions given.

The repository field must be 'Comm', and the account field must be 'ck'. The repository field is called bank.

The paidtofm (payee) field must begin with "Sci". This is the meaning of the % sign in the "like" statement. The or statement means the field can alternatively contain any 3 characters (3 dashes) followed by "new".

You may substitute "advance" for "checks" to query on the advance table.

Example 2:

select * from checks where taxded = 'true'

and (extract(year from dates)=1999 and (extract(month from dates)=8)

and not Gross Is Null

This will give all tax-deductable items for August, 1999. Any value with Gross being 0 is excluded.

Note that it is easier to read the query if each and began on a different line.

Example 3:

select k.*,c.Moneymkt from checks k,chfi c

where k.bank=c.bank and k.account=c.account

and k.taxded='true' and k.currency='$'

and extract(year from dates)=1999

and c.Moneymkt <> 'R'

order by k.budget,k.dates

This will give the tax deductable items for 1999 order by date and budget. The field c.Moneymkt is a field of the table ch_fi which indicates repository type. This query then excludes the corporate transactions. Repository type R is a corporate transaction.

This query is an example of a join. Two tables were joined together, so that a condition on one table can be used for the other table. Here we must specify that the repository field of the chfi table must match the bank field of the checks table.

Example 4:  Update. To run the update command, press the exec  button.

update temp set DBchecked = 'Y' where paidtofm like 'Sci%'

This will change the table temp, setting the field dbchecked to 'Y' when the condition appearing after "where" is met. This query changes a table (the word "update" appears here), and does not give a result. This is why we must use the exec button. The previous examples used the word "select", and so we press the open button.

Be careful with this query. Please do not use it for any table except temp, for it may destroy your data. With temp, you can see the results of the query. If these results are satisfactory, then the update menu button updates the table (checks or advance) from the temp table.

Example 5. Queries giving fields. Instead of select *,  you may  write the fields as:

select dates,paid_to_fm,comments from checks where budget = 'car' order by dates desc

This  will generate a query showing only the fields selected. The order will be by latest date first. Instead of viewing the table grid, you will see the printed form.

Example 6. An Info query. You must type select * from info.

select * from info where key2='Pav' and lineNo=1 order by text_

Example 7. An info query, using two tables.

select i.* from info i,codes j where i.lineno=1

and i.key2=j.ckey and j.ctext = 'Patterns'

order by i.text_