Toad World® Forums

Need to split two column by using (>) and (<=)

#1

i have two separate query like this, only different with this two query

on first query i am using nvl(cm.CONT_FINAL_AMT,0)>nvl(cm.CONT_PAID_AMT,0)

on second query i am using and nvl(cm.CONT_FINAL_AMT,0)<=nvl(cm.CONT_PAID_AMT,0)

i need a column like item_code model_name color_name >value <=value

give me some suggestion

select

im.ITEM_CODE, wm.MODEL_NAME_ARB,wc.COLOR_NAME_ENG, sum(cd.ITEM_PIECES), SUBSTR(wm.MODEL_NAME_ARB, 0, INSTR(wm.MODEL_NAME_ARB, ‘-’)-1), ww.current_balance
from
contract_details cd, contract_master cm , wh_model_master wm , wh_color_mast wc , wh_item_master im,COMP_BRANCH CB, wh_item_warehouses ww
where

cd.MODEL_NO =im.MODEL_NO
and im.item_code=ww.item_code
and cd.COLOR_CODE =im.COLOR_CODE
and cd.COLOR_CODE =wc.COLOR_CODE
and im.WOOD_CODE =im.WOOD_CODE
and cd.CONT_NO =cm.CONT_NO
and cd.MODEL_NO =wm.MODEL_NO
and cm.BRANCH_CODE=cb.BRANCH_CODE
and cd.MODEL_NO <>‘BDB0026’
and substr(wm.MODEL_NO,-4) =:m
and cd.ITEM_PIECES>NVL(cd.ITEM_DELV_PIECES,0)
and nvl(cm.CONT_FINAL_AMT,0)>nvl(cm.CONT_PAID_AMT,0)
and ww.wh_code = ‘WH50’
and ww.wh_code=NVL(:wh_CODE, ww.WH_CODE)
and cm.CONT_STATUS not in(‘DW’,‘DL’,‘CN’,‘SP’)
and cm.branch_code not in (‘11’,‘13’,‘15’,‘16’,‘17’,‘18’,‘20’,‘21’,‘22’,‘23’,‘24’)
group by im.ITEM_CODE,wm.MODEL_NAME_ARB,wc.COLOR_NAME_ENG, ww.current_balance
ORDER BY wc.COLOR_NAME_ENG

select

im.ITEM_CODE, wm.MODEL_NAME_ARB,wc.COLOR_NAME_ENG, sum(cd.ITEM_PIECES), SUBSTR(wm.MODEL_NAME_ARB, 0, INSTR(wm.MODEL_NAME_ARB, ‘-’)-1), ww.current_balance
from
contract_details cd, contract_master cm , wh_model_master wm , wh_color_mast wc , wh_item_master im,COMP_BRANCH CB, wh_item_warehouses ww
where

cd.MODEL_NO =im.MODEL_NO
and im.item_code=ww.item_code
and cd.COLOR_CODE =im.COLOR_CODE
and cd.COLOR_CODE =wc.COLOR_CODE
and im.WOOD_CODE =im.WOOD_CODE
and cd.CONT_NO =cm.CONT_NO
and cd.MODEL_NO =wm.MODEL_NO
and cm.BRANCH_CODE=cb.BRANCH_CODE
and cd.MODEL_NO <>‘BDB0026’
and substr(wm.MODEL_NO,-4) =:m
and cd.ITEM_PIECES>NVL(cd.ITEM_DELV_PIECES,0)
and nvl(cm.CONT_FINAL_AMT,0)<=nvl(cm.CONT_PAID_AMT,0)
and ww.wh_code = ‘WH50’
and ww.wh_code=NVL(:wh_CODE, ww.WH_CODE)
and cm.CONT_STATUS not in(‘DW’,‘DL’,‘CN’,‘SP’)
and cm.branch_code not in (‘11’,‘13’,‘15’,‘16’,‘17’,‘18’,‘20’,‘21’,‘22’,‘23’,‘24’)
group by im.ITEM_CODE,wm.MODEL_NAME_ARB,wc.COLOR_NAME_ENG, ww.current_balance
ORDER BY wc.COLOR_NAME_ENG

#2

I used CASE to split the values in the SELECT and added the amt fields to the GROUP BY. If you need to split on the SUM of the amt fields, remove them from the GROUP BY and add SUM() to both CASE statements. Similarly, if the current_balance field is not the value you want to display, you can change that too.

SELECT im.item_code,

wm.model_name_arb,

wc.color_name_eng color_name,

SUM(cd.item_pieces) item_pieces,

SUBSTR(wm.model_name_arb, 0, INSTR(wm.model_name_arb, ‘-’)-1) model_name,

ww.current_balance,

CASE WHEN NVL(cm.cont_final_amt,0) > NVL(cm.cont_paid_amt,0) THEN ww.current_balance

ELSE 0

END bal_lt_value,

CASE WHEN NVL(cm.cont_final_amt,0) >= NVL(cm.cont_paid_amt,0) THEN ww.current_balance

ELSE 0

END bal_ge_value

FROM contract_details cd,

contract_master cm ,

wh_model_master wm ,

wh_color_mast wc ,

wh_item_master im,

comp_branch cb,

wh_item_warehouses ww

WHERE cd.model_no = im.model_no

AND im.item_code = ww.item_code

AND cd.color_code = im.color_code

AND cd.color_code = wc.color_code

AND im.wood_code = im.wood_code

AND cd.cont_no = cm.cont_no

AND cd.model_no = wm.model_no

AND cm.branch_code = cb.branch_code

AND cd.model_no <> ‘BDB0026’

AND SUBSTR(wm.model_no,-4) = :m

AND cd.item_pieces > NVL(cd.item_delv_pieces,0)

AND ww.wh_code = ‘WH50’

AND ww.wh_code = NVL(:wh_code, ww.wh_code)

AND cm.cont_status NOT IN(‘DW’,‘DL’,‘CN’,‘SP’)

AND cm.branch_code NOT IN (‘11’,‘13’,‘15’,‘16’,‘17’,‘18’,‘20’,‘21’,‘22’,‘23’,‘24’)

GROUP BY im.item_code, wm.model_name_arb, wc.color_name_eng, ww.current_balance,

cm.cont_final_amt, cm.cont_paid_amt

ORDER BY wc.color_name_eng