How to add specific if condition in sql

Question

I have built this query (snapshot attached) to check if there is any staleness in prices by comparing today's price with previous day price for certain timezones, what i need is it should only throw only that object_id which shows '0' as BP_move for the mentioned timezones. for eg in the snapshot we can see that AED fx price is 0 for across all the timezones so it is acceptable but other ccy like EUR, GHS ,KES ,NZD and QAR are stale only for one or two timezones so those should not get published in output. is there any if condition i can use in this query ?

select distinct
    vyc.asof, vyc.timezone, vyc.object_id, i.name, 
    vpf.rate * 100 todays_rate, vpf2.rate * 100 prior_rate, 
    (vpf.rate - vpf2.rate) * 1000 bp_move 
from
    val_yield_curves vyc
inner join 
    val_prices_fx vpf on vyc.asof = vpf.asof 
                      and vyc.rowkey = vpf.curve
inner join 
    val_yield_curves vyc2 on vyc.timezone = vyc2.timezone 
                          and vyc2.asof = case when to_char(sysdate,'DY') = 'MON' then trunc(sysdate-3) else trunc(sysdate-1) end 
                          and vyc.object_id = vyc2.object_id
inner join 
    val_prices_fx vpf2 on vyc2.asof = vpf2.asof 
                       and vyc2.rowkey = vpf2.curve 
                       and vpf.instrument = vpf2.instrument
inner join 
    instruments i on i.pkey = vpf.instrument
where 
    vyc.object_id like '%Spot%' 
    and vyc.timezone in ('L0200', 'L1000', 'L1200', 'L1500')
    and (vpf.rate - vpf2.rate) * 1000 in '0'
    and vyc.asof = trunc(sysdate)
order by 
    vyc.object_id

Join with a subquery that returns the object IDs that fit your criteria. — Barmar
– Barmar, Commented Jul 13, 2021 at 14:23

Shirosamus · Accepted Answer · 2021-07-13 14:30:12Z

1

I don't know if I understood correctly your problem but...

If you don't need timezone information, you can

GROUP BY object_id, name, rates
HAVING COUNT(DISTINCT Timezone) >= 4

If you need timezone information, you can add a "OBJECT_ID IN (SELECT...)" condition in your WHERE. You have to make it check if you have the right number of lines for your OBJECT_ID. You can GROUP BY/HAVING COUNT this subrequest as above and returns OBJECT_ID.

answered Jul 13, 2021 at 14:30

Shirosamus

1266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Naina Over a year ago

can you pls share an example ?

Naina Over a year ago

i only need those object_ids which have 0 value against all the input timezone ('L0200', 'L1000','L1200','L1500)'

Collectives™ on Stack Overflow

How to add specific if condition in sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related