Category: Table

View [examples]

Published on by homunizamLeave a comment
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

Transactions [examples]

Published on by homunizamLeave a comment
Begin transaction Before Image Journaling
BEGIN 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/BEGIN_TRANSACTION

End transaction Before Image Journaling
END 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/END_TRANSACTION

Restore tables to their condition at the beginning 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

Verify success of a transaction rollback
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
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.
SAVEPOINT
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
set transaction isolation level read uncommitted

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

Current Transaction Isolation Level setting
TXLEVEL
set transaction isolation level read uncommitted
? txlevel()
UNCOMMITTED

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

Current Transaction Isolation Level setting
TXNISOLATION
set transaction isolation level read uncommitted;
cTrans = txnisolation()

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

Enable or disable automatic rollback
SET 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
ISMARKED
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
COMPLETED
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
TXNLEVEL
// 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
COMMIT
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
END 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
ROLLBACK 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)
  // 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
SAVE TRANSACTION

 

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

Disable journaling in a specified workarea
RESET IN
// Clear BIJ in workarea suppliers
reset in suppliers

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

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]

Published on by homunizamLeave a comment
Update specified columns
UPDATE
// 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

 

Modify fields in a table
REPLACE
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 fields with values stored in correspondingly named memory variables
REPLACE AUTOMEM
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
GATHER
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

Update the contents of the active table with data from another table
UPDATE
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
append memo minutes from meeting

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

Fill fields with blanks
BLANK
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
SYSVERSIONCOLUMNS
SELECT * FROM sysversioncolumns

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

Replace fields in the current record buffer without re-reading the current record
REPLACE
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

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

Table – Select [examples]

Published on by homunizamLeave a comment
Retrieves data from one or more tables or views
SELECT
// 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

 

// 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

Number of rows in a SELECT statement query
COUNT
// 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
MAX
SELECT MAX(sal) Maximum FROM accounts

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

Returns a minimum value in a SELECT statement
MIN
SELECT MIN(sal) Minimum FROM accounts

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

Unique row identifier in SELECT * statements
SET SQLROWID
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
SQLEVAL
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
SQLVALUES
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

Table – relating [examples]

Published on by homunizamLeave a comment
Specify relationships between open tables
SET RELATION
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
SET SKIP
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
REFERENCES
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
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
KEYLOOKUP
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
SQLLOOKUP
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
RLOOKUP
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
DBRELATION
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
RELATION
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
TARGET
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
DBRSELECT
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
REFERENCES
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
// 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

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]

Published on by homunizamLeave a comment

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
goto top
browse
goto 10

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

Search the active table for records that satisfy a certain condition
LOCATE
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
CONTINUE
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
SKIP
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]

Published on by homunizamLeave a comment
Inserts one or more rows into a table
INSERT
// 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
APPEND AUTOMEM
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
APPEND BLANK
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
SET TIMESTAMP
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
AUTO_INCREMENT
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
AUTOINC
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
APPEND FROM
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
INSERT INTO
// 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
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

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

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

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

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

Table – information [examples]

Published on by homunizamLeave a comment
Display the contents of the active table and any related tables 
DISPLAY
open database southwind
use products order categoryid
display
display all
seek 2
display productid, productname while categoryid = 2

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

List the contents of the active table and any related tables
LIST
open database southwind
use products order categoryid
seek 2
list productid, productname while categoryid = 2
// rsp example
<%@ Language=VFP %><% if not isServer()     ? "" else     ? "" endif %>

<% text raw function editCustomerID(customerid) { Lianja.showDocument("page:page1?action=search&text="+customerid); }; endtext save datasession open database southwind use customers list html off ; fields customerid,companyname,contacttitle,contactname,address,region,city,country ; onclick "customerid","editCustomerID('{}')" restore datasession %>

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

 Display the structure of the active table 
LIST STRUCTURE
use patrons
list structure
 Display the structure of the active table 
DISPLAY STRUCTURE
use patrons
display structure
Fill an array with field descriptions
ADESC
declare headings[fcount()]
adesc(headings)

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

Count fields contained in a table
FCOUNT
use accounts
do while i <= fcount()
    ? field(i)
    ++ i
enddo

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

Field name
FIELD
use payroll
declare fname[fcount()]
for n=1 to fcount()
    fname[n] = field(n)
    ?fname[n]
next
?
 
use demo
go top
? field(1,1,1)
ACCOUNT_NO,C,5,00046

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

String containing comma-separated information about a specified field
FIELDINFO
open database southwind
use example
astore(a_info,fieldinfo(1,1),",")
display memory

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

Field name
FIELDNAME
use payroll
declare fname[fcount()]
for n=1 to fcount()
    fname[n] = fieldname(n)
    ?fname[n]
next
?
 
use demo
go top
? fieldname(1,1,1)
ACCOUNT_NO,C,5,00046

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

Comma separated list of field names
FLDLIST
open database southwind
use customers
fieldList = fldlist()

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

Count fields contained in a table
FLDCOUNT
use accounts
do while i <= fldcount()
    ? field(i)
    ++ i
enddo

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

Table file name
DBF
use patrons
? dbf()
patrons.dbf
dbfname = dbf()
? dbfname
patrons.dbf
? len(dbfname)
        11
use
dbfname = dbf()
? len(dbfname)
         0

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

Field description
LABEL
close databases
clear
open database southwind
use example
for i = 1 to fldcount()
    ? "Field: " + field(i) + " - Description: " + label(i)
next
?

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

Numeric field count
NFCOUNT
use accounts
? nfcount()
         3

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

Date of last table update
LUPDATE
use patrons
? lupdate()
02/02/2000

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

Fill an array with table structure details
AFIELDS
open database southwind
use customers
? afields(myarr)
display memory

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

All the fields in the current row as a string
RTOS
use backup in 0
use accounts in 0
nrecs=reccount()
for i = 1 to nrecs
    if rtos(accounts) != rtos(backup)
       debug("record "+recno()+" doesn't match")
    endif
next

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

Number of records in table
RECCOUNT
use accounts
? reccount()
      2500
? fcount()
        18
declare aAccounts[reccount(),fcount()]
copy to array aAccounts for empty(paid_date)

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

Record size
RECSIZE
use accounts
if header() + reccount() * recsize() > 100000
dialog box "Database too big for backup disk."
endif

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

Table header size
HEADER
use mytable
size = header() + reccount() * recsize()

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

Number of records in table, excluding deleted records and those not in the active filter
ROWCOUNT
use accounts
? reccount()
      2500
delete first 10
? rowcount()
      2490

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

Number of records in table, excluding deleted records and, if the table is open, those not in the active filter
TABLEROWCOUNT
open database southwind
? tablerowcount("orders")
       823

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

Check whether a specific column exists
COLUMNEXISTS
if columnexists("southwind","customers","customerid",5,0)
    open database southwind
    use customers
else
    open database southwind
    alter table customers (...)
endif

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

Determine if a table is open
INUSE
? inuse("customers")
.T.
? inuse("products")
.T.

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

Determine if a table is open readonly
ISREADONLY
open database southwind
use customers in 1 
use orders in 2 noupdate
? isreadonly(1)              // .F.
? isreadonly(2)              // .T.
? isreadonly("customers")    // .F.
? isreadonly("orders")       // .T.
? isreadonly()               // .F.

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

Check whether table exists
TABLEEXISTS
if tableexists("southwind","customers")
    open database southwind
    use customers
else
    open database southwind
    create table customers (...)
endif

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

Current property settings for a table or cursor
CURSORGETPROP
open database southwind
use customers
? cursorgetprop("Buffering")
         3

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

Define current property settings for a table or cursor
CURSORSETPROP
close databases
clear
set exclusive off
open database southwind
use customers
// Set to known value before start
replace customerid with "ALFKI"
 
cursorsetprop("Buffering", 5, "customers")
messagebox("Buffering set to " +etos(cursorgetprop("Buffering")))
 
? "Original customerid value: " + customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate at start: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
 
// Alter in another session
// update southwind!customers set customerid = 'MULTI' where recno() = 1
? "Someone else just updated the record!"
? "New customerid value: " +  customerid 
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace: " + getfldstate("customerid")
tablerevert(.T.)
 
? "Reverted customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after revert: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
? "New customerid value: " +  customerid 
tableupdate(.T.)
? "Updated customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace and update: " + getfldstate("customerid")
?

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

