"Igor Tandetnik" wrote...
On 5/30/2014 1:29 PM, jose isaias cabrera wrote:
Good point. I was, wrongly, thinking that it was top to bottom with the
id. So, the idea is that once Xtra4='y' has provided a value, that is
what I want. So, I think that because I am always getting 'noemail'
then, it is caused because the last record read had Xtra4!='y'. So,
this is not properly giving the result desired. So, how can I change
this,
CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END,
so that once I have a value from Xtra4='y', I want to keep that value as
the result. Thanks.
SELECT ...
ifnull(
(select t2.vEmail from LSOpenJobs t2
where t2.ProjID = min(CASE t1.Xtra4 WHEN 'y' THEN t1.ProjID ELSE null
END)),
'noemail'),
...
from LSOpenJobs t1 ...;
This gives you, for each group, vEmail value corresponding to the row with
the smallest ProjID among those where Xtra4='y'; if there's no such row,
it produces 'noemail'.
Thanks for your help, Igor. This is how I put the SELECT together? Is this
correct? I am sorry, I am not that good at this SQL login, as it is so
immense:
===begin SQL==
SELECT cust,
ProjID,
proj,
A_No,
bdate,
ifnull(
(select t2.vEmail from LSOpenJobs t2
where t2.ProjID = min(CASE t1.Xtra4 WHEN 'y' THEN t1.ProjID ELSE
null END)),
'noemail'),
sum(ProjFund),
sum(ProjFund)-sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END),
sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END)
from LSOpenJobs t1
WHERE
lower(cust) = 'phin' AND
PClass='Language' AND
(bdate BETWEEN '2014-01-15' AND '2014-05-15')
GROUP BY cust, ProjID, proj, A_No
HAVING
sum(case Xtra4 when 'y' then invoice else 0 end) > 0;
===end SQL==
If this is correct, then, I am getting a
Error: misuse of aggregate function min()
Any thought will help immensely. Thanks.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
