I have this query:

SELECT
PALLET_CONFIG, QTY_ON_HAND, ZONE_1,QTY_ON_HAND / SUBSTR(CONFIG_ID, INSTR(CONFIG_ID, 'E') 1, INSTR(CONFIG_ID, 'B') - INSTR(CONFIG_ID, 'E')-1) AS substring 
FROM v_inventory 
WHERE ZONE_1 <> 'SUSPENSE' AND PALLET_CONFIG = 'USPAL' 
FETCH FIRST 15 ROWS ONLY

enter image description here

I am trying to figure out a way to return values if:

The result of the substring is equal or higher than the QTY_ON_HAND, then I need that the division to happen. On the other hand, if the result of the substring is lower than the QTY_ON_HAND, then I just need it to return '1'.

CodePudding user response：

If query you wrote returns values you want, then one option is to use it as a CTE (a subquery) and - with a CASE expression - decide what to do (regarding qty_on_hand vs. substring relation):

with temp as
  (select
        pallet_config, 
        qty_on_hand, 
        zone_1,
        to_number(substr(config_id, 
                         instr(config_id, 'E') 1, 
                         instr(config_id, 'B') - instr(config_id, 'E')-1
                        )) as substring 
        from v_inventory 
        where zone_1 <> 'SUSPENSE' and pallet_config = 'USPAL' 
        fetch first 15 rows only
  )
select pallet_config,
       zone_1,
       qty_on_hand,
       substring,
       --
       case when substring >= qty_on_hand then qty_on_hand / substring
            else 1
       end result
from temp;

CodePudding user response：

Use LEAST to limit the value to be a maximum of 1:

SELECT PALLET_CONFIG,
       QTY_ON_HAND,
       ZONE_1,
       LEAST(
         QTY_ON_HAND
         / SUBSTR(CONFIG_ID, INSTR(CONFIG_ID, 'E') 1, INSTR(CONFIG_ID, 'B') - INSTR(CONFIG_ID, 'E')-1),
         1
       ) AS substring 
FROM   v_inventory 
WHERE  ZONE_1 <> 'SUSPENSE'
AND    PALLET_CONFIG = 'USPAL' 
FETCH FIRST 15 ROWS ONLY