View [examples]

Creates a logical view based on one or more tables
CREATE_VIEW
// Create a view based on price being over $10
CREATE VIEW OverTen AS;
SELECT * FROM orders WHERE price > 10
 
// Create a view based on price being over $20
CREATE SQL VIEW OverTwenty AS;
SELECT * FROM orders WHERE price > 20

https://www.lianja.com/doc/index.php/CREATE_VIEW

https://www.lianja.com/doc/index.php/SQL_CREATE_VIEW


Frees up all system resources allocated to a view
DROP_VIEW

 

DROP VIEW OverTen

https://www.lianja.com/doc/index.php/DROP_VIEW


Advertisements

Transactions [examples]

Disable journaling in a specified workarea

// Clear BIJ in workarea suppliers
reset in suppliers

https://www.lianja.com/doc/index.php/RESET_IN


Begin transaction Before Image Journaling

procedure recovery
 
rollback
if rollback()
    dialog box "Rollback was ok."
else
    dialog box "Rollback not completed."
endif
 
return
 
use accounts
on error do recovery
begin transaction
    delete first 15
    insert
    replace all t1 with (t2*t3)/100
    list
end transaction
if completed()
    dialog box "Transaction completed"
else
    dialog box "Errors occurred during transaction"
endif

https://www.lianja.com/doc/index.php/BEGIN_TRANSACTION

 


End transaction Before Image Journaling

procedure recovery
 
rollback
if rollback()
    dialog box "Rollback was ok."
else
    dialog box "Rollback not completed."
endif
 
return
 
use accounts
on error do recovery
begin transaction
    delete first 15
    insert
    replace all t1 with (t2*t3)/100
    list
end transaction
if completed()
    dialog box "Transaction completed"
else
    dialog box "Errors occurred during transaction"
endif

https://www.lianja.com/doc/index.php/END_TRANSACTION


Restore tables to their condition at the beginning of a transaction

procedure recovery
 
rollback
if rollback()
    dialog box "Rollback was ok."
else
    dialog box "Rollback not completed."
endif
 
return
 
use accounts
on error do recovery
begin transaction
    delete first 15
    insert
    replace all t1 with (t2*t3)/100
    list
end transaction
if completed()
    dialog box "Transaction completed"
else
    dialog box "Errors occurred during transaction"
endif

https://www.lianja.com/doc/index.php/ROLLBACK


Verify success of a transaction rollback

procedure recovery
 
rollback
if rollback()
    dialog box "Rollback was ok."
else
    dialog box "Rollback not completed."
endif
 
return
 
use accounts
on error do recovery
begin transaction
    delete first 15
    insert
    replace all t1 with (t2*t3)/100
    list
end transaction
if completed()
    dialog box "Transaction completed"
else
    dialog box "Errors occurred during transaction"
endif

https://www.lianja.com/doc/index.php/ROLLBACK()


Flag the beginning of a transaction

BEGIN TRANSACTION trans1
  INSERT INTO customer;
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
    CITY, STATE, ZIP,LIMIT, START_DATE);
    VALUES;
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
  BEGIN TRANSACTION trans2
  INSERT INTO accounts (ORD_VALUE) VALUES (60)
  // Rollback the trans1 transaction and any transactions
  // nested in trans1
  ROLLBACK TRANSACTION trans1
END TRANSACTION

https://www.lianja.com/doc/index.php/SQL_BEGIN_TRANSACTION


Identifies a stage within a transaction which can subsequently be used as ROLLBACK point.

BEGIN TRANSACTION parent_and_child
  INSERT INTO customer;
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
    CITY, STATE, ZIP,LIMIT, START_DATE);
    VALUES;
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street', 'Beverly', 'MA', '01915', 2000, date())
  SAVEPOINT parent_added
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
  ROLLBACK TRANSACTION parent_added
END TRANSACTION

https://www.lianja.com/doc/index.php/SAVEPOINT


Set the current transaction state

set transaction isolation level read uncommitted

https://www.lianja.com/doc/index.php/SET_TRANSACTION


Current Transaction Isolation Level setting

set transaction isolation level read uncommitted
? txlevel()
UNCOMMITTED

https://www.lianja.com/doc/index.php/TXLEVEL()


Current Transaction Isolation Level setting

set transaction isolation level read uncommitted;
cTrans = txnisolation()

https://www.lianja.com/doc/index.php/TXNISOLATION()


Enable or disable automatic rollback

use setcomm
set rollback on
 
begin transaction
    delete first 15
    replace all t1 with (t2*t3)/100
    list
end transaction
 
if completed()    && determine if the transaction was successful
    dialog box "Transaction completed"
else
    dialog box "Errors occurred during transaction"
endif

https://www.lianja.com/doc/index.php/SET_ROLLBACK


Verify if journaling is in operation

procedure recovery
  rollback 
  if rollback()
      dialog box "Rollback was ok."
  else
      dialog box "Rollback not completed."
  endif
return
 
use setcomm
on error do recovery
if ismarked()
    dialog box "Other transaction in progress."
    return
