![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
![]() |
Thu, May 13 2010 12:05 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | This doesn't work
SELECT _ID, _Name, _Type, _Created, (SELECT MAX(_Date) FROM CallStats WHERE _fkProjects = _ID) AS _LastCallDate, (SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID) AS _Planned, (SELECT COUNT(*) FROM Calls WHERE _LastResult IS NULL AND _fkProjects = _ID) AS _NoResult, (SELECT COUNT(*) FROM Calls WHERE NOT _Done AND _fkProjects = _ID) AS _NotComplete, (SELECT COUNT(*) FROM (SELECT DISTINCT _fkProjects,_fkCalls FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _Called FROM Projects but this SELECT _ID, _Name, _Type, _Created, (SELECT MAX(_Date) FROM (SELECT _Date,_fkProjects FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _LastCallDate, (SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID) AS _Planned, (SELECT COUNT(*) FROM Calls WHERE _LastResult IS NULL AND _fkProjects = _ID) AS _NoResult, (SELECT COUNT(*) FROM Calls WHERE NOT _Done AND _fkProjects = _ID) AS _NotComplete, (SELECT COUNT(*) FROM (SELECT DISTINCT _fkProjects,_fkCalls FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _Called FROM Projects does. Its the first of the sub-selects that gives a problem - returns NULL in the first code. Roy Lambert |
Thu, May 13 2010 2:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< This doesn't work >> Please send me the database catalog and tables that you're using via email. I always need the data to test this stuff. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, May 14 2010 2:32 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
OK I thought it might be WAD and I'd just get my poor old brain confused by your brilliant technical explanation ![]() You already have the catalog and CallStats for the "b13 - Queries with subselects" email Roy Lambert |
Fri, May 28 2010 6:45 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< OK I thought it might be WAD and I'd just get my poor old brain confused by your brilliant technical explanation ![]() Sorry about the delay on this - I forgot to tell you the answer yesterday when I figured it out finally: This doesn't work SELECT _ID, _Name, _Type, _Created, (SELECT MAX(_Date) FROM CallStats WHERE _fkProjects = _ID) AS _LastCallDate, FROM Projects The problem here is the _ID reference - it should be this: (SELECT MAX(_Date) FROM CallStats WHERE _fkProjects = Projects._ID) AS _LastCallDate, because CallStats also has an _ID column. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, May 28 2010 8:53 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>because CallStats also has an _ID column. I know my head isn't working to well right now but even so I should have spotted that. I think I've finally passed my shelf life. Victor Meldrew move over. Roy Lambert |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |