Supporting Functions

In BS_SQL function, you can use many supporting functions to make own reports from details reports to manager and analyzing reports. We list typical and commonly used supporting functions:
1. Recno(): Put numerical order automatically. This supporting function is after SELECT (Ex: BS_SQL("SELECT RECNO(), ITEMS, NAME_ID....")
2. Distinct: Get unique list or remove duplicate. It is after SELECT (ex: BS_SQL("SELECT DISTINCT ITEM_ID FROM INVENTORY")
3. =, <>, >=, <=, LIKE, NOT LIKE, AND, OR...(ex:BS_SQL("SELECT * FROM INVENTORY WHERE QLT>=100 AND ITEMS_ID LIKE 'HH%'")
4. RANGE, CELL: Use compare with one cell (ex: BS_SQL("SELECT * FROM INVENTORY WHERE ITEMS_ID=RANGE(A2) AND DATE>=CELL(A1)")
5. SUM, COUNT, AVG...+,-,*,/: Only use for numeric fields. (ex: BS_SQL("SELECT ITEMS_ID, SUM(QLT) FROM INVENTORY GROUP BY ITEMS_ID")
6. IIF, FIELDIF: Use to get value with conditional logic. (ex: BS_SQL("SELECT ITEMS_ID, SUM(IIF(DATE<RANGE(A1),QLT,0)), FIELDIF(DATE<RANGE(A1),AMOUT,0,SUM) FROM INVENTORY GROUP BY ITEMS_ID")
7. INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN: Use to get data from more than one table to make reports. Ex: BS_SQL("SELECT INVENTORY.ITEMS_ID, LIST.ITEMS_NAME, SUM(QLT) FROM INVENTORY INNER JOIN LIST ON INVENTORY.ITEMS_ID=LIST.ITEMS_ID GROUP BY ITEMS_ID")
8. AS: Use to header name. ex: BS_SQL("SELECT ITEMS_ID, SUM(QLT) AS [SUM OF QLT] FROM INVENTORY GROUP BY ITEMS_ID
9. VTC: Value to column. Ex: BS_SQL("SELECT ITEMS_ID, VTC(YEAR(DATE),SUM,QLT) FROM INVENTORY GROUP BY ITEMS_ID")
10.....
Email to Support: vuducthinh.uct@gmail.com