On Tuesday, February 5, 2013 12:29:28 PM UTC-8, Charles Monteiro wrote:
>
> I've been battling to try to chain something together but I"m failing, I
> have the following snippet of code from a stored proc function that I'm
> porting:
>
> Delete from table1 a
> where a.status <> 'O'
> or exists (select b.contract_nbr
> from contract b
> where b.contract_date = a.contract_date
> and b.contract_nbr = a.contract_nbr
> and b.contract_sfx = a.contract_sfx
> and ((b.status <> 'O') or
> (b.orig_par = b.tba_Par_Allocated + b.tba_Par_Paired_Off +
> b.tba_Par_Given_Up))
> );
>
> not sure at all how to chain up an "or exists"
>
> the where clause seems pretty straight ahead until the last and criteria
> which is an or expression between a not equals clause and a second clause
> which requires the summation of numeric fields who sum is to be compared to
> a field in the second table.
>
> I get that I'm going to need to setup a join i.e. via Sequel since I don't
> think that Sequel can specify it the same way the stored proc does.
>
> Help is greatly appreciated with the above code. Also in general I guess
> I'm failing how to construct nested boolean queries such as demonstrated
> above.
>
EXISTS is handled by using a separate dataset for the subselect, and
calling #exists on it. OR can be handled either via Dataset#or or the #|
method on Sequel expression objects, depending on what type of operation
you want to do. Here's an example:
DB[:table1___a].
exclude(:a__status=>'O').
or(DB[:contract___b].
select(:b__contract_nbr).
where{[contract_date, contract_nbr, contract_sfx].map{|x|
[x.qualify(:b), x.qualify(:a)]}}.
where{Sequel.~(:b_status=>'O') |
{:b__orig_par=>b__tba_Par_Allocated+:b__tba_Par_Paired_Off+:b__tba_Par_Given_Up}}.
exists).
delete
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
