having trouble with sub select in D4 to get the right results

[JCC007]

Hi all

It was suggested to me to use the following SQL statement

select Access."Key", UserKey, Access, Advised
from access
where (UserKey, Access."Key&quot in (select UserKey, max(Access."key&quot
from access
group by UserKey)
order by UserKey

to get the following result:
Will show:
142, 12, FI83P10, 2002/01/01
143, 13, Spandial, 2000/01/01
145, 14, IDIR, 2001/01/01

Because they are the latest records with each userkey where 'Key' is the max value of each set of records.


from table:

Key, UserKey, Access, Advised

140, 12, FI83P10, 2000/01/01
141, 12, FI83P10, 2001/01/01
142, 12, FI83P10, 2002/01/01
143, 13, Spandial, 2000/01/01
144, 14, IDIR, 2000/01/01
145, 14, IDIR, 2001/01/01

I tried:
strSQL := 'SELECT Max(Access."Key&quot, UserKey, Access, Access."Transaction", Actioned, ActionedBy, Completed, CompletedBy, Advised, AdvisedBy, Clientele ';
AppendStr(strSQL, 'FROM Access ');
AppendStr(strSQL, 'Where UserKey = ' + IntToStr(DM.AccountsKey.Value));
AppendStr(strSQL, 'GROUP BY Access."Key", UserKey, Access, Access."Transaction", Actioned, ActionedBy, Completed, CompletedBy, Advised, AdvisedBy, Clientele ');
AppendStr(strSQL, 'ORDER BY Access ');

but it is not working. Any Delphi 4 SQL gurus out there have any ideas?

Thanks

James

 

[lespaul]

Try this:

strSQL := SELECT Access."Key", UserKey, Access, Access."Transaction", Actioned, ActionedBy, Completed, CompletedBy, Advised, AdvisedBy, Clientele FROM Access Where UserKey = ' + IntToStr(DM.AccountsKey.Value) + 'AND Access."Key" = (SELECT MAX(Access."Key&quot FROM Access) GROUP BY Access."Key", UserKey, Access, Access."Transaction", Actioned, ActionedBy, Completed, CompletedBy, Advised, AdvisedBy, Clientele ORDER BY Access

Do you have to use AppendStr in Delphi4? I use the same technique for adding the AccountsKey.Value when adding to the sql string:

'SELECT * FROM WHEREEVER' +
'WHERE SOMETHING = ANYTHING ' +
'ORDER BY SOMETHING';

Makes it a little easier to read!

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[awarnica]

Try debugging the code and stopping the line after the SQL statement has been completely formed. Hover over the variable and see what your SQL statement looks like.

If any quotes are out of place, you can see them.

"If it is stupid but it works, then it isn't stupid"

 

[LucieLastic]

Make sure you have spaces in for each appendstr, ie before the FROM, WHERE, GROUP and ORDER BY etc.

strSQL := 'SELECT Max(Access."Key&quot, UserKey, Access, Access."Transaction", Actioned, ActionedBy, Completed, CompletedBy, Advised, AdvisedBy, Clientele ';

AppendStr(strSQL, ' FROM Access ');
AppendStr(strSQL, ' Where UserKey = ' + IntToStr(DM.AccountsKey.Value));
AppendStr(strSQL, ' GROUP BY Access."Key", UserKey, Access, Access."Transaction", Actioned, ActionedBy, Completed, CompletedBy, Advised, AdvisedBy, Clientele ');
AppendStr(strSQL, ' ORDER BY Access ');

lou