String containing the specified field’s autosuggestionheaders column constraint
FIELDAUTOSUGGESTIONHEADERS
open database southwind
use customers
? fieldautosuggestions("customers","contacttitle")
select distinct contacttitle,contactname from customers
? fieldautosuggestionheaders("customers","contacttitle")
Title,Name
? fieldautosuggestions(1,"contacttitle")
select distinct contacttitle,contactname from customers
? fieldautosuggestionheaders(1,"contacttitle")
Title,Name

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

String containing the specified field’s autosuggestions column constraint
FIELDAUTOSUGGESTIONS
open database southwind
use customers
? fieldautosuggestions("customers","contacttitle")
select distinct contacttitle,contactname from customers
? fieldautosuggestionheaders("customers","contacttitle")
Title,Name
? fieldautosuggestions(1,"contacttitle")
select distinct contacttitle,contactname from customers
? fieldautosuggestionheaders(1,"contacttitle")
Title,Name

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

String containing the specified field’s choices column constraint
FIELDCHOICES
open database southwind
use order_details
? fieldchoices(1,"productid")
select productname from products order by productname
? fieldchoices("order_details","productid")
select productname from products order by productname

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

Check if a field has been modified
GETFLDSTATE
close databases
clear
set exclusive off
open database southwind
use customers
// Set to known value before start
replace customerid with "ALFKI"
 
cursorsetprop("Buffering", 5, "customers")
messagebox("Buffering set to " +etos(cursorgetprop("Buffering")))
 
? "Original customerid value: " + customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate at start: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
 
// Alter in another session
// update southwind!customers set customerid = 'MULTI' where recno() = 1
? "Someone else just updated the record!"
? "New customerid value: " +  customerid 
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace: " + getfldstate("customerid")
tablerevert(.T.)
 
? "Reverted customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after revert: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
? "New customerid value: " +  customerid 
tableupdate(.T.)
? "Updated customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace and update: " + getfldstate("customerid")
?

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

Information about a table, virtual table or view
TABLEINFO
open database mytables
// Standard Lianja table
? tableinfo("ltable")
Table
// View
? tableinfo("lview")
View|select * from customers
// Virtual Table
? tableinfo("lvirtual")
VirtualTable|lvirtual|awhr|currencycode||select * from sales.currency

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

Count records in the active table that satisfy a specified condition
COUNT
use patrons
count to nResult for event = "CHOPIN" and date = date()

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

Number of records in table
LASTREC
use accounts
? lastrec()
      2500
? fcount()
        18
declare aAccounts[reccount(),fcount()]
copy to array aAccounts for empty(paid_date)

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

Filetype of the active table or the table in a specified workarea
FILETYPE
use demo
? filetype()
Lianja

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

Table – filtering [examples]

Published on by homunizamLeave a comment
Seclude records in a table that do not satisfy a certain condition
SET FILTER
use patrons
set filter to event = "BALLET" and date < date()
browse

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

Return filter expression

FILTER
use patrons
set filter to event="BALLET"
? "["+filter()+"]"
[event="BALLET"]
? type("filter()")
C

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

Return the filter expression

DBFILTER
use patrons
set filter to event="BALLET"
? "[" + dbfilter() + "]"
[event="BALLET"]
? type("dbfilter()")
C

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

Test if table has an active filter expression

ISFILTERED
use patrons
set filter to event="BALLET"
? isfiltered()
.T.

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

Determine whether the specified expression is equal to one of the values

IN
SELECT name, address, balance, cost*1.15;
  FROM accounts;
  WHERE paid_date < date() AND ord_value IN (100, 200, 300);
  ORDER BY name, paid_date
 
SELECT name, address, balance, rep_id;
  FROM accounts;
  WHERE rep_id IN (SELECT emp_id from employees where location = 'MA');
  ORDER BY name

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

Determine whether the specified expression matches another

LIKE
SELECT name, address, balance, cost*1.15;
  FROM accounts;
  WHERE paid_date < date() AND name LIKE "%Inc";
  ORDER BY name, paid_date

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

Determine whether the specified expression is >= to another and <= to other

BETWEEN
SELECT name, address, balance, cost*1.15;
  FROM accounts;
  WHERE paid_date < date() AND ord_value NOT BETWEEN 0 AND 10000;
  ORDER BY name, paid_date

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

Sets soft seeking of data