endif
begin transaction
    delete first 15
    insert
    replace all t1 with (t2*t3)/100
    list
end transaction
if completed()
    dialog box "Transaction completed.  No errors."
else
    dialog box "Errors occurred during transaction"
endif

https://www.lianja.com/doc/index.php/ISMARKED()


Determine whether an error occurred during a multi-statement transaction

procedure recovery
  rollback 
  if rollback()
      dialog box "Rollback was ok."
  else
      dialog box "Rollback not completed."
  endif
return
 
use setcomm
on error do recovery
if ismarked()
    dialog box "Other transaction in progress."
    return
endif
begin transaction
    delete first 15
    insert
    replace all t1 with (t2*t3)/100
    list
end transaction
if completed()
    dialog box "Transaction completed.  No errors."
else
    dialog box "Errors occurred during transaction"
endif

https://www.lianja.com/doc/index.php/COMPLETED()


Current Transaction Level number

// Nested Transactions
? txnlevel() && 0
BEGIN TRANSACTION trans1
  ? txnlevel() && 1
  INSERT INTO customer;
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
    CITY, STATE, ZIP,LIMIT, START_DATE);
    VALUES;
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
  BEGIN TRANSACTION trans2
    ? txnlevel() && 2
    INSERT INTO accounts (ORD_VALUE) VALUES (60)
    // Commit the trans1 transaction and any transactions
    // nested in trans1
    COMMIT TRANSACTION trans1
    ? txnlevel() && 0
END TRANSACTION
? txnlevel() && 0

https://www.lianja.com/doc/index.php/TXNLEVEL()


Ends the current transaction, saving changes

BEGIN TRANSACTION trans1
  INSERT INTO customer;
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
    CITY, STATE, ZIP,LIMIT, START_DATE);
    VALUES;
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
  // Commit the trans1 transaction 
  COMMIT TRANSACTION trans1
END TRANSACTION
// End of program

https://www.lianja.com/doc/index.php/COMMIT


Commit changes made during a transaction and close the transaction

BEGIN TRANSACTION trans1
  INSERT INTO customer;
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
    CITY, STATE, ZIP,LIMIT, START_DATE);
    VALUES;
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
  BEGIN TRANSACTION trans2
  INSERT INTO accounts (ORD_VALUE) VALUES (60)
  // Rollback the trans1 transaction and any transactions
  // nested in trans1
  ROLLBACK TRANSACTION trans1
END TRANSACTION

https://www.lianja.com/doc/index.php/SQL_END_TRANSACTION


Ends the current transaction, undoing all changes

BEGIN TRANSACTION trans1
  INSERT INTO customer;
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
    CITY, STATE, ZIP,LIMIT, START_DATE);
    VALUES;
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
  // Rollback the trans1 transaction 
  ROLLBACK TRANSACTION trans1
END TRANSACTION

https://www.lianja.com/doc/index.php/SQL_ROLLBACK


Identifies a stage within a transaction which can subsequently be used as ROLLBACK point

BEGIN TRANSACTION parent_and_child
  INSERT INTO customer;
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
    CITY, STATE, ZIP,LIMIT, START_DATE);
    VALUES;
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street', 'Beverly', 'MA', '01915', 2000, date())
  SAVE TRANSACTION parent_added
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
  ROLLBACK TRANSACTION parent_added
END TRANSACTION
// End of program

https://www.lianja.com/doc/index.php/SAVE_TRANSACTION


begin transaction trans1
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    update order_details set discount = 0.05;
      where orderid = 10248
    begin transaction trans2
        update order_details set discount = 0.10;
          where orderid = 10248
    rollback transaction trans1
end transaction
 
begin transaction trans3
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    update order_details set discount = 0.05;
      where orderid = 10248
    commit transaction trans3
end transaction
 
begin transaction trans4
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    save transaction parent_updated
    update order_details set discount = 0.10;
      where orderid = 10248
    rollback transaction parent_updated
end transaction
 
begin transaction trans5
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    savepoint parent_updated
    update order_details set discount = 0.15;
      where orderid = 10248
    rollback transaction parent_updated
end transaction

https://www.lianja.com/doc/index.php/Using_Recital_SQL


 

Table – Update [examples]

update products;
  set unitsinstock = unitsinstock + unitsonorder, unitsonorder = 0;
  where supplierid = 1
begin transaction trans1
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    update order_details set discount = 0.05;
      where orderid = 10248
    begin transaction trans2
        update order_details set discount = 0.10;
          where orderid = 10248
    rollback transaction trans1
end transaction
 
begin transaction trans3
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    update order_details set discount = 0.05;
      where orderid = 10248
    commit transaction trans3
end transaction
 
begin transaction trans4
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    save transaction parent_updated
    update order_details set discount = 0.10;
      where orderid = 10248
    rollback transaction parent_updated
end transaction
 
begin transaction trans5
    update orders;
      set requireddate = date()+30;
      where orderid = 10248
    savepoint parent_updated
    update order_details set discount = 0.15;
      where orderid = 10248
    rollback transaction parent_updated
