Getting Records with Different Criteria

Question

I have an oracle query that i am using to collect records that have a buyer type code of VTEST but i also need to populate records in the same query that have code matching ADULT but onlyt matching a particular sales channel.

the listed tables dont have a sales channel link but the transaction table does have sales channeland it can join to the event_seat table by the transaction_id field

so basically i want to pull records that match VTEST and ADULT but where as the adult ones can ONLY match the sales channel id of 6

any help is greatly appreciated

SELECT
    e.event_date,
    e.venue_id,
    e.description     AS event,
    t.price,
    t.ticket_id,
    bt.buyer_type_code,
    bt.description    AS buyer_type,
    btg.description   AS ticket_category,
    SUM(sci.actual_amount) AS tax,
    t.price + SUM(sci.actual_amount) AS revenue,
    e.event_id,
    coupon.coupon_code
FROM
    event e
    INNER JOIN event_seat es ON e.event_id = es.event_id
    INNER JOIN ticket t ON es.ticket_id = t.ticket_id
    LEFT JOIN service_charge_item sci ON sci.ticket_id = t.ticket_id
    INNER JOIN buyer_type bt ON t.buyer_type_id = bt.buyer_type_id
    INNER JOIN buyer_type_group btg ON bt.buyer_type_group_id = btg.buyer_type_group_id
    LEFT JOIN coupon ON t.coupon_id = coupon.coupon_id
WHERE
    e.event_date > '1-JAN-2022'
    AND e.description LIKE '%Testshow%'
    AND e.description NOT LIKE '%Join%'
    AND e.description NOT LIKE '%Left%'
    AND e.event_status_code = 'SAL'
GROUP BY
    e.event_date,
    e.venue_id,
    e.description,
    t.price,
    t.ticket_id,
    bt.buyer_type_code,
    bt.description,
    btg.description,
    e.event_id,
    coupon.coupon_code
HAVING
    bt.buyer_type_code LIKE 'VTEST'

Answer 1

Considering you haven't done the joins and it's through 2 tables, I will leave this part to you, but you can implement the logic you describe by removing the HAVING clause and add in the where:

AND bt.buyer_type_code IN ('VTEST','ADULT')
AND 6 = CASE WHEN buyer_type_code = 'ADULT' THEN sales_tbl.channel_id --or whatever it's name is
             ELSE 6
        END

Answer 2

It's because of how you write your condition.

if you're going to login page while unauthenticated the flow goes like:

• 'login' !== 'signup' (true)
• await checkAuth() (false)
• execute next({ name: 'login' })

Maybe you meant:

const pagesForAuthOnly = []
const pagesForGuestsOnly = ['login', 'signup']
const authenticated = await checkAuth()

if (pagesForAuthOnly.includes(to.name)) {
  if (authenticated) {
    next()
  } else {
    next({ name: 'login' })
  }
} else if (pagesForGuestsOnly.includes(to.name)) {
  if (authenticated) {
    next({ name: 'home' })
  } else {
    next()
  }
} else {
  next()
}