select
STOCKID,
MATUNITID,
DESCRIPTION,
CODE,
UNIT,
INSTOCK,
COSTPRICE,
INSTOCK * COSTPRICE as STOCKVALUE
from 
    (
    select
    S.STOCKID,
    M.MATUNITID,
    M.DESCRIPTION,
    M.CODE,
    U.DESCRIPTION as UNIT,
    
    iif(S.QTYINSTOCK = 0, NULL, S.QTYINSTOCK) as INSTOCK,

    (select 
      max(MC.COSTPRICE / MC.PRICEQTY)
      from MATCOST MC 
      where MC.MATUNITID = S.MATUNITID
      and MC.UNITID = S.UNITID 
      and MC.STATUSID = 1) as COSTPRICE

    from STOCK S
    join MATUNIT M on S.MATUNITID = M.MATUNITID
    left join UNITS U on S.UNITID = U.UNITID
    )
order by 2,3,4

select
maxvalue(QTYOUT_STRAIGHT, QTYOUT_ROTATED) as QTYOUT,
iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, QTYDOWN_ROTATE, QTYDOWN_STRAIGHT) as QTYDOWN,
iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, QTYACCROSS_ROTATE, QTYACCROSS_STRAIGHT) as QTYACCROSS,
iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, 'Yes', 'No') as ROTATED,
(AREA_PARENT-(maxvalue(QTYOUT_STRAIGHT, QTYOUT_ROTATED)*AREA_CHILD))/AREA_PARENT*100 as WASTE
from
    (
    select 
      floor((DEPTH_PARENT / DEPTH_CHILD)) * floor((WIDTH_PARENT / WIDTH_CHILD)) as QTYOUT_STRAIGHT,
      floor((DEPTH_PARENT / WIDTH_CHILD)) * floor((WIDTH_PARENT / DEPTH_CHILD)) as QTYOUT_ROTATED,
      
      floor((DEPTH_PARENT / DEPTH_CHILD)) as QTYDOWN_STRAIGHT,
      floor((WIDTH_PARENT / WIDTH_CHILD)) as QTYACCROSS_STRAIGHT,
      floor((DEPTH_PARENT / WIDTH_CHILD)) as QTYDOWN_ROTATE,
      floor((WIDTH_PARENT / DEPTH_CHILD)) as QTYACCROSS_ROTATE,
      
      DEPTH_PARENT * WIDTH_PARENT as AREA_PARENT,
      DEPTH_CHILD * WIDTH_CHILD as AREA_CHILD
    from 
        (
        select 

        cast(1200 as float) as DEPTH_PARENT,
        cast(2400 as float) as WIDTH_PARENT,

        cast(600 as float) as DEPTH_CHILD,
        cast(900 as float) as WIDTH_CHILD

        from RDB$DATABASE
        )
    )

select
iif(WASTE_ROTATE < WASTE_STRAIGHT, QTYACCROSS_ROTATE, QTYACCROSS_STRAIGHT) as QTYACCROSS,
iif(WASTE_ROTATE < WASTE_STRAIGHT, 'Yes', 'No') as ROTATED,
minvalue(WASTE_ROTATE, WASTE_STRAIGHT) as WASTE
from
    (
    select 
    QTYACCROSS_STRAIGHT,
    QTYACCROSS_ROTATE,
    ((WIDTH_REEL - (QTYACCROSS_STRAIGHT * WIDTH_CHILD))/WIDTH_REEL * 100) as WASTE_STRAIGHT,
    ((WIDTH_REEL - (QTYACCROSS_ROTATE * DEPTH_CHILD))/WIDTH_REEL * 100) as WASTE_ROTATE
    from
        (
        select 
        WIDTH_REEL,
        DEPTH_CHILD,
        WIDTH_CHILD,        
        floor((WIDTH_REEL / WIDTH_CHILD)) as QTYACCROSS_STRAIGHT,
        floor((WIDTH_REEL / DEPTH_CHILD)) as QTYACCROSS_ROTATE
        from 
            (
            select 

            cast(2000 as float) as WIDTH_REEL,
            cast(600 as float) as DEPTH_CHILD,
            cast(1200 as float) as WIDTH_CHILD

            from RDB$DATABASE
            )
        )
    )

select
USERNAME,

case 
  when ((ACCESS is null) or (ACCESS = 0)) then 'Access Denied'
  when ACCESS = 1 then 'View - User only'
  when ACCESS = 2 then 'View - Rep only'
  when ACCESS = 3 then 'View - All'
  when ACCESS = 4 then 'Edit - User only'
  when ACCESS = 5 then 'Edit - Rep only'
  when ACCESS = 6 then 'Edit - All'
  when ACCESS = 7 then 'Edit & Delete'
  when ACCESS = 8 then 'Full Access'
  end as ACCESS
  
from 
    (
    select 
    S.SHORTNAME as USERNAME,

    (select 
    max(A.ACCESSID)
    from MODULESSETTINGS A
    where A.ENTRYTYPEID = 1 --ENTRYTYPEID
    and ((A.USERID = S.STAFFID)
      or (A.ROLEID in 
      (select R.ROLEID
      from MODULESUSERROLES R 
      where R.USERID = S.STAFFID)))) as ACCESS

    from CPYSTAFF S 
    )

select

ERROR1 
||trim(leading ' ' from iif(((ERROR1 > '') and (ERROR2 > '')), ', ', ''))
||ERROR2
||trim(leading ' ' from iif(((ERROR1||ERROR2 > '') and (ERROR3 > '')), ', ', ''))
||ERROR3
||trim(leading ' ' from iif(((ERROR1||ERROR2||ERROR3 > '') and (ERROR4 > '')), ', ', ''))
||ERROR4
    
FROM
    (
    select 
    --'' as ERROR1,
    'This is Error 1' as ERROR1,
    '' as ERROR2,
    --'This is Error 2' as ERROR2,
    --'' as ERROR3,
    'This is Error 3' as ERROR3,
    --'' as ERROR4
    'This is Error 4' as ERROR4
    from RDB$DATABASE
    )