end transaction

https://www.lianja.com/doc/index.php/Using_Lianja_SQL


use products
replace unitsinstock with unitsinstock + unitsonorder,;
  unitsonorder = 0;
  for supplierid = 1
use products order productid
seek 73
if found()
   replace blank
endif
use products order productid
seek 73
if found()
   // Copy contents to a new record
   scatter to temp
   append blank
   gather from temp
endif
// or
seek 73
if found()
   // Copy specified contents to a new record
   scatter memvar fields productid, quantitype
   append blank
   gather memvar
endif

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Lianja


Update fields with values stored in correspondingly named memory variables

use customers
store automem
//...
replace automem

https://www.lianja.com/doc/index.php/REPLACE_AUTOMEM


Replace fields with elements from an array or series of memory variables

use demo
goto 45
scatter fields last_name, first_name to table_1
append blank
gather from table_1 fields last_name, first_name

https://www.lianja.com/doc/index.php/GATHER


Modify fields in a table

use patrons index events, names
replace all date with ctod("26/04/2000");
  for event = "PHANTOM"
 
// Multiple Replace Statements
replace firstname with m_firstname,;
  surname with m_surname,;
  state with m_state

https://www.lianja.com/doc/index.php/REPLACE


Update the contents of the active table with data from another table

select b
use newpatrons alias new
select a
use patrons index names, events, dates
update on name from new;
  replace balance with balance + new->balance

https://www.lianja.com/doc/index.php/UPDATE


Add a text file to a memo field

append memo minutes from meeting

https://www.lianja.com/doc/index.php/APPEND_MEMO


Fill fields with blanks

blank all for deleted()

https://www.lianja.com/doc/index.php/BLANK


Description of the columns in a table that are automatically updated when any row is updated

SELECT * FROM sysversioncolumns

https://www.lianja.com/doc/index.php/SYSVERSIONCOLUMNS


Update specified columns

// Update all accounts that are now overdue by adding a 15% commission charge
UPDATE accounts;
  SET ord_value=ord_value*1.15, due_date = date()+30;
  WHERE paid_date < date()
 
// Example using a cursor
OPEN DATABASE southwind
 
// Declare the cursor to select records from the orders table
DECLARE cursor1;
  CURSOR FOR;
  SELECT orderid, customerid, orderdate;
  FROM orders;
  WHERE requireddate < date()
 
// Open the cursor
OPEN cursor1
 
// Fetch records one at a time from the cursor and update them
FETCH cursor1;
  INTO m_order, m_customer, m_orderdate
  do while sqlcode = 0
    if not empty(m_order) and empty(shippeddate)
        UPDATE orders;
          SET shippeddate = date();
          WHERE CURRENT OF cursor1
    endif
    FETCH cursor1;
      INTO m_order, m_customer, m_orderdate
enddo
 
// Close the cursor and free up any resources used for the cursor
CLOSE cursor1
DROP CURSOR cursor1
 
CLOSE DATABASES
 
// Example with an encrypted table
UPDATE encacc<key_1,key_2,key_3>;
  SET ord_value=ord_value*1.15, due_date = date()+30;
  WHERE paid_date < date()

https://www.lianja.com/doc/index.php/SQL_UPDATE


Replace fields in the current record buffer without re-reading the current record

use accounts
replace(time, time())
replace(use, getenv("username"))
replace(date, date())

https://www.lianja.com/doc/index.php/REPLACE()


use products
replace unitsinstock with unitsinstock + unitsonorder,;
  unitsonorder = 0;
  for supplierid = 1
use products order productid
seek 73
if found()
   replace blank
endif
use products order productid
seek 73
if found()
   // Copy contents to a new record
   scatter to temp
   append blank
   gather from temp
endif
// or
seek 73
if found()
   // Copy specified contents to a new record
   scatter memvar fields productid, quantitype
   append blank
   gather memvar
endif

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Recital


update products;
  set unitsinstock = unitsinstock + unitsonorder, unitsonorder = 0;
  where supplierid = 1

https://www.lianja.com/doc/index.php/Using_Recital_SQL


 

Table – Select [examples]

// Return all columns
select * from products
// Return specified columns
select 'Product Details: ',  productid, upper(productname) as Name,;
  unitprice*unitsinstock as stockholding;
  from products
// Return aggregate function results for the whole table
select avg(unitprice) Average, min(unitprice) Minimum,;
  max(unitprice) Maximum, count(unitprice) Count,;
  sum(unitprice) Sum;
  from products
// Return one row for each group of rows with an identical contactname column
select distinct contactname from customers
// Return one row for each group of identical rows
select distinctrow contactname from customers
// Return the first 10 rows
select top 10 * from customers
// Return the first 50% of rows
select top 50 percent * from customers
// Select a maximum of 10 rows, starting from row 6
select * from customers limit 5,10
// or
select * from customers limit 10 offset 5
// Select all rows from shippers and employees without relating the tables
select * from shippers ship, employees as emp
// Relate orders, order_details and shippers tables with multiple joins
select orders.orderid, orders.shipvia,;
  shippers.companyname, order_details.productid;
  from orders left outer join order_details;
  on orders.orderid = order_details.orderid,;
     orders inner join shippers;
     on orders.shipvia = shippers.shipperid
