Paradox Community

Items in pnews.paradox-dos

Subject:update
Date:Mon, 9 Apr 2018 02:30:55 +1000
From:Bernie van't Hof <berniev@bje.com.au>
Newsgroups:pnews.paradox-dos
At last. Some worthwhile progress to report.

Insert, delete, display and update queries seem to be generating the right sql.

Not done yet: De-dupe result column names (-1, -2 etc), 'Fast'

If you spot any dumb stuff, pls advise!

Github updated.

- Bernie
======================================================
Results (queries below):


==  display ==


DROP TABLE IF EXISTS `pvt_BERNIE_Answer`

SELECT
     `Name` AS `CheName`
FROM
     `transact`
WHERE
     `Name` = CONCAT('Test', 'ghgkkk') AND
     DATE_FORMAT(`Date`, '%d-%b-%Y') LIKE '25-Jan-%'

CREATE TABLE `pvt_BERNIE_Answer` AS (
SELECT
     `Name` AS `CheName`
FROM
     `transact`
WHERE
     `Name` = CONCAT('Test', 'ghgkkk') AND
     DATE_FORMAT(`Date`, '%d-%b-%Y') LIKE '25-Jan-%'
)


==  display ==


DROP TABLE IF EXISTS `pvt_BERNIE_Answer`

SELECT DISTINCT
     `t1`.`Account Name`,
     `t3`.`A/C#`,
     `t3`.`Name`,
     CONCAT(`t3`.`Name`, 'test'),
     count(DISTINCT `t3`.`Amt`) AS `Other`,
     count( `t3`.`Amt`) AS `Fred`,
     '3' + '2'
FROM
     `accnums` AS `t1`
     JOIN `gstacc` AS `t2` ON `t2`.`TargetAcc` = `t1`.`A/C#`
     JOIN `transact` AS `t3` ON `t3`.`GSTA/C` = `t2`.`GSTA/C`
WHERE
     `t3`.`A/C#`  >  '0000' AND
     `t3`.`Name` = 'ghg\"kkk'
ORDER BY
     BINARY `t1`.`Account Name`,
     BINARY `t3`.`A/C#`,
     BINARY `t3`.`Name` DESC,
     BINARY CONCAT(`t3`.`Name`, 'test'),
     `Other`,
     `Fred`,
     '3' + '2'

CREATE TABLE `pvt_BERNIE_Answer` AS (
SELECT DISTINCT
     `t1`.`Account Name`,
     `t3`.`A/C#`,
     `t3`.`Name`,
     CONCAT(`t3`.`Name`, 'test'),
     count(DISTINCT `t3`.`Amt`) AS `Other`,
     count( `t3`.`Amt`) AS `Fred`,
     '3' + '2'
FROM
     `accnums` AS `t1`
     JOIN `gstacc` AS `t2` ON `t2`.`TargetAcc` = `t1`.`A/C#`
     JOIN `transact` AS `t3` ON `t3`.`GSTA/C` = `t2`.`GSTA/C`
WHERE
     `t3`.`A/C#`  >  '0000' AND
     `t3`.`Name` = 'ghg\"kkk'
ORDER BY
     BINARY `t1`.`Account Name`,
     BINARY `t3`.`A/C#`,
     BINARY `t3`.`Name` DESC,
     BINARY CONCAT(`t3`.`Name`, 'test'),
     `Other`,
     `Fred`,
     '3' + '2'
)


==  insert ==


DROP TABLE IF EXISTS `pvt_BERNIE_Inserted`

CREATE TABLE `pvt_BERNIE_Inserted` LIKE `transact`

INSERT INTO `pvt_BERNIE_Inserted`(
     `Name`,
     `Date`
)
SELECT
     CONCAT(`t1`.`Co name`, 'test'),
     CURDATE()
FROM
     `clients`
WHERE
     `t1`.`Client#` = '0000'

INSERT INTO `transact` SELECT * FROM `pvt_BERNIE_Inserted`


==  insert ==


DROP TABLE IF EXISTS `pvt_BERNIE_Inserted`

CREATE TABLE `pvt_BERNIE_Inserted` LIKE `transact`

INSERT INTO `pvt_BERNIE_Inserted`(
     `A/C#`,
     `Name`,
     `Date`,
     `Src`,
     `Dest`
)
VALUES (
     '0000',
     'testttt',
     '2015-01-23',
     '4321',
     '1234'
)

INSERT INTO `transact` SELECT * FROM `pvt_BERNIE_Inserted`


==  insert ==


DROP TABLE IF EXISTS `pvt_BERNIE_Inserted`

CREATE TABLE `pvt_BERNIE_Inserted` LIKE `transact`

INSERT INTO `pvt_BERNIE_Inserted`(
     `Name`
)
SELECT
     CONCAT(`transact`.`Name`, 'te st')
FROM
     `transact`
WHERE
     `Name` LIKE 't%'

INSERT INTO `transact` SELECT * FROM `pvt_BERNIE_Inserted`


==  insert ==


DROP TABLE IF EXISTS `pvt_BERNIE_Inserted`

CREATE TABLE `pvt_BERNIE_Inserted` LIKE `transact`

INSERT INTO `pvt_BERNIE_Inserted`(
     `Name`,
     `Date`
)
SELECT
     CONCAT(CONCAT(`t1`.`Client#`, `t1`.`Co name`), 'test'),
     '2015-01-12'
FROM
     `clients` AS `t1`
     JOIN `transact` AS `t2` ON `t2`.`A/C#` = `t1`.`Client#`
