Below is a quick cheat sheet for querying into MV databases for people with a background in SQL.
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]"
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"
SQL: SELECT * FROM orders ORDER BY total
MV: SSELECT ORDERS BY TOTAL
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"
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