// Equivalent to 'select * from shippers, employees'
select * from shippers full join employees
// Return rows sorted in descending order by orderid then productid
select orderid, productid, quantity;
  from order_details;
  order by orderid, 2 desc
// Return the orderid and total for each order
select orderid as Order, sum(unitprice * quantity * (1-discount)) as Total;
  from order_details;
  group by orderid
// Having condition
select sum(unitprice * quantity) as subtotal,;
  avg(unitprice * quantity) as averagetotal;
  from order_details;
  where discount = 0;
  group BY orderid;
  having quantity > 10
 
// Where condition with nested select
select shipname from orders, customers;
  where orders.customerid = customers.customerid;
  and employeeid = (select max(employeeid);
  from orders where orderdate = {07/19/1996});
  order by shipname
select productid from products into array array1
select * from shippers into html shippers
select employeeid, lastname, firstname from employees into table emp database newsouth
 
select employeeid, lastname, firstname from employees save as xml emp
 
select * from shippers to file shiptxt

https://www.lianja.com/doc/index.php/Using_Lianja_SQL


Retrieves data from one or more tables or views
// Specifying database name
SELECT * FROM southwind!orders
// or
SELECT * FROM southwind.orders
 
// Select all rows, including duplicates, from an encrypted table
SELECT ALL * FROM enctab<key_1,key2,key_3>
 
// Select "last_name" column from rows with a unique "last_name"
SELECT DISTINCT last_name FROM customer
 
// Select "last_name" column from unique rows
SELECT DISTINCTROW last_name FROM customer
 
// Select first 10 rows
SELECT TOP 10 * FROM accounts
 
// Select first 50% of the rows
SELECT TOP 50 PERCENT * FROM accounts
 
// Crystal Reports / ODBC style JOINS: LEFT OUTER, RIGHT OUTER, FULL OUTER
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM {OJ "customer" customer;
  LEFT OUTER JOIN "accounts" accounts;
  ON customer.account_no = accounts.account_no}
 
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM {OJ "customer" customer;
  RIGHT OUTER JOIN "accounts" accounts;
  ON customer.account_no = accounts.account_no}
 
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM {OJ "customer" customer;
  FULL OUTER JOIN "accounts" accounts;
  ON customer.account_no = accounts.account_no}
 
// JOINS: INNER, LEFT OUTER, RIGHT OUTER, CROSS, FULL
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM customer;
  INNER JOIN accounts;
  ON customer.account_no = accounts.account_no
 
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM customer;
  LEFT OUTER JOIN accounts;
  ON customer.account_no = accounts.account_no
 
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM customer;
  RIGHT OUTER JOIN accounts;
  ON customer.account_no = accounts.account_no
 
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM customer;
  CROSS JOIN accounts
 
SELECT customer.account_no, customer.last_name, accounts.ord_value;
  FROM customer;
  FULL OUTER JOIN accounts
 
// JOINs with nested SELECTs
SELECT contactname FROM customers;
  WHERE customerid IN;
 (SELECT customerid FROM orders WHERE orderdate = {07/19/1996}) 
 
SELECT shipname FROM orders, customers;
  WHERE orders.customerid = customers.customerid AND;
  employeeid = (SELECT max(employeeid);
  FROM orders;
  WHERE orderdate = {07/19/1996});
  order by shipname
 
SELECT contactname FROM customers;
  WHERE ctod("07/19/1996") > ANY;
 (SELECT orderdate FROM orders WHERE customers.customerid = orders.customerid)
 
SELECT orders.customerid;
  FROM orders;
  WHERE 30 > ALL (SELECT sum(quantity) FROM order_details;
  WHERE orders.orderid = order_details.orderid)
 
SELECT orderid,customerid;
  FROM orders as o1;
  WHERE 2 < (SELECT quantity FROM order_details as i1;
  WHERE o1.orderid = i1.orderid AND i1.productid = 71) 
 
SELECT lastname FROM employees;
  WHERE exists;
  (SELECT * FROM orders;
  WHERE employees.employeeid = orders.employeeid;
  AND orderdate = CTOD("11/11/1996"))
 
SELECT lastname FROM employees;
  WHERE not exists;
  (SELECT * FROM orders;
  WHERE employees.employeeid = orders.employeeid;
  AND orderdate = CTOD("11/11/1996"))
 
SELECT companyname, (select MAX(orderid);
  FROM orders as o1;
  WHERE o1.customerid = c1.customerid);
  FROM customers as c1
 
// Multiple JOINs
SELECT customer.account_no, customer.state,;
  state.descript, accounts.ord_value;
  FROM customer RIGHT OUTER JOIN accounts;
  ON customer.account_no = accounts.account_no,;
  customer INNER JOIN state;
  ON customer.state = state.state;
  ORDER BY account_no
 