WHERE
     (`t1`.`Co name` LIKE 'T%')
     OR
     (`t2`.`A/C#`  >  '0001' AND
     `t2`.`Date`  <  CURDATE())

INSERT INTO `transact` SELECT * FROM `pvt_BERNIE_Inserted`


==  update ==


DROP TABLE IF EXISTS `pvt_BERNIE_Changed`

CREATE TABLE `pvt_BERNIE_Changed` AS (
SELECT * FROM `transact`
)

UPDATE `transact` SET (
     `Name`,
     `Date`,
     `Date`
)
VALUES (
     'Test',
     '2015-01-23',
     '2015-01-25'
)


==  update ==


DROP TABLE IF EXISTS `pvt_BERNIE_Changed`

CREATE TABLE `pvt_BERNIE_Changed` AS (
SELECT * FROM `transact`
WHERE
     (`t1`.`Co name` LIKE 'T%')
     OR
     (`t2`.`Name` = CONCAT(`t1`.`Co name`, 'test') AND
     `t2`.`Date` = '2015-01-25')
)

UPDATE `transact` SET (
     `Name`,
     `Date`
)
SELECT
     CONCAT(`t1`.`Co name`, 'test'),
     '2015-01-25'
FROM
     `clients` AS `t1`
     JOIN `transact` AS `t2` ON `t2`.`A/C#` = `t1`.`Client#` AND `t2`.`Name` = `t1`.`Co name`
WHERE
     (`t1`.`Co name` LIKE 'T%')
     OR
     (`t2`.`Name` = CONCAT(`t1`.`Co name`, 'test') AND
     `t2`.`Date` = '2015-01-25')


==  delete ==


DROP TABLE IF EXISTS `pvt_BERNIE_Deleted`

CREATE TABLE `pvt_BERNIE_Deleted` AS (
SELECT *
FROM
     `transact`
WHERE
     `Name` = 'Test' AND
     `Date` = '2015-01-25'
)

DELETE
FROM
     `transact`
WHERE
     `Name` = 'Test' AND
     `Date` = '2015-01-25'


==  delete ==


DROP TABLE IF EXISTS `pvt_BERNIE_Deleted`

CREATE TABLE `pvt_BERNIE_Deleted` AS (
SELECT *
FROM
     `clients` AS `t1`
     JOIN `transact` AS `t2` ON `t2`.`A/C#` = `t1`.`Client#` AND `t2`.`Name` = `t1`.`Co name`
WHERE
     (`t1`.`Co name` LIKE 't%')
     OR
     (`t2`.`Date` = '2015-01-25')
)

DELETE
FROM
     `clients` AS `t1`
     JOIN `transact` AS `t2` ON `t2`.`A/C#` = `t1`.`Client#` AND `t2`.`Name` = `t1`.`Co name`
WHERE
     (`t1`.`Co name` LIKE 't%')
     OR
     (`t2`.`Date` = '2015-01-25')

============================================================
Queries:

PROC ShowCount()
   c = c + 1
   MESSAGE("Query number " + STRVAL(c))
ENDPROC

;PLAY DataDir + "C\\PDOXDATA\\PUBLICSC\\TESTdate"

ShowCount()
query

transact | Name                                  | Date      |
          | checkplus as cheName, Test+"ghgkkk" | 25-Jan-.. |

endquery
Do_It!
clearall

ShowCount()
   	query

accnums | A/C#  | Account Name |
         | _t    | CHECK        |

gstacc | GSTA/C | TargetAcc |
        | _g     | _t        |

transact | A/C#          |  Name                         | Amt                             
           | GSTA/C |
          | check   >0000 |  checkdesc   _n , "ghg\"kkk", calc _n + test   | CHECK CALC count
as other, CALC count all 
as fred, calc 3 +2 | _g     |

endquery
Do_It!
clearall

ShowCount()
   	query

clients | Client#  | Co name |
         | _a, 0000 | _n      |

transact | A/C# | Date  | Name      |
  fast insert  | _a   | TODAY | _n + test |

endquery
Do_It!
clearall

ShowCount()
query

transact | A/C# | Name    | Date        | Src  | Dest |
  insert  | 0000 | testttt | 23-Jan-2015 | 4321 | 1234 |

endquery
Do_It!
clearall

ShowCount()
query

transact | A/C# | Name        |
          | _a   | _n, t..     |
  insert  | _a   | _n + "te st" |

endquery
Do_It!
clearall

ShowCount()
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
; _x is a value, not a JOIN
; _n is a value, not a JOIN
; Date is a value
; T.. in Date is a where
; if there was another table linked to clients, it would be a join
Do_It!
clearall

ShowCount()
query

transact | Name | Date                              |
          | Test | 23-Jan-2015, changeto 25-Jan-2015 |

endquery
Do_It!
clearall

ShowCount()
query

clients | Client#  | Co name |
         | _x       | _n, T.. |

transact | A/C# | Name                   | Date                 |
          | _x   | _n, changeto _n + test | changeto 25-Jan-2015 |

endquery
Do_It!
clearall

ShowCount()
query

transact | Name | Date        |
  delete  | Test | 25-Jan-2015 |

endquery
Do_It!
clearall

ShowCount()
query

clients | Client# | Co name |
         | _c      | _n, t.. |

transact | A/C# | Name | Date        |
  delete  | _c   | _n   | 25-Jan-2015 |

endquery
Do_It!
clearall



Copyright © 2004 thedbcommunity.com