Thanks alot for the code. I'll see if I can get something generic out
if it. Perhaps using the data dictionary will help.
I'd like to be able use something like :
DB[:mytable].asoftoday
or DB[:mytable].asof('2009-12-13')
The effective dates are always key values so it should be easy to pick
out the keys ahead of these values and use them in the where clause.
Thanks again.
On Mar 4, 2:02 pm, Jeremy Evans <[email protected]> wrote:
> On Mar 4, 11:15 am, kdf <[email protected]> wrote:
>
> > Thanks Jeremy, that works.
>
> > I had found a similar example but I was hoping for a simpler
> > approach. The majority of my legacy tables have composite keys and
> > effective dating so this adds a lot of potential for error in my code.
>
> > How hard would it be to write a plugin to generate this code
> > automatically ?
>
> Depends how similar the different cases are. If the columns have the
> same name in all tables, it shouldn't be difficult:
>
> class Sequel::Dataset
> # With fieldname and fieldvalue
> def wfnfv(n, v)
> s = first_source_alias
> filter(:fieldname.qualify(s)=>n,
> :fieldvalue.qualify(s)=>v)
> end
> # Latest by effective date
> def lbed
> s = first_source
> a = unused_table_alias(s)
> filter(:effdt.qualify(s)=>DB[s.as(a)].
> select{max(:effdt.qualify(a))}.
> filter(:fieldname.qualify(s)=>:fieldname.qualify(a),
> :fieldvalue.qualify(s)=>:fieldvalue.qualify(a)))
> end
> end
> DB[:mytable].wfnfv('X', 'Y').lbed
>
> SELECT *
> FROM mytable
> WHERE (((mytable.fieldname = 'X')
> AND (mytable.fieldvalue = 'Y'))
> AND (mytable.effdt IN (
> SELECT max(mytable_0.effdt)
> FROM mytable AS mytable_0
> WHERE ((mytable.fieldvalue = mytable_0.fieldvalue)
> AND (mytable.fieldname = mytable_0.fieldname)))))
>
> Obviously if the columns have different names in all the tables,
> you'll have more method arguments. Note that the code above requires
> 3.9.0, which will be released today.
>
> Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