SET SOFTSEEK
set softseek on
use demo
select state
seek "MB"
? found()
.F.
? eof()
.F.
? state
MD
set softseek off
seek "MB"
? found()
.F.
? eof()
.T.

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

Enable/disable row filters or set a row filter for the current table
SET ROWFILTER
open database southwind
use customers
set rowfilter on
set rowfilter to country = "Germany"
// List records where country = "Germany"
list
set rowfilter off
// List all records
list
set rowfilter to country = "Mexico"
// List records where country = "Mexico"
list
set rowfilter to
// List all records
list

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

 

Scope
NEXT / ALL / RECORD / REST / FIRST
open database southwind
use products order categoryid
// Default current record
display productid, productname
// All records
display all productid, productname
// Default all records
list productid, productname
// Default, process rest of records from current position stopping when record
// does not match the condition
seek 2
list productid, productname while categoryid = 2
// Specific record (record 10 here)
display record 10 productid, productname
// First 10 records from top of file (indexed order here)
list first 10 productid, productname
// Next 20 records from current position (indexed order here)
list next 20 productid, productname
// Process all records, listing those that match the condition
list productid, productname for supplierid = 1

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

The having and where clauses restrict the selection based on specified conditions. Only those rows for which the conditions are true are returned.

// 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

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

Row filters are stored in the system!sysrowfilters table.

Because row filters are stored in a database table you can also use standard SQL commands to perform CRUD operations on the row filters.

insert into system!sysrowfilters ;
    values ('public', 'southwind', 'customers', 'bname', [startsWith(customerid, 'B')])

Notice that ‘public’ is the tenancy that an authenticated user belongs to and ‘bname’ is the role you have assigned to the user. A user can have multiple roles.

If the role you specify is prefixed with a ~ e.g.

insert into system!sysrowfilters ;
    values ('public', 'southwind', 'customers', '~cname', [startsWith(customerid, 'C')])

and the authenticated user does not have the cname role, then all rows where the customerid starts with a ‘C’ are excluded for that user.

You can test data access for different users and roles using the following commands interactively in the console workspace.

open database southwind
use customers
list status 
list

The LIST STATUS command now includes the roles and row filters for each table.

Also see the SET ROWFILTER set command:

set rowfilter on | off
set rowfilter to

and the USERROWFILTER() function:

cRowFilter = userrowfilter([ [, , cTable>]])

and the $rowfilter OData argument, e.g.

/odata/southwind/customers?$rowfilter=country eq 'USA'

https://www.lianja.com/doc/index.php/Category:Row_Level_Security

Table – Delete [examples]

Published on by homunizamLeave a comment
Mark records in the active table for deletion
DELETE
use patrons index names, events
delete all for event = "HAMLET"
 
use patrons index events, names
seek "OPERA"
delete rest;
  for date < date();
  while event = "OPERA"

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

Permanently remove all records from the active table
ZAP
set exclusive on
use newpatrons
zap

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

Remove records from the active table that are marked for deletion

PACK
use diary
? reccount()
       800
set talk on
delete all for date < date()
100 record(s) deleted.
pack
Pack complete, 700 records copied.
? reccount()
       700

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

Reinstate records that are marked for deletion

RECALL
use patrons index events
delete for event = "OPERA" and eventdate < date()
seek "OPERA"
recall rest while event = "OPERA" and eventdate < date()

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

Deletes one or more rows from a table

DELETE FROM
DELETE FROM staff WHERE ord_date < date()

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

Deletes all rows from a table

TRUNCATE TABLE
truncate table southwind!temp

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

Determine whether or not records that are marked for deletion are hidden

SET DELETED
set deleted off
recall all

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

Deletion mark status

DELETED
use patrons
delete
? deleted()
.T.
recall
? deleted()
.F.

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

set deleted off
use example
goto 10
delete
echo deleted() // .T.
recall
echo deleted() // .F.

To physically remove records marked for deletion, the pack command must be issued on the table. The table must be open for exclusive use.

use example exclusive
pack

The zap command can be used to physically delete all the records from a table immediately. The table must be open for exclusive use.

use example exclusive
zap

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

delete from currorders where shippeddate < date()

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

delete from currorders where shippeddate < date()
// 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

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

set deleted off
use example
goto 10
delete
echo deleted() // .T.
recall
echo deleted() // .F.
use example exclusive
pack
use example exclusive
zap

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