Paradox Community

Items in pnews.paradox-dos

Subject:Re: single row query - progress
Date:Tue, 15 May 2018 18:29:57 -0400
From:"Steven Green" <greens@diamondsg.com>
Newsgroups:pnews.paradox-dos
you forgot "calc Crazy as QueryType" :-)


--

Steven Green
Myrtle Beach, South Carolina, USA

http://www.OasisTradingPost.com

Collectibles and Memorabilia
Vintage Lego Sets and Parts
- and Paradox support, too
"Bernie van't Hof"  wrote in message 
news:5afb1846@pnews.thedbcommunity.com...

FYI: Testing lots of different concepts

Ignoring the query is silly...

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

SELECT DISTINCT
     `t1`.`A/C#` AS `A/C#`,
     `t1`.`Account Name` AS `Account Name`,
     `t3`.`A/C#` AS `A/C#-1`,
     `t3`.`Name` AS `Name`,
     CONCAT(`t3`.`Name`, 'test ') AS `CalcTest`,
     `t3`.`Amt` AS `Amt`,
     count( `t3`.`Amt`) AS `Other`,
     count(DISTINCT `t3`.`Amt`) AS `Count of Amt`,
     '3' + '2' AS `3 + 2`,
     sum( `t3`.`Amt`) AS `Sum of Amt`
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' AND
     (`t3`.`Date`  >  CURDATE() OR DATE_FORMAT(`t3`.`Date`, '%d-%b-%Y') LIKE 
'23-Jun%') AND
     `t3`.`Amt`  >  `t3`.`Amt` + '2' AND
     `t3`.`Dest` = `t3`.`Src`
GROUP BY
     `t3`.`A/C#` AND `t3`.`Name` AND `t3`.`Amt`
HAVING
      NOT COUNT(`t3`.`Amt`)  >  '2'
ORDER BY
     BINARY `t1`.`A/C#` DESC,
     BINARY `t1`.`Account Name`,
     BINARY `t3`.`A/C#`,
     BINARY `t3`.`Name` DESC,
     `CalcTest`,
     `t3`.`Amt`,
     `Other`,
     `Count of Amt`,
     `3 + 2`,
     `Sum of Amt` 


Copyright © 2004 thedbcommunity.com