Paradox Community

Items in pnews.paradox-dos

Subject:Converter Update
Date:Mon, 2 Jul 2018 03:43:00 +1000
From:Bernie van't Hof <berniev@bje.com.au>
Newsgroups:pnews.paradox-dos
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
--------------------------------------------------


Copyright © 2004 thedbcommunity.com