| Subject: | Re: Converter Update
| | Date: | Mon, 2 Jul 2018 00:00:42 +0100
| | From: | Michael Kennedy <Info@KennedySoftware.ie>
| | Newsgroups: | pnews.paradox-dos
|
WOW!!!!
Just one point (that I touched on previously)... I never use the
QUERY/ENDQUERY approach, and I think others did likewise - mainly for
readability of the PAL code.
Instead:
1. I used QBE to build the on-screen query, interactively.
2. I then ran that QRY_2_PAL script, to convert the on-screen query
to standard PAL code (see below).
3. I then put that PAL code into the main app Script, followed by a
DO_IT!, and some basic checks that the query ran successfully.
During execution, that PAL code built the query, just like the
QUERY/ENDQUERY approach, and then the DO_IT! executed it.
So, this is all the converter would see (no QUERY/ENDQUERY syntax):
MENU {Ask} SELECT Trans_01_Tmp
[Account No] = "_ac1"
[Year End Date] = "_yr1"
[Run Batches] = "Y"
[Business Typ] = "F or B"
MENU {Ask} SELECT Trans_51_Tmp
MOVETO [Account No] "_ac1" CHECKPLUS
MOVETO [Year End Date] "_yr1" CHECKPLUS
[Bank Code] = "Blank or 0"
[Lodgement] = "_am1,calc 0-_am1 as Amt"
Do_It! Qry_is_Ok("C18") Add "Answer" Temp_1 Delete "Answer" ClearAll
- Mike
On 01/07/2018 18:43, Bernie van't Hof wrote:
> Still nibbling away ..
> Some changes have been made after recent discussions (thank you).
> Github updated. Most changes around:
> pdox/converter/qbeQuery/toSql/Query.php
> pdox/converter/qbeQuery/PalQuery.php
> Feedback welcome!
>
>
> query
>
> transact | Src | Dest |
> | _a | 3121 |
> | CHECK | _a |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `t11`.`Src` AS `Src`
> FROM
> `transact` AS `t10`
> JOIN `transact` AS `t11` ON `t11`.`Dest` = `t10`.`Src`
> WHERE
> `t10`.`Dest` = '3121'
> ORDER BY
> BINARY `t11`.`Src`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> clients | Client# | Co name |
> | Check | check |
>
> Endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Client#` AS `Client#`,
> `Co name` AS `Co name`
> FROM
> `clients`
> ORDER BY
> BINARY `Client#`,
> BINARY `Co name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> clients | Client# | Co name |
> | _a! | check |
>
> transact | A/C# | Name | Amt |
> | _a | check | <1 |
> | _a | check | >100 |
>
> Endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `t10`.`Co name` AS `Co name`,
> `t20`.`Name` AS `Name`
> FROM
> `clients` AS `t10`
> JOIN `transact` AS `t20` ON `t20`.`A/C#` = `t10`.`Client#`
> WHERE
> (`t20`.`Amt` < '1')
> OR (`t20`.`Amt` > '100')
> ORDER BY
> BINARY `t10`.`Co name`,
> BINARY `t20`.`Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name |Src | Dest |
> | | checkplus | | 3121 |
> | | checkplus | 3111 | |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> (`Dest` = '3121')
> OR (`Src` = '3111')
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name | Date
|
> Src | Dest |
> | checkplus as cheName, Test+"gh\"gkorkk"| 25-Jan-.. | count
> >2, not count all > 1 | average <100 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `Name` AS `cheName`
> FROM
> `transact`
> WHERE
> `Name` = CONCAT('Test', 'gh\"gkorkk') AND
> DATE_FORMAT(`Date`, '%d-%b-%Y') LIKE '25-Jan-%'
> GROUP BY
> `Name`
> HAVING
> NOT COUNT(`Src`) > '1' AND
> AVG(`Dest`) < '100'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> accnums | A/C# | Account Name |
> | checkdesc _x ,_t | CHECK |
>
> gstacc | GSTA/C | TargetAcc |
> | _g! | _t ,_x |
>
> transact | A/C# |
> Name
|
> | check >0000 | checkdesc _n , "ghg\"kkk", calc _n + test as
> CalcTest |
>
> transact |
> Amt
> |
> | CHECK _aa, CALC count as other, CALC count all, calc 3 +2,
> >_aa +2, calc sum, not count>2 |
>
> transact | GSTA/C | Src | Dest | Date |
> | _g | _s | _s | >TODAY or 23-Jun.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `t10`.`A/C#` AS `A/C#`,
> `t10`.`Account Name` AS `Account Name`,
> `t30`.`A/C#` AS `A/C#-1`,
> `t30`.`Name` AS `Name`,
> CONCAT(`t30`.`Name`, 'test ') AS `CalcTest`,
> `t30`.`Amt` AS `Amt`,
> COUNT(DISTINCT `t30`.`Amt`) AS `Other`,
> COUNT(`t30`.`Amt`) AS `COUNT of Amt`,
> '3' + '2' AS `3 + 2`,
> SUM(`t30`.`Amt`) AS `SUM of Amt`
> FROM
> `accnums` AS `t10`
> JOIN `gstacc` AS `t20` ON `t20`.`TargetAcc` = `t10`.`A/C#`
> JOIN `transact` AS `t30` ON `t30`.`GSTA/C` = `t20`.`GSTA/C`
> WHERE
> `t30`.`A/C#` > '0000' AND
> `t30`.`Name` = 'ghg\"kkk' AND
> (`t30`.`Date` > CURDATE() OR DATE_FORMAT(`t30`.`Date`, '%d-%b-%Y')
> LIKE '23-Jun%') AND
> `t30`.`Amt` > `t30`.`Amt` + '2' AND
> `t30`.`Dest` = `t30`.`Src`
> GROUP BY
> `t30`.`A/C#`, `t30`.`Name`, `t30`.`Amt`
> HAVING
> NOT COUNT(DISTINCT `t30`.`Amt`) > '2'
> ORDER BY
> BINARY `t10`.`A/C#` DESC,
> BINARY `t10`.`Account Name`,
> BINARY `t30`.`A/C#`,
> BINARY `t30`.`Name` DESC,
> `CalcTest`,
> `t30`.`Amt`,
> `Other`,
> `COUNT of Amt`,
> `3 + 2`,
> `SUM of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name | Date |
> delete | Test | 25-Jan-2015 |
> delete | Fred | 2016.12.23 |
>
> endquery
>
> QUERY TYPE: Delete
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Deleted`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Deleted` AS (
>
> SELECT *
> FROM
> `transact`
> WHERE
> (`Name` = 'Test' AND
> `Date` = '2015-01-25')
> OR (`Name` = 'Fred' AND
> `Date` = '2016-12-23')
> )
>
> SQL RESULT: OK
>
> DELETE
> FROM
> `transact`
> WHERE
> (`Name` = 'Test' AND
> `Date` = '2015-01-25')
> OR (`Name` = 'Fred' AND
> `Date` = '2016-12-23')
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> clients | Client# | Co name |
> | _c | _n, t.. |
>
> transact | A/C# | Name | Date |
> delete | _c | _n | 25-Jan-2015 |
> delete | _c | _n | 24-Jan-2015 |
>
> endquery
>
> QUERY TYPE: Delete
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Deleted`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Deleted` AS (
>
> SELECT `t20`.*
> FROM
> `clients` AS `t10`
> JOIN `transact` AS `t20` ON `t20`.`A/C#` = `t10`.`Client#` AND
> `t20`.`Name` = `t10`.`Co name`
> WHERE
> (`t10`.`Co name` LIKE 't%')
> AND
> ((`t20`.`Date` = '2015-01-25')
> OR (`t20`.`Date` = '2015-01-24'))
> )
>
> SQL RESULT: OK
>
> DELETE `t20`
> FROM
> `clients` AS `t10`
> JOIN `transact` AS `t20` ON `t20`.`A/C#` = `t10`.`Client#` AND
> `t20`.`Name` = `t10`.`Co name`
> WHERE
> (`t10`.`Co name` LIKE 't%')
> AND
> ((`t20`.`Date` = '2015-01-25')
> OR (`t20`.`Date` = '2015-01-24'))
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name | Date |
> | _c | _n, t.. | |
> DELETE | _c | _n | 25-Jan-2015 |
>
> endquery
>
> QUERY TYPE: Delete
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Deleted`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Deleted` AS (
>
> SELECT `t11`.*
> FROM
> `transact` AS `t10`
> JOIN `transact` AS `t11` ON `t11`.`A/C#` = `t10`.`A/C#` AND
> `t11`.`Name` = `t10`.`Name`
> WHERE
> (`t10`.`Name` LIKE 't%')
> OR (`t11`.`Date` = '2015-01-25')
> )
>
> SQL RESULT: OK
>
> DELETE `t11`
> FROM
> `transact` AS `t10`
> JOIN `transact` AS `t11` ON `t11`.`A/C#` = `t10`.`A/C#` AND
> `t11`.`Name` = `t10`.`Name`
> WHERE
> (`t10`.`Name` LIKE 't%')
> OR (`t11`.`Date` = '2015-01-25')
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name | Date | Src | Dest |
> insert | 0000 | testttt | 23-Jan-2015 | 4321 | |
>
> endquery
>
> QUERY TYPE: Insert
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Inserted`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Inserted` LIKE `transact`
>
> SQL RESULT: OK
>
> INSERT INTO `pvt_BERNIE_Inserted` (
> `A/C#`,
> `Name`,
> `Date`,
> `Src`
> )
> SELECT
> '0000',
> 'testttt',
> '2015-01-23',
> '4321'
> FROM
> `transact`
>
> SQL RESULT: OK
>
> INSERT INTO `transact` SELECT * FROM `pvt_BERNIE_Inserted`
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name |
> | _a | _n, t.. |
> insert | _a | _n + "te st" |
>
> endquery
>
> QUERY TYPE: Insert
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Inserted`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Inserted` LIKE `transact`
>
> SQL RESULT: OK
>
> INSERT INTO `pvt_BERNIE_Inserted` (
> `A/C#`,
> `Name`
> )
> SELECT
> `t10`.`A/C#`,
> CONCAT(`t10`.`Name`, 'te st')
> FROM
> `transact` AS `t10`
> WHERE
> `t10`.`Name` LIKE 't%'
>
> SQL RESULT: OK
>
> INSERT INTO `transact` SELECT * FROM `pvt_BERNIE_Inserted`
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> clients | Client# | Co name |
> | _x | _n, T.. |
>
> transact | A/C# | Name | Date |
> | _x, >0001 | | <today
|
> insert | _x | _x + _n + test | 12-Jan-2015 |
>
> endquery
>
> QUERY TYPE: Insert
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Inserted`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Inserted` LIKE `transact`
>
> SQL RESULT: OK
>
> INSERT INTO `pvt_BERNIE_Inserted` (
> `A/C#`,
> `Name`,
> `Date`
> )
> SELECT
> `t20`.`A/C#`,
> CONCAT(CONCAT(`t20`.`A/C#`, `t10`.`Co name`), 'test'),
> '2015-01-12'
> FROM
> `clients` AS `t10`
> JOIN `transact` AS `t20` ON `t20`.`A/C#` = `t10`.`Client#`
> WHERE
> (`t10`.`Co name` LIKE 'T%')
> AND
> (`t20`.`A/C#` > '0001' AND
> `t20`.`Date` < CURDATE())
>
> SQL RESULT: OK
>
> INSERT INTO `transact` SELECT * FROM `pvt_BERNIE_Inserted`
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> clients | Client# | Co name |
> | _a, 0000 | _n |
>
> transact | A/C# | Date | Name |
> fast insert | _a | TODAY | _n + test |
>
> endquery
>
> QUERY TYPE: Insert (FAST)
>
> INSERT INTO `transact` (
> `A/C#`,
> `Name`,
> `Date`
> )
> SELECT
> `t10`.`Client#`,
> CONCAT(`t10`.`Co name`, 'test'),
> CURDATE()
> FROM
> `clients` AS `t10`
> WHERE
> `t10`.`Client#` = '0000'
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name | Date |
> | Test | 23-Jan-2015, changeto 25-Jan-2015 |
>
> endquery
>
> QUERY TYPE: Update
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Changed`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Changed` AS (
> SELECT *
> FROM
> `transact`
> WHERE
> `Name` = 'Test' AND
> `Date` = '2015-01-23'
> )
>
> SQL RESULT: OK
>
> UPDATE `transact`
> SET `Date` = '2015-01-25'
> WHERE
> `Name` = 'Test' AND
> `Date` = '2015-01-23'
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> clients | Client# | Co name |
> | _x | _n, T.. |
>
> transact | A/C# | Name | Date
|
> | _x | _n, changeto _n + test | changeto 25-Jan-2015 |
>
> endquery
>
> QUERY TYPE: Update
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Changed`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Changed` AS (
> SELECT *
> FROM
> `clients` AS `t10`
> JOIN `transact` AS `t20` ON `t20`.`A/C#` = `t10`.`Client#` AND
> `t20`.`Name` = `t10`.`Co name`
> WHERE
> `t10`.`Co name` LIKE 'T%'
> )
>
> SQL RESULT: OK
>
> UPDATE `transact` AS `t20`
> JOIN `clients` AS `t10` ON `t10`.`Client#` = `t20`.`A/C#` AND
> `t10`.`Co name` = `t20`.`Name`
> SET `t20`.`Name` = CONCAT(`t10`.`Co name`, 'test'), `t20`.`Date` =
> '2015-01-25'
> WHERE
> `t10`.`Co name` LIKE 'T%'
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> clients | Client# | Co name |
> | _x | _n, T.. |
>
> transact | A/C# | Name | Date
|
> fast | _x | _n, changeto _n + test | changeto 25-Jan-2015 |
>
> endquery
>
> QUERY TYPE: Update (FAST)
>
> UPDATE `transact` AS `t20`
> JOIN `clients` AS `t10` ON `t10`.`Client#` = `t20`.`A/C#` AND
> `t10`.`Co name` = `t20`.`Name`
> SET `t20`.`Name` = CONCAT(`t10`.`Co name`, 'test'), `t20`.`Date` =
> '2015-01-25'
> WHERE
> `t10`.`Co name` LIKE 'T%'
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check ..Ju.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> DATE_FORMAT(`Date`, '%d-%b-%Y') LIKE '%Ju%'
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | _x, calc 6 - _x |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '6' - `Amt` AS `6 - Amt`
> FROM
> `transact`
> ORDER BY
> `6 - Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | checkplus |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `Amt` AS `Amt`
> FROM
> `transact`
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check >1 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` > '1'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check -6*(-4-3) |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '-6' * ('-4' - '3')
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check < 1 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` < '1'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check <1 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` < '1'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | checkdesc |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> ORDER BY
> `Amt` DESC
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt | Name |
> | check | checkplus |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`,
> `Amt` AS `Amt`
> FROM
> `transact`
> ORDER BY
> BINARY `Name`,
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 123 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '123'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 1.2 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '1.2'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 9 or 10 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> (`Amt` = '9' OR `Amt` = '10')
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 9, 10 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '9' AND
> `Amt` = '10'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check today -1 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> `Date` = DATE_SUB(CURDATE(), INTERVAL CEIL(1) DAY)
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check today |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> `Date` = CURDATE()
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check 1 + today |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> `Date` = DATE_ADD(CURDATE(), INTERVAL CEIL('1') DAY)
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check 23-Nov-2017-4 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> `Date` = DATE_SUB('2017-11-23', INTERVAL CEIL(4) DAY)
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check ~date.full |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> `Date` = '2015-10-09'
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date
|
> | checkplus 11-Jan-2016 - 1/10/2016 + today - 4 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> `Date` = DATE_SUB(DATE_ADD(CURDATE(), INTERVAL
> CEIL(DATEDIFF('2016-01-11', '2016-01-10')) DAY), INTERVAL CEIL(4) DAY)
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name | Amt
|
> | checkplus as q2 | checkplus as MyName | checkplus as Amtt |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `A/C#` AS `q2`,
> `Name` AS `MyName`,
> `Amt` AS `Amtt`
> FROM
> `transact`
> ORDER BY
> BINARY `A/C#`,
> BINARY `Name`,
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc sum |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> SUM(`Amt`) AS `SUM of Amt`
> FROM
> `transact`
> ORDER BY
> `SUM of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc min |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> MIN(`Amt`) AS `MIN of Amt`
> FROM
> `transact`
> ORDER BY
> `MIN of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc average |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> AVG(`Amt`) AS `Average of Amt`
> FROM
> `transact`
> ORDER BY
> `Average of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc max |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> MAX(`Amt`) AS `MAX of Amt`
> FROM
> `transact`
> ORDER BY
> `MAX of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc count |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> COUNT(DISTINCT `Amt`) AS `COUNT of Amt`
> FROM
> `transact`
> ORDER BY
> `COUNT of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc sum unique |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> SUM(DISTINCT `Amt`) AS `SUM of Amt`
> FROM
> `transact`
> ORDER BY
> `SUM of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name |
> | check "str'lit" |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> `Name` = 'str''lit'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 9. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '9.'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check .9 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '.9'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check .2 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '.2'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check 9-.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> DATE_FORMAT(`Date`, '%d-%b-%Y') LIKE '09-%'
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check 9-.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> DATE_FORMAT(`Date`, '%d-%b-%Y') LIKE '09-%'
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 9@ |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` LIKE '9_'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check @8 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` LIKE '_8'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 7@6 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` LIKE '7_6'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check ..9 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` LIKE '%9'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check 9.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` LIKE '9%'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | check ..9.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` LIKE '%9%'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name |
> | check b@ |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> `Name` LIKE 'b_'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name |
> | check @b |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> `Name` LIKE '_b'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name |
> | check c@d |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> `Name` LIKE 'c_d'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name |
> | check ..e |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> `Name` LIKE '%e'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name |
> | check f.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> `Name` LIKE 'f%'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name |
> | check ..g.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> `Name` LIKE '%g%'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> WHERE
> `Date` = '2016-06-03'
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt | Date | Name |
> | not 3 | not 10/23/2016 | check not Fred.. |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> NOT `Name` LIKE 'Fred%' AND
> NOT `Date` = '2016-10-23' AND
> NOT `Amt` = '3'
> ORDER BY
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check blank |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Date |
> | check not blank |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Date` AS `Date`
> FROM
> `transact`
> ORDER BY
> `Date`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc 4 + 3 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '4' + '3' AS `4 + 3`
> FROM
> `transact`
> ORDER BY
> `4 + 3`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc 4 + 3 as mycalc |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '4' + '3' AS `Mycalc`
> FROM
> `transact`
> ORDER BY
> `Mycalc`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc sum |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> SUM(`Amt`) AS `SUM of Amt`
> FROM
> `transact`
> ORDER BY
> `SUM of Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc sum as mycalc |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> SUM(`Amt`) AS `Mycalc`
> FROM
> `transact`
> ORDER BY
> `Mycalc`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# |
> | calc MIN as fred |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> MIN(`A/C#`) AS `Fred`
> FROM
> `transact`
> ORDER BY
> `Fred`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | calc 2 *3 as jjjjj |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '2' * '3' AS `Jjjjj`
> FROM
> `transact`
> ORDER BY
> `Jjjjj`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt | Name |
> | calc 0 as aaa | calc "" as noName |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> ' ' AS `NoName`,
> '0' AS `Aaa`
> FROM
> `transact`
> ORDER BY
> `NoName`,
> `Aaa`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Src |
> | check _x | 1234 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `A/C#` AS `A/C#`
> FROM
> `transact`
> WHERE
> `Src` = '1234'
> ORDER BY
> BINARY `A/C#`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | checkplus _a, 2 *(3+_a) or _a +4 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> (`Amt` = '2' * ('3' + `Amt`) OR `Amt` = `Amt` + '4')
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | _x, calc 6-_x |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '6' - `Amt` AS `6 - Amt`
> FROM
> `transact`
> ORDER BY
> `6 - Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | CHECK 6-3 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Amt` AS `Amt`
> FROM
> `transact`
> WHERE
> `Amt` = '6' - '3'
> ORDER BY
> `Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | _x, calc 6 - _x |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '6' - `Amt` AS `6 - Amt`
> FROM
> `transact`
> ORDER BY
> `6 - Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | _a, calc -2 *(3+_a) |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '-2' * ('3' + `Amt`) AS `-2 * (3 + Amt)`
> FROM
> `transact`
> ORDER BY
> `-2 * (3 + Amt)`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | _a, calc 2 *(3+_a) |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '2' * ('3' + `Amt`) AS `2 * (3 + Amt)`
> FROM
> `transact`
> ORDER BY
> `2 * (3 + Amt)`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name | Amt |
> | CHECK | _x, calc 0 - _x |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`,
> '0' - `Amt` AS `0 - Amt`
> FROM
> `transact`
> ORDER BY
> BINARY `Name`,
> `0 - Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name | Amt |
> | CHECKPLUS | checkplus _x, calc 0 - _x |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `Name` AS `Name`,
> `Amt` AS `Amt`,
> '0' - `Amt` AS `0 - Amt`
> FROM
> `transact`
> ORDER BY
> BINARY `Name`,
> `Amt`,
> `0 - Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Name | Amt |
> | CHECK | check _x, calc 0 - _x |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `Name` AS `Name`,
> `Amt` AS `Amt`,
> '0' - `Amt` AS `0 - Amt`
> FROM
> `transact`
> ORDER BY
> BINARY `Name`,
> `Amt`,
> `0 - Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | Amt |
> | checkplus _x, calc 0 - _x |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `Amt` AS `Amt`,
> '0' - `Amt` AS `0 - Amt`
> FROM
> `transact`
> ORDER BY
> `Amt`,
> `0 - Amt`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Src |
> | _x | 1234 |
>
> clients | Client# | Co name | Post2 |
> | _x | check | check as myref |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `t20`.`Co name` AS `Co name`,
> `t20`.`Post2` AS `myref`
> FROM
> `transact` AS `t10`
> JOIN `clients` AS `t20` ON `t20`.`Client#` = `t10`.`A/C#`
> WHERE
> `t10`.`Src` = '1234'
> ORDER BY
> BINARY `t20`.`Co name`,
> BINARY `t20`.`Post2`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name | Src | Dest |
> | check,_x, >0000 | _n | _s | _d |
>
> clients | Client# | Co name |
> | check,_x | check _n |
>
> accnums | A/C# | Account Name |
> | check, _d | Check as acccname |
>
> accsrc | A/C# | GSTA/C |
> | check, _s | check |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `t10`.`A/C#` AS `A/C#`,
> `t20`.`Client#` AS `Client#`,
> `t20`.`Co name` AS `Co name`,
> `t30`.`A/C#` AS `A/C#-1`,
> `t30`.`Account Name` AS `acccname`,
> `t40`.`A/C#` AS `A/C#-2`,
> `t40`.`GSTA/C` AS `GSTA/C`
> FROM
> `transact` AS `t10`
> JOIN `clients` AS `t20` ON `t20`.`Client#` = `t10`.`A/C#` AND
> `t20`.`Co name` = `t10`.`Name`
> JOIN `accnums` AS `t30` ON `t30`.`A/C#` = `t10`.`Dest`
> JOIN `accsrc` AS `t40` ON `t40`.`A/C#` = `t10`.`Src`
> WHERE
> `t10`.`A/C#` > '0000'
> ORDER BY
> BINARY `t10`.`A/C#`,
> BINARY `t20`.`Client#`,
> BINARY `t20`.`Co name`,
> BINARY `t30`.`A/C#`,
> BINARY `t30`.`Account Name`,
> BINARY `t40`.`A/C#`,
> BINARY `t40`.`GSTA/C`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> accnums | A/C# | Account Name |
> | _s | check |
>
> transact | A/C# | Amt | Src | Dest |
> | _a | >0 | _s | not _s |
> | _a | <0 | _s | _s |
>
> Clients | Client# | Co name |
> | _a | check |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT DISTINCT
> `t10`.`Account Name` AS `Account Name`,
> `t30`.`Co name` AS `Co name`
> FROM
> `accnums` AS `t10`
> JOIN `transact` AS `t20` ON `t20`.`Src` = `t10`.`A/C#`
> JOIN `Clients` AS `t30` ON `t30`.`Client#` = `t20`.`A/C#`
> WHERE
> (`t20`.`Amt` > '0' AND
> NOT `t20`.`Dest` = `t20`.`Src`)
> OR (`t20`.`Amt` < '0' AND
> `t20`.`Dest` = `t20`.`Src`)
> ORDER BY
> BINARY `t10`.`Account Name`,
> BINARY `t30`.`Co name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name
| Amt |
> | checkplus as a | CHECKPLUS "Fred, Martha or Tim" | >0 |
> | checkplus as a | checkplus fred |
<0 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `A/C#` AS `a`,
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> (`Name` = 'Fred, Martha or Tim' AND
> `Amt` > '0')
> OR (`Name` = 'fred' AND
> `Amt` < '0')
> ORDER BY
> BINARY `A/C#`,
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | A/C# | Name
| Amt |
> | checkplus as a | CHECKPLUS "Fred, Martha or Tim" | >0 |
> | checkplus as b | checkplus fred |
<0 |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> `A/C#` AS `a`,
> `Name` AS `Name`
> FROM
> `transact`
> WHERE
> (`Name` = 'Fred, Martha or Tim' AND
> `Amt` > '0')
> OR (`Name` = 'fred' AND
> `Amt` < '0')
> ORDER BY
> BINARY `A/C#`,
> BINARY `Name`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
> query
>
> transact | GSTAmt |
> | calc 2 *(3 + 4) |
> | calc "s" |
>
> endquery
>
> QUERY TYPE: Display
>
> DROP TABLE IF EXISTS `pvt_BERNIE_Answer`
>
> SQL RESULT: OK
>
> CREATE TABLE `pvt_BERNIE_Answer` AS (
>
> SELECT
> '2' * ('3' + '4') AS `2 * (3 + 4)`
> FROM
> `transact`
> ORDER BY
> `2 * (3 + 4)`
> )
>
> SQL RESULT: OK
> --------------------------------------------------
>
|
