On Sun, Jun 20, 2010 at 9:56 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Sam Carleton <scarle...@miltonstreet.com> wrote:
>> On Sun, Jun 20, 2010 at 5:23 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
>>
>>> In what way did the statement I gave you, exactly as written, fail to
>>> satisfy your requirements?
>>
>> Igor,
>>
>> When I put in EXACTLY what you gave me:
>>
>> SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
>> WHERE case when instertedon > julianday(@time) then findImage(@path,
>> FolderId, ImageId) else 0 end;
>
> Note that I only gave you the WHERE clause. The idea was to drop it into your
> statement, leaving other clauses intact. What happens if you append LIMIT 1
> to the query above?
Igor,
Yes, I understand you gave me just the WHERE clause, but I still am
clueless as to how to use what you gave me with what I started with.
Here is what I know:
I started with (this is the real thing):
SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
WHERE instertedon > julianday(@time) AND findLargeImage(@path,
FolderId, ImageId) = 1
LIMIT 1
You gave me:
WHERE case when instertedon > julianday(@time)
then findLargeImage(@path, FolderId, ImageId)
else 0 end;
So I am assuming you were purposing I change out my WHERE clause for
yours, so it looks like this:
SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
WHERE case when instertedon > julianday(@time)
then findLargeImage(@path, FolderId, ImageId)
else 0 end;
When I ran this, I got the EXACT same results as running my original
query, it runs the complete result set through the extension function.
Looking at the statement, this seems logical: Find ALL the items in
V_FAVORITES_SELECTED, if the insertedon is greater then
julianday(@time), call the findLargeImage function, otherwise return
0.
As I stated before, my goal, in pseudo code is:
prepare "SELECT FolderId, ImageId, instertedon FROM
V_FAVORITES_SELECTED WHERE instertedon > julianday(@time)"
do(step) {
if( findLargeImage(@rootPath, FolderId, ImageId) == 1)
break;
}
What I did not try was appending your WHERE clause onto the end of my
complete statement, thus this:
SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
WHERE instertedon > julianday(@time) AND findLargeImage(@path,
FolderId, ImageId) = 1
LIMIT 1
WHERE case when instertedon > julianday(@time)
then findLargeImage(@path, FolderId, ImageId)
else 0 end;
But then I don't think that is valid SQL nor do I think this is what
you meant.
Igor, Personally would I would really appreciate is if you could give
me the complete SQL statement you intended for me to use:)
Sam
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
