Toad World® Forums

Split a column into two data column

I’m looking to take a query result like, i have a column in table (amount) but i would like to split the column like greater than 1000 should be in A column & less than 1000 should be in B column.

is this posible,

Using a Trigger.

give me some examble to do

i have query like this

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