Steve Ellenoff wrote:
> I'm curious what the best approach for this scenario is when working
> with a non-vfp backend.
>
> User has a custom query based form where they can enter numerous
> different criteria to select which data records to include in a
> report they will run. When they click proceed the app pulls down the
> data into a local VFP cursor and displays the returned results in a
> grid with checkboxes to allow the user to *further refine* the list
> of records to include in the report to run. Once the user clicks
> proceed, only the checked records are included in the final report output.
>
> The current VFP approach is to do the initial select * for all the
> initial records wanted, and then do a set filter on the checked records.
>
> How do I achieve this same thing for an SQL backend? Obviously the
> first part is the same, but how do I tell the backend about the
> checked records? I ask because there could be well over 3000 included
> records (for year end reporting and such). I cannot envision me doing
> a WHERE ID = 1 OR ID = 2 .... OR ID = 3000 for example..
>
> I was thinking I could leave the "checked" filtering logic in VFP as
> is, and just change the initial SELECT * to hit the SQL backend, but
> I was wondering what the best practice is here, as this seems
> wasteful if the user only ends up selecting a few records to run on
> the report, and pulling SELECT * from a SQL backend is never a best
> practice if it can be avoided.
>
> Another idea I had was to write to a temp table on the backend the
> records the user selected and then select from the temp table and
> original table to get the final result. What I don't like about this
> idea is having to send possibly 3000 insert statements if the user
> wants to include 3000 records in the report. That sounds like it'd be
> terribly slow.
>
> How have you guys handled this situation in the past?
>
> Thanks-
> Steve
>
You can use :
"""
select *
from TheTable
where TheTable.Id in (1, 2, 3, ...., 3000)
"""
Much easier (and shorter) than using inserts. You just scan your local
table for selected records and add their Ids to your sqlstring separated
by commas.
Or you might use :
"""
select *
from TheTable
inner join (
select 1 as Id
union select 2
union select 3
...
..
.
union select 3000
) spoof
on spoof.Id = TheTable.Id
"""
In this simple case I'd use the first approach, but there are cases when
what you want to express is more complex and the "spoof" table comes
handy (you can do the same with Inserts but with this approach it is
only ONE sql instruction).
Both ways have worked for me.
HTH
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
