When I am running my query on Oracle I receive this result on same column :
text1 - text2 - text3 - text4
( there are many columns )
I should have count from each text
thanks for your help
query:
select ca.title
from table_case ca,
table_contract x, table_site_part sp, mtm_site_part24_contract5 mtm,
table_queue q,
table_user u_og,
table_employee empl,
table_site office,
table_site s,
table_address addr,
table_contact_role co_r,
table_contact co,
table_gbst_elm gb,
table_x_tt_ticket tt,
table_case vc
where ca.case_originator2user = u_og.objid
and x.objid=mtm.contract2dir_sitepart
and ca.case_currq2queue = q.objid
and ca.CASESTS2GBST_ELM=gb.objid
and u_og.objid = empl.employee2user
and empl.SUPP_PERSON_OFF2SITE = office.objid
and ca.case_reporter2site = s.objid
and s.cust_primaddr2address = addr.objid
and s.objid = co_r.contact_role2site
and co_r.contact_role2contact = co.objid
and sp.objid=mtm.dir_sitepart2contract
and sp.all_site_part2site=s.objid
and co_r.s_role_name = 'DEFAULT'
and q.s_title = upper('OP_AD_WIN_TECH')
and ca.objid = tt.X_TT_TICKET2CASE (+)
and tt.X_HANDLING_UNIT IS NULL
and ca.CASE_VICTIM2CASE=vc.OBJID(+)
and x.type not in ('UMTS','GSM','???/???','.','Time and Materials')
result:
63CAGDIRO - VoIP/VDSL - No Service - All
63CAGDIRO - VoIP/VDSL - No Service - All
79WIIHAHG - VDSL - Quality Internet - Internet
79WIIHAHG - VDSL - Quality Internet - Internet
71GROGRO - VDSL - tv box error code general arg fail - TV
71GROGRO - VDSL - tv box error code general arg fail - TV
73LATLWHS - VDSL - No Service All - All services
73LATLWHS - VDSL - No Service All - All services
If you are looking to 'convert' one text in multiple columns you could do something like :
with
text as (
select '63CAGDIRO - VoIP/VDSL - No Service - All' as txt from dual union all
select '63CAGDIRO - VoIP/VDSL - No Service - All' from dual union all
select '79WIIHAHG - VDSL - Quality Internet - Internet' from dual union all
select '79WIIHAHG - VDSL - Quality Internet - Internet' from dual union all
select '71GROGRO - VDSL - tv box error code general arg fail - TV' from dual union all
select '71GROGRO - VDSL - tv box error code general arg fail - TV' from dual union all
select '73LATLWHS - VDSL - No Service All - All services' from dual union all
select '73LATLWHS - VDSL - No Service All - All services ' from dual),
pos as (
select txt, instr(txt,'-',1,1) as p1, instr(txt,'-',1,2) as p2,
instr(txt,'-',1,3) as p3 from text
)
select
substr(t.txt, 1, p1 -1 ) col_name_1,
substr(t.txt, p1 + 1, p2 - p1 - 2 ) col_name_2,
substr(t.txt, p2 + 1, p3 - p2 - 2 ) col_name_3,
substr(t.txt, p3 + 1, length(t.txt) ) col_name_4
from text t , pos p
where t.txt = p.txt;
Result will be:
COL_NAME_1 COL_NAME_2 COL_NAME_3 COL_NAME_4
63CAGDIRO VoIP/VDSL No Service All
63CAGDIRO VoIP/VDSL No Service All
63CAGDIRO VoIP/VDSL No Service All
63CAGDIRO VoIP/VDSL No Service All
79WIIHAHG VDSL Quality Internet Internet
79WIIHAHG VDSL Quality Internet Internet
79WIIHAHG VDSL Quality Internet Internet
79WIIHAHG VDSL Quality Internet Internet
71GROGRO VDSL tv box error code general arg fail TV
71GROGRO VDSL tv box error code general arg fail TV
71GROGRO VDSL tv box error code general arg fail TV
71GROGRO VDSL tv box error code general arg fail TV
73LATLWHS VDSL No Service All All services
73LATLWHS VDSL No Service All All services
I notice a pattern, it seems to me that the column are delimited by '-'.
I have Found the solution:
select
CASE
WHEN ca.title LIKE '%Service%' THEN 'Service'
WHEN ca.title LIKE '%TV%' THEN 'NO TV'
WHEN ca.title LIKE '%Quality%' THEN 'Quality'
WHEN ca.title LIKE '%bad%' THEN 'Bad Quality'
END as Services,
COUNT(*) AS group_by_count
from ...
GROUP BY
CASE
WHEN ca.title LIKE '%Service%' THEN 'Service'
WHEN ca.title LIKE '%TV%' THEN 'NO TV'
WHEN ca.title LIKE '%Quality%' THEN 'Quality'
WHEN ca.title LIKE '%bad%' THEN 'Bad Quality'
END