On Mar 4, 7:22 am, kdf <[email protected]> wrote:
> Hi,
>
> I just found Sequel and have been using it for some really simple
> scripts. So far it's been fantastic to quickly produce some reports.
>
> I'm having issues doing more advanced where clauses though and haven't
> been able to find any examples to help with composite primary keys
> containing an effective date.
>
> Can anyone provide insight ?
>
> My table key structure looks like (Oracle 10g):
>
> FIELDNAME VARCHAR2(20)
> FIELDVALUE VARCHAR2(5)
> EFFDT DATE
>
> I'd like to be able to :
>
> SELECT *
> FROM MYTABLE MT1
> WHERE
> MT1.FIELDNAME = 'X'
> AND MT1.FIELDVALUE = 'Y'
> AND MT1.EFFDT =
> (SELECT MAX(MT2.EFFDT)
> FROM MYTABLE MT2
> WHERE
> MT1.FIELDNAME = MT2.FIELDNAME
> AND MT1.FIELDVALUE = MT2.FIELDVALUE)
A direct translation to Sequel ruby code would be:
DB[:mytable___mt1].filter(:mt1__fieldname=>'X',
:mt1__fieldvalue=>'Y',
:mt1__effdt=>DB[:mytable___mt2].
select{max(mt2__effdt)}.
filter(:mt1__fieldname=>:mt2__fieldname,
:mt1__fieldvalue=>:mt2__fieldvalue))
There's probably other ways to do the query (e.g. using a join instead
of a correlated subquery), but I'd recommending using this way first
and only switching if you have issues with it.
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.