// Select account number and order value details for Massachusetts customers 
SELECT account_no, ord_value;
  FROM accounts;
  WHERE account_no in (SELECT account_no FROM customer WHERE state = 'MA');
  ORDER BY account_no
 
// Select all overdue accounts with 15% commission in sorted "last_name" order.
SELECT last_name, zip, balance, balance*1.15;
  FROM customer;
  WHERE balance > 0;
  ORDER BY last_name
 
// Select total and average balance for all overdue accounts, grouped by "limit"
SELECT SUM(balance), AVG(balance);
  FROM customer;
  WHERE balance > 0;
  GROUP BY limit
 
// Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  GROUP BY limit
 
// Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
// For Massachusetts customers only
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  GROUP BY limit;
  HAVING state = "MA"
 
// Save into an array
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO ARRAY temp
 
// Save into an array of objects
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO ARRAYOFOBJECTS temp
// convert first selected row to JSON
cjson = json_encode(temp[1])
 
// Save into an object
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO object temp
 
// Save into a JSON file
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO JSON temp
 
// Save into an HTML file
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO HTML temp
 
// Save into an XML file
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO XML temp
 
// Create a cursor
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO CURSOR temp
 
// Save as a table
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO TABLE temp DATABASE mydbc
 
//or
 
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  SAVE AS temp
 
// Save in Microsoft® ActiveX® Data Objects XML format
// Any XML files created in the ADO format can be loaded
// with the Open method of the ADO Recordset object. 
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  SAVE AS XML temp.xml FORMAT ADO
// In Visual Basic the file can then be loaded:
// Set adoPrimaryRS = New Recordset
// adoPrimaryRS.Open "temp.xml"
 
// Save in Microsoft® Excel XML format
SELECT * FROM customers SAVE AS xml mycustomers.xml FORMAT EXCEL
Lianja.showDocument("mycustomers.xml")
 
// Save in text file format
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  TO FILE temp.txt
 
// Select all customer accounts that have an outstanding balance or are based in Massachusetts
SELECT account_no;
  FROM customer;
  WHERE state = 'MA';
  UNION SELECT account_no;
  FROM accounts;
  WHERE balance > 0;
  ORDER BY account_no
 
// Select a maximum of 10 rows, starting from row 6
SELECT * FROM customer;
  LIMIT 5,10
//or
SELECT * FROM customer;
  LIMIT 10 OFFSET 5
 
// Ignore the smartquery cache
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO CURSOR temp;
  nosmartquery
 
// Force smartquery
SELECT SUM(balance) AS Total, AVG(balance) AS Average;
  FROM customer;
  WHERE balance > 0;
  INTO CURSOR temp;
  smartquery

https://www.lianja.com/doc/index.php/SQL_SELECT


Number of rows in a SELECT statement query

// Get a count of all rows in the accounts table.
SELECT COUNT(*) Total FROM accounts
 
// Get a count of jobs
SELECT COUNT(jobs) Jobs FROM employee
 
// Get a count of distinct rows for jobs
SELECT COUNT(DISTINCT jobs) Jobs FROM employee

https://www.lianja.com/doc/index.php/COUNT()


Returns a maximum value in a SELECT statement

SELECT MAX(sal) Maximum FROM accounts

https://www.lianja.com/doc/index.php/SQL_MAX()


Returns a minimum value in a SELECT statement

SELECT MIN(sal) Minimum FROM accounts

https://www.lianja.com/doc/index.php/SQL_MIN()


Unique row identifier in SELECT * statements

set sql on
set sqlrowid on
select * from state.rdb where state = "M";

https://www.lianja.com/doc/index.php/SET_SQLROWID


Return the single row result of an SQL statement

open database southwind
cTotal = sqleval("select max(available), min(available) from example")
 
// Access multiple row results
sqleval('select state from example group by state')
? astring(_sqlvalues)
AL,AR,CA,CO,CT,FL,GA,IA,IL,KY,LA,MA,MD,ME,NH,NJ,NY,OH,PA,VA,WI

https://www.lianja.com/doc/index.php/SQLEVAL()


Return the single row result of an SQL statement

open database southwind
cTotal = sqlvalues("select max(available), min(available) from example")
 
// Access multiple row results
sqlvalues('select state from example group by state')
? astring(_sqlvalues)
AL,AR,CA,CO,CT,FL,GA,IA,IL,KY,LA,MA,MD,ME,NH,NJ,NY,OH,PA,VA,WI

https://www.lianja.com/doc/index.php/SQLVALUES()


Return the singleton result from any Lianja expression

select set("EXCLUSIVE") as Excl, time() as Time from sysresultset

https://www.lianja.com/doc/index.php/SYSRESULTSET


 

// List order table order number and associated order_details table records
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
set skip on
set skip to order_details
list orders.orderid,;
  order_details.unitprice, order_details.quantity,;
  order_details.discount;
  for orders.employeeid = 5;
  and orders.shipvia = 3
