SW-Tools ODBC - Programmers Reference

2022-09-01 008.384

       Me=        Default path the drivers files
       Basis=     Path for BASIS.SSV defining the file system interfaces
       Dmf=       Path for the datadictionary FILES.SSV and xx.SSD
       Isa=       Default path for the database files if needed
       Com=       Company number
       Based=     Normally blank, forces all files to a given BASIS filetype
       Fixfil=    0   Forces the driver to read FILES.SSV whenever accessed
       Upper=     0   Use upper/lowercase names instead of just uppercase
       Fname=     0   Use File ID only as SQL tablenames
       Fnamelen=  n   Use max n characters in tablename length
       Ftext      0   Usage of file text desctiption
       Qualifier= 0   Return NULL instead of file ID as table qualifier
       Owner=     0   Return NULL instead of filetype as table owner
       Lan=       ENG The language is fixed on the disk
       Test=      1   Internal testflags producing a c:\wif testoutput
       Update=    1   Data source is not readonly, requires full release

SELECT *
FROM 'Payment Terms'

SELECT No_, Name, 'Search Name'
FROM Vendor
ORDER BY 2 DESC, 'Search Name'

SELECT *
FROM 'Payment Terms'
WHERE 'Discount %' = 2

SELECT No_, Name, Balance, Balance * 7, Balance + 2 calc, calc + 7 new
FROM Customer
WHERE new + 1 NOT BETWEEN 8 + 1 AND 99

SELECT No_,'Balance','Customer'.'Name'
FROM 'Customer'
WHERE 'Balance' > 1000

SELECT No_, Description, 'Vendor No_'
FROM Item
WHERE 'Vendor No_' IN ("20000", "30000")

SELECT No_, Name, 'Unit Price', a.No_, a.'Vendor No_', a.Description
FROM Vendor, Item AS a
WHERE No_ = a.'Vendor No_' AND 'Unit Price' > 400

SELECT No_, 'Unit Price', a.No_, 'Vendor No_', Vendor.Name
FROM Vendor, OUTER Item a
WHERE 'Vendor No_' = No_ AND 'Unit Price' > 0

SELECT No_, 'Unit Price', a.No_, 'Vendor No_', Name
FROM { oj Vendor LEFT OUTER JOIN Item a ON 'Vendor No_'=No_ }
WHERE No_ > 20000 AND 'Unit Price' > 0

SELECT No_, Name
FROM Vendor a
WHERE EXISTS
(SELECT * FROM Item WHERE 'Vendor No_' = a.No_)

SELECT No_, Balance, Name
FROM Vendor a
WHERE Balance > ALL
(SELECT 'Unit Price' + 7 FROM Item WHERE 'Vendor No_' = a.No_)

SELECT No_, Balance, Name
FROM Vendor a
WHERE Balance < ANY
(SELECT 'Unit Price' + 7 FROM Item WHERE 'Vendor No_' = a.No_)

SELECT No_, Name FROM Vendor
WHERE No_ IN (SELECT 'Vendor No_' FROM Item)

SELECT COUNT(*), SUM(Balance), MAX(Balance), MIN(Balance), AVG(Balance)
FROM Vendor

SELECT No_, Name, b.Name
FROM Vendor a, Vendor b
WHERE a.No_ = b.No_

SELECT No_, Description
FROM Item
WHERE Description NOT LIKE "%O%" { escape 'x' }

SELECT No_, Description
FROM Item
WHERE Description MATCHES "*Door*"

SELECT No_, { fn LCASE(Name) }
FROM Customer

SELECT No_, --(*vendor(SWTools),product(ODBC) fn CONVERT(No_,SQL_INTEGER)*)--, Name
FROM Customer

SELECT No_, 'Order Date', 'Posting Description'
FROM 'Sales Header'
WHERE 'Order Date' > { d '2001-01-21' }

SELECT No_, 'Order Date'
FROM 'Sales Header'
WHERE 'Order Date' > #2001-01-21#

SELECT #1, #31, 'Unit Price', a.#1-2
FROM Item, Vendor a
WHERE #1 MATCHES("19*") AND a.#1 = #31
ORDER BY #31

SELECT No_, Description, 'Budgeted Amount', 'Budgeted Amount'(1), 'Standard Cost'
FROM Item

SELECT 'Vendor No_', COUNT(*), SUM('Unit Price')
FROM Item
GROUP BY 'Vendor No_'

SELECT 'Vendor No_', SUM('Unit Price')
FROM Item
GROUP BY 'Vendor No_'
HAVING COUNT(*) = 1

SELECT DISTINCT 'Vendor No_', Class
FROM Item

SELECT SUM(DISTINCT 'Unit Price'), COUNT(DISTINCT 'Vendor No_')
FROM Item

SELECT No_, 'Unit Price'
FROM Item
WHERE 'Unit Price' > 100
UNION ALL
SELECT No_, 'Standard Cost' FROM Item WHERE 'Standard Cost' < 1000 ORDER BY 2

SELECT *
FROM VALUES ("4701","aa",65),("4702","bb",8)

SELECT *
FROM (SELECT No_, Name FROM Customer)
WHERE Name MATCHES("D*")

SELECT *, a.*
FROM 'Payment Terms', 'Payment Terms' a

SELECT No_, Name, 'Net Change'
FROM 'G/L Account'
WHERE 'Date Filter' = "28-02-00" AND 'Net Change' <> 0

SELECT No_, Name, 'Net Change', 'Date Filter'
FROM 'G/L Account'
WHERE 'Date Filter' = "30-06-00" AND 'Net Change' <> 0

xx = Desided file ID, if omitted or invalid the driver selects a free ID
yyyy = BASIS file interface name (owner), defaults to the first (SSV).

SELECT *
FROM 'Payment Terms'
ORDER BY 1
INTO TEMP za\nf.mytable

SELECT *
FROM mytable

INSERT INTO mytable
VALUES (1,2,3)

SELECT * FROM mytable

INSERT INTO mytable
(SELECT No_ FROM Item WHERE No_ < 70100)

SELECT *
FROM mytable

INSERT INTO mytable
(Code,Description,'Discount %') VALUES ("4701","aa",65),("4702","bb",8)

SELECT *
FROM mytable

INSERT INTO mytable
(Code,Description,'Discount %')
(SELECT No_, Description, 'Unit Price' FROM Item WHERE 'Unit Price' > 500)

SELECT *
FROM mytable

UPDATE mytable
SET 'Discount %' = 10
WHERE 'Discount %' > 500

SELECT *
FROM mytable

DELETE FROM mytable
WHERE 'Discount %' = 10

SQLRowCount(hstmt)

SELECT *
FROM mytable

DROP TABLE mytable

- Item
- Vendor

- SWItem
- SWVendor

SELECT *
FROM Item
ORDER BY 1
INTO TEMP zb\nf.SWItem

SELECT *
FROM Vendor
ORDER BY 1
INTO TEMP zc\nf.SWVendor

SQLGetCursorName(hstmt,cursorname,256,&len)

SQLSetCursorName(hstmt,"mycursor",SQL_NTS)