ansi sql : between syntax

select emp_id from emptable where emp_id BETWEEN 5 and 10;

This will list
5
6
7
8
9
10

because the range on BETWEEN clause is inclusive of the test values on databases using ANSI SQL.

Error SP2-0734 - unknown command beginning

while running a query that ran fine on toad but failed on the server/sqlplus due to above error.

The solution is simple, just remove all blank lines from your script.

To delete blank lines in vi:
%g/^$/d
(i.e. global change, nothing between beginning of line to end of line, delete)

REGEXP in FIND and REPLACE in Open Office

On More Option in Find, regular expressions can be used.

e.g. to quickly replace period with period and new line.

Macros in Documents on Open Office Aqua

It's easy to record and replay macros on Open Office. I'm currently formatting a document and it involved mundane tasks like setting font format to heading3, converting a paragraph to bulleted list, etc.

Customized macros work well for these.

STEPS
1. Begin to record a macro
Tools>Macros>Record Macro
2. Perform the tasks you want recorded.
3. Click on Stop Recording. "Stop recording" looks inactive on mac but it does work.
4. #3 will open a window asking you to save the macro. Give a name.
5. Macro is now saved. Run it and see everything works as needed.
Tools>Macros>Run Macro
6. Assign this macro to a keyboard shortcut for ease in running. Tools>Macros>Organize Macros>Openoffice Basic
7. Select the macro and click edit.
8. #7 will open the macro in a new window.
9. Tools > Customize
10. Select the shortcut key you want to assign to this macro e.g. Command+J
11. From Category, select your macro's location. It will be OpenOffice org Macros > user
12. From Functions, select the macro. Click Modify.
The shortcut should appear in the Keys window.

Reduce Font in excel with a shortcut key

In MS Word one can use
ctrl + [ to reduce font size
ctrl + ] to increase font size

These do not work in MS Excel.

Following macro reduces any given font size by 2 units. Assign it to a shortcut key e.g. ctrl + p and you're set.

Sub reduceFont()
With Selection.Font
.Size = .Size - 2
End With
End Sub

Count Distinct on Multiple Columns

Some days back I figured following is a valid query. It can be used to get count of distinct occurrences of a column.

select count(distinct colname) from tablename;

But it would not take more than one column. To look up distinct records based on more than one column we can use Oracle's concatenate function - "||". Thus all the columns will be treated as a single entity.

select count(distinct colname1 || colname2 || colname3) from tablename;

A good example where we would want to use above query is to get distinct customer orders from a transaction table that contains customer-name,order-date and order-status.