// List order value totals for selected orders
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
scan for orders.employeeid = 5 and orders.shipvia = 3
    select order_details
    echo sumvalues(unitprice * quantity * (1-discount))
    echo "\n"
    select orders
endscan

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Recital


 

// Return all columns
select * from products
// Return specified columns
select 'Product Details: ',  productid, upper(productname) as Name,;
  unitprice*unitsinstock as stockholding;
  from products
// Return aggregate function results for the whole table
select avg(unitprice) Average, min(unitprice) Minimum,;
  max(unitprice) Maximum, count(unitprice) Count,;
  sum(unitprice) Sum;
  from products
// Return one row for each group of rows with an identical contactname column
select distinct contactname from customers
// Return one row for each group of identical rows
select distinctrow contactname from customers
// Return the first 10 rows
select top 10 * from customers
// Return the first 50% of rows
select top 50 percent * from customers
// Select a maximum of 10 rows, starting from row 6
select * from customers limit 5,10
// or
select * from customers limit 10 offset 5
// Return rows sorted in descending order by orderid then productid
select orderid, productid, quantity;
  from order_details;
  order by orderid, 2 desc
// Return the orderid and total for each order
select orderid as Order, sum(unitprice * quantity * (1-discount)) as Total;
  from order_details;
  group by orderid
select productid from products into array array1
select * from shippers into html shippers
select employeeid, lastname, firstname from employees into table emp database newsouth
 
select employeeid, lastname, firstname from employees save as xml emp
 
select * from shippers to file shiptxt

https://www.lianja.com/doc/index.php/Using_Recital_SQL


 

Tables – relating [examples]

open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
set skip on
set skip to order_details
list orders.orderid, order_details.orderid
// List order table order number and associated order_details table records
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
set skip on
set skip to order_details
list orders.orderid,;
  order_details.unitprice, order_details.quantity,;
  order_details.discount;
  for orders.employeeid = 5;
  and orders.shipvia = 3
// List order value totals for selected orders
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
scan for orders.employeeid = 5 and orders.shipvia = 3
    select order_details
    echo sumvalues(unitprice * quantity * (1-discount))
    echo "\n"
    select orders
endscan
use order_details
average unitprice, quantity to m_units, m_quant
count to m_over for quantity > 30
seek 10248
sum (unitprice * quantity) * (1-discount) while orderid = 10248 to m_sum
total on orderid to totals

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Lianja


Specify relationships between open tables

select b
use addresses index add_names
select c
use accounts index acc_names
select a
use patrons index events, dates, names
set relation to name into addresses,;
  name into accounts
set filter to accounts->balance>1000
list date, event, addresses->street, addresses->city, accounts->balance

https://www.lianja.com/doc/index.php/SET_RELATION


Automatically establishes one-to-many relationships across related tables

select c
use invoices index cust_code
select b
use orders
set relation to cust_code into invoices
select a
use customers
set skip on
set relation to cust_code into invoices
set skip to orders, invoices

https://www.lianja.com/doc/index.php/SET_SKIP


Column constraint to create a relationship to an index key of another table

CREATE TABLE supplier;
  (SuppId i PRIMARY KEY,;
  SuppName c(40) UNIQUE)
CREATE TABLE purchase_order;
  (POid i PRIMARY KEY,;
  SuppId i REFERENCES supplier TAG SuppId,;
  POtotal n(10,2))

https://www.lianja.com/doc/index.php/REFERENCES_Column_Constraint


Description of how one table imports the keys of another table

SELECT * FROM syscrossreference

https://www.lianja.com/doc/index.php/SYSCROSSREFERENCE


Description of the foreign key columns that reference the primary key columns

SELECT * FROM sysexportedkeys

https://www.lianja.com/doc/index.php/SYSEXPORTEDKEYS


Description of the primary key columns that are referenced by the foreign key

SELECT * FROM sysimportedkeys

https://www.lianja.com/doc/index.php/SYSIMPORTEDKEYS


Perform a cross-table lookup

use depts in 2 index dnum
use funcs in 1 index funcno
? lookup(depts->dname,funcs->dnum,depts)
Research and Development

https://www.lianja.com/doc/index.php/LOOKUP()


Perform a cross-table lookup

open database southwind
use orders in 0
? keylookup("employees","employeeid",orders.employeeid, lastname, "not found")

https://www.lianja.com/doc/index.php/KEYLOOKUP()


Perform a cross-table lookup

open database southwind
? sqllookup("employees","employeeid","1","trim(firstname)+' '+ lastname","'Not found'")
Nancy Davolio
? sqllookup("employees","employeeid","77","firstname+lastname","'Not found'")
Not found
? sqllookup("employees","lastname","'Davolio'","employeeid","0")
         1
? sqllookup("employees","lastname","'MacDavolio'","employeeid","0")
         0
? sqllookup("employees","where lastname = 'Davolio' and firstname = 'Nancy'", "","employeeid","0")
         1
