SQLtoMVGuide

Below is a quick cheat sheet for querying into MV databases for people with a background in SQL.

Basics

The SELECT verb is the same in both languages but the supporting syntax is different. Here are two examples of simple SELECT queries:

SQL: SELECT * FROM orders;

MV: SELECT ORDERS

Both queries are selecting all orders on file but the output is different. MV will store the data in a temporary list of the keys/ids while SQL saves a temporary result set. In MV, there are no INSERTs or UPDATEs. You can only view data from the query language while editing is reserved for programming languages. While using MV from the "console", if you want to query your data and see it (per SELECT in SQL) you use the verbs LIST (replaces SELECT) and SORT (replaces SSELECT).

Single and double quotes are important in MV. Single quotes are used to denote that you are querying for a specific key. If I wanted to select the order with the id of 10 I would query: SELECT ORDERS '10'. Double quotes are used to compare values of a field: SELECT ORDERS WITH FNAME = "John".

Every query in MV where you select based on the value of a field is treated similar to a SQL LIKE query. The major difference is that wildcards in MV are direction specific. Examples:

SQL: SELECT * FROM orders WHERE name LIKE 'John%'

MV: SELECT ORDERS WITH NAME "John]"

MV: SELECT ORDERS WITH NAME "John[" #This is invalid as the '[' wildcard searches to the left.

SQL: SELECT * FROM orders WHERE name LIKE '%Jon%'

MV: SELECT ORDERS WITH NAME "[Jon]"

Examples

Basic SELECT

SQL: SELECT * FROM orders

MV: SELECT ORDERS

SELECTing specific columns/attributes. *NOTE: LIST can not be captured in a program as resultset

SQL: SELECT id,name_on_order,total FROM orders

MV: LIST ORDERS ID NAME TOTAL

Using WHERE to granularly SELECT data. Also highlights AND/OR

SQL: SELECT * FROM orders WHERE total > 100.00 AND tax=0 OR shipping=0

MV: SELECT ORDERS WITH TOTAL > "100.00" AND WITH TAX "0" OR WITH SHIPPING EQ "0"

MV: SELECT ORDERS IF TOTAL > "100.00" AND IF TAX "0" OR IF SHIPPING = "0"

ORDER-BY Ascending

SQL: SELECT * FROM orders ORDER BY total

MV: SSELECT ORDERS BY TOTAL

ORDER-BY Descending

SQL: SELECT * FROM orders ORDER BY total DESC

MV: SSELECT ORDERS BY-DSND TOTAL

LIMIT *NOTE: LIMIT 20,40 functionality is not possible

SQL: SELECT * FROM orders LIMIT 10

MV: SELECT ORDERS (L,10)

SELECT col that's not equal to value

SQL: SELECT * FROM orders WHERE name_on_order<>'John Doe'

MV: SELECT ORDERS WITH NAME NOT "John Doe"

MV: SELECT ORDERS WITH NAME NE "John Doe"

MV: SELECT ORDERS WITH NAME # "John Doe"

SELECTing based on NULL values

SQL: SELECT * FROM orders WHERE name_on_order IS NULL

MV: SELECT ORDERS WITH NO NAME

MV: SELECT ORDERS WITHOUT NAME

SELECT using IN. Translates to OR statements in MV

SQL: SELECT * FROM orders WHERE name_on_order IN ('John','Jon','Jhon')

MV: SELECT ORDERS WITH NAME "John" "Jon" "Jhon"

SELECT using LIKE. %% == [] ***NOTE: MV wildcards are direction specific.

SQL: SELECT * FROM orders WHERE name_on_order LIKE '% Doe'

MV: SELECT ORDERS WITH NAME "[ Doe"

Notes

Still looking to know how to do:

1. How to SELECT based on sub values. Ex: SELECT ORDERS WITH NAME<1> "John" AND WITH NAME<2> "Doe"

2. Obtain something similar to a resultset and not just a list of keys to then LOOP/READ