CODEWITHSUNDEEP
sqloracle-database
JL_Coder
JL_Coder

Reputation: 164

How to shorten this SQL query?

is there any way to shorten this query like using case when for example?

     SELECT ords_id
FROM orders o
JOIN stocks s ON o.ords_stks_id = s.stks_id
WHERE (s.stks_print_btn_status LIKE '%901%'
       AND o.ords_stas_id = 90
       AND o.lsnr IS NOT NULL
       AND o.ords_id = orderid)
  OR (s.stks_print_btn_status LIKE '%1001%'
      AND o.ords_stas_id = 100
      AND o.lsnr IS NOT NULL
      AND o.ords_id = orderid)
  OR (s.stks_print_btn_status LIKE '%801%'
      AND o.ords_stas_id = 80
      AND o.lsnr IS NOT NULL
      AND o.ords_id = orderid)
  OR (s.stks_print_btn_status LIKE '%231%'
      AND o.ords_stas_id = 23
      AND o.lsnr IS NOT NULL
      AND o.ords_id = orderid)

Upvotes: 0

Views: 95

Answers (4)

Claudio Venturini
Claudio Venturini

Reputation: 698

At first you could avoid repeating o.lsnr is not null and o.ords_id = orderid for every condition.

SELECT ords_id
FROM orders o
JOIN stocks s ON o.ords_stks_id = s.stks_id
WHERE o.lsnr IS NOT NULL
  AND o.ords_id = orderid
  AND ( (s.stks_print_btn_status LIKE '%901%'
         AND o.ords_stas_id = 90)
       OR (s.stks_print_btn_status LIKE '%1001%'
           AND o.ords_stas_id = 100)
       OR (s.stks_print_btn_status LIKE '%801%'
           AND o.ords_stas_id = 80)
       OR (s.stks_print_btn_status LIKE '%231%'
           AND o.ords_stas_id = 23) )

Upvotes: 1

HomerPlata
HomerPlata

Reputation: 1787

Just removed some duplication:

SELECT ords_id
FROM orders o
JOIN stocks s ON o.ords_stks_id = s.stks_id
WHERE o.lsnr IS NOT NULL
  AND o.ords_id = orderid
  AND ((s.stks_print_btn_status LIKE '%901%'
        AND o.ords_stas_id = 90)
       OR (s.stks_print_btn_status LIKE '%1001%'
           AND o.ords_stas_id = 100)
       OR (s.stks_print_btn_status LIKE '%801%'
           AND o.ords_stas_id = 80)
       OR (s.stks_print_btn_status LIKE '%231%'
           AND o.ords_stas_id = 23));

Upvotes: 0

Christian Palmer
Christian Palmer

Reputation: 1302

This takes your query literally - I assume that s.STKS_PRINT_BTN_STATUS will contain the order ID with '1' appended...

select ords_id
 from orders o
     join stocks s on o.ords_stks_id = s.stks_id
 where 1=1
 and o.lsnr is not null
 and o.ords_id = orderid
 and s.STKS_PRINT_BTN_STATUS LIKE '%'||o.ords_stas_id||'1%'
 and o.ords_stas_id IN (90,100,80,23)
;

Upvotes: 2

Martin Venter
Martin Venter

Reputation: 231

What are you trying to achieve? Is this purely shortening the query? You could move the duplicated part of the WHERE out to something like this:

select ords_id
 from orders o join stocks s on o.ords_stks_id = s.stks_id
where 
(o.lsnr is not null and o.ords_id = orderid)
 AND 
(s.STKS_PRINT_BTN_STATUS like '%901%' and o.ords_stas_id = 90) or
(s.STKS_PRINT_BTN_STATUS like '%1001%' and o.ords_stas_id = 100) or
(s.STKS_PRINT_BTN_STATUS like '%801%' and o.ords_stas_id = 80) or
(s.STKS_PRINT_BTN_STATUS like '%231%' and o.ords_stas_id = 23);

While that removes some duplication, I expect you are actually trying to achieve something else?

Upvotes: 0

Related Questions