? sqllookup("vt_orders","where orderid = 10248", "","customerid","'Not found'")
WILMK
? sqllookup("vt_orders","select * from orders where orderid = 10248", "","customerid","'Not found'")
WILMK

https://www.lianja.com/doc/index.php/SQLLOOKUP()


Perform a cross-table lookup for a specified key expression

use func in 1 index funcno
use depts in 2 index deptnum
seek "300"
? iif(rlookup(depts->funcno,func,1),func->dept_name,"No Department.")
Research and Development
 
use customer.rdb
index on account_no tag account_no
index on upper(last_name) tag uplast
index on zip tag zip
? rlookup("STEREK",customer,"uplast")
.T.

https://www.lianja.com/doc/index.php/RLOOKUP()


Return table-linking expression

use shows in 2 index pcode
use patrons in 1
set relation to patron_code into shows
? dbrelation(1)
patron_code
? target(1)
         2

https://www.lianja.com/doc/index.php/DBRELATION()


Return table-linking expression

use shows in 2 index pcode
use patrons in 1
set relation to patron_code into shows
? relation(1)
patron_code
? target(1)
         2

https://www.lianja.com/doc/index.php/RELATION()


Return target workarea of a relation

use shows in 2 index people
use patrons in 1
set relation to patron_code into shows
? target(1)
         2

https://www.lianja.com/doc/index.php/TARGET()


Return the target workarea of a relation

use shows in 2 index people
use patrons in 1
set relation to patron_code into shows
? dbrelation(1)
patron_code
? dbrselect(1)
         2

https://www.lianja.com/doc/index.php/DBRSELECT()


Perform a cross-table lookup for a specified key expression

use customer.rdb
index on account_no tag account_no
index on upper(last_name) tag uplast
index on zip tag zip
? references("STEREK",customer,"uplast")
.T.

https://www.lianja.com/doc/index.php/REFERENCES()


 

open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
set skip on
set skip to order_details
list orders.orderid, order_details.orderid

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Recital


 

// Select all rows from shippers and employees without relating the tables
select * from shippers ship, employees as emp
// Relate orders, order_details and shippers tables with multiple joins
select orders.orderid, orders.shipvia,;
  shippers.companyname, order_details.productid;
  from orders left outer join order_details;
  on orders.orderid = order_details.orderid,;
     orders inner join shippers;
     on orders.shipvia = shippers.shipperid
// Equivalent to 'select * from shippers, employees'
select * from shippers full join employees

https://www.lianja.com/doc/index.php/Using_Recital_SQL


 

Table – navigating [examples]

Navigating a Lianja Table

Lianja tables can be navigated via their physical record order or via the master index.

use example
goto 10
// First physical record
go top
// Last physical record
go bottom
set order tag account_no
// First record in index
go top
// Last record in index
go bottom
use example
goto 10
// Skip to record 12
skip 2
// Skip back to record 10
skip -2
set order tag account_no
// First record in index
go top
// Tenth record in index
skip 10
use example order account_no
// Character key
seek "00010"
if found()
    // commands if key found in index
endif
use orders order orderid
// Numeric key
seek 11058

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Recital


Position to a specific record in a table

goto top
browse
goto 10

https://www.lianja.com/doc/index.php/GOTO


Search the active table for records that satisfy a certain condition

use patrons
locate for event = "DREAM"
do while found()
    display event, name, seats, seats * price
    continue
enddo

https://www.lianja.com/doc/index.php/LOCATE


Resume locate search

use patrons
locate for event = "OPERA"
do while found()
    display name, event
    continue
enddo

https://www.lianja.com/doc/index.php/CONTINUE


Move the record pointer forwards and backwards in the active table

use patrons index events, dates, names
m_event = event
do while .not. eof()
    display off date, event, name
    if m_event <> event
        ?
        m_event = event
    endif
    skip
enddo
// Another example
skip 9 in orders

https://www.lianja.com/doc/index.php/SKIP


 

Table – Insert [examples]

use example
// Add 10 blank records
append blank 10
use example
// Append records from another table (temp.dbf)
append from temp for state = "MA"
// Append records from a text file with '|' delimiter (piped.txt)
append from piped type delimited with |
// Append from a Microsoft Excel CSV file (customers.csv)
append from customers csv
// Open test table and add 2000 records
use test
generate 2000

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Lianja


Add new records and fill them with random information
use newtable
generate 10

https://www.lianja.com/doc/index.php/GENERATE


Inserts one or more rows into a table
// Add 2 new rows and update the column values.
INSERT INTO accounts!balances;
  (acc_prefix, acc_no, balance);
  VALUES ('hmt', 'a12345', m_value*1.75),;
  VALUES ('hmt', 'a12346', m_value*1.75)
 
// Encrypted table example.
INSERT INTO encbal<key_1,key_2,key_3>;
  (acc_prefix, acc_no, balance);
  VALUES ('hmt', 'a12345', m_value*1.75)

https://www.lianja.com/doc/index.php/INSERT


Appends a blank record into the active table, then updates the fields with memory variable values

use cust
store automem
m.account_no = strzero(seqno(),5)
append automem

https://www.lianja.com/doc/index.php/APPEND_AUTOMEM


Append one or more records into the active table

use patrons index names
append blank 10

https://www.lianja.com/doc/index.php/APPEND_BLANK


Enable a timestamp to be inserted into the specified field as a record is being appended

open database southwind
alter table example add column timeref char(8)
use example
set timestamp to timeref
// timeref field will be populated with current system time
append blank
use
// timeref field will be populated with current system time
insert into example (account_no, last_name) values ("99999", "Smith")

https://www.lianja.com/doc/index.php/SET_TIMESTAMP


Column constraint to auto increment the value of a column

CREATE TABLE newcust;
  (acc_num INT AUTO_INCREMENT, acc_name char(20))
INSERT INTO newcust;
  (acc_name) VALUES ("Smith")
INSERT INTO newcust;
  (acc_name) VALUES ("Jones")
SELECT * FROM newcust

https://www.lianja.com/doc/index.php/AUTO_INCREMENT_Column_Constraint


Column constraint to auto increment the value of a column

CREATE TABLE newcust;
  (acc_num INT AUTOINC NEXTVALUE 10 STEP 5, acc_name char(20))
INSERT INTO newcust;
  (acc_name) VALUES ("Smith")
INSERT INTO newcust;
  (acc_name) VALUES ("Jones")
SELECT * FROM newcust

https://www.lianja.com/doc/index.php/AUTOINC_Column_Constraint


Append records to the active table from another table or external file
use patrons
append from system type sdf
append from textfile type delimited
append from transactions for code <> "D"
 
// Another Example
use payroll
append from (iif(dow(date())>5, "weekend.dbf","weekday.dbf") for amount > 100

https://www.lianja.com/doc/index.php/APPEND_FROM


Inserts one or more rows into a table

// Add a new row and update the column values.
INSERT INTO accounts!balances;
  (acc_prefix, acc_no, balance);
  VALUES ('hmt', 'a12345', m_value*1.75)
 
// Encrypted table example.
INSERT INTO encbal<key_1,key_2,key_3>;
  (acc_prefix, acc_no, balance);
  VALUES ('hmt', 'a12345', m_value*1.75)
 
// Insert multiple records in a single statement
INSERT INTO southwind!example (account_no, last_name, first_name) VALUES ;    
 ("20000", "Smith", "Sam"), ;    
 ("20001", "Jones", "Jack"), ;    
 ("20002", "Baker", "Beryl")

https://www.lianja.com/doc/index.php/SQL_INSERT


To determine NULL value support

set null on
CREATE TABLE nullon (firstname c(20), lastname c(20))
INSERT INTO nullon (lastname) VALUES ("Smith")
? [SET NULL ON]
? [ISNULL() ], isnull(firstname)
? [EMPTY() ], empty(firstname)
wait
SET NULL ON
ISNULL()  .T.
EMPTY()  .F.
Press any key to continue...
 
set null off
CREATE TABLE nulloff (firstname c(20), lastname c(20))
INSERT INTO nulloff (lastname) VALUES ("Smith")
? [SET NULL OFF]
? [ISNULL() ], isnull(firstname)
? [EMPTY() ], empty(firstname)
wait
SET NULL OFF
ISNULL()  .F.
EMPTY()  .T.
Press any key to continue...
 
 
set null off
CREATE TABLE nulloff2 (firstname c(20) NULL, lastname c(20))
INSERT INTO nulloff2 (firstname,lastname) VALUES (NULL,"Smith")
? [SET NULL OFF, NULL Column Constraint]
? [ISNULL() ], isnull(firstname)
? [EMPTY() ], empty(firstname)
wait
SET NULL OFF, NULL Column Constraint
ISNULL()  .T.
EMPTY()  .F.
Press any key to continue...

https://www.lianja.com/doc/index.php/SET_NULL


use example
// Add 10 blank records
append blank 10
use example
// Append records from another table (temp.dbf)
append from temp for state = "MA"
// Append records from a text file with '|' delimiter (piped.txt)
append from piped type delimited with |
// Append from a Microsoft Excel CSV file (customers.csv)
append from customers csv
// Open test table and add 2000 records
use test
generate 2000

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Recital


insert into example;
  (account_no, last_name);
  values("01001","Brown")
insert into example;
  (account_no, last_name);
  select * from temp
account_no = "01002"
title = "Ms"
last_name = "Smith"
 
insert into example;
  from memvar
 
arr1 = {"01003","Mr","Jones"}
 
insert into example;
  from array arr1
 
obj1 = object("account_no" => "01004","title" => "Mrs","last_name" => "Green")
 
insert into example;
  from name obj1
 
insert into example;
  from xml newcust.xml

https://www.lianja.com/doc/index.php/Using_Lianja_SQL 

https://www.lianja.com/doc/index.php/Using_Recital_SQL