how to get this data in a one line of output [message #280843] |
Thu, 15 November 2007 00:37  |
slipzst
Messages: 7 Registered: November 2007 Location: KELANA JAYA
|
Junior Member |

|
|
the coding is like this
set head off;
set pagesize 0;
set linesize 200;
set feedback off;
set termout off;
set pause off;
spool /bistari/dw/sds2/rosrita/fahmi/ogbs01.txt;
select distinct
ptt.marketing_ptt_num||'|'||
exchange.exchange_ident_name||'|'||
exchange.exchange_ident_code||'|'||
tel_profile.service_num||'|'||
customer_profile.customer_name,
tel_profile.apartment_num||'|'||
tel_profile.lot_num||'|'||
tel_profile.house_num||'|'||
tel_profile.floor_level_code||'|'||
tel_profile.building_name||'|'||
tel_profile.street_num_code||'|'||
tel_profile.street_type||'|'||
tel_profile.street_name||'|'||
tel_profile.postal_code||'|'||
tel_profile.section_name||'|'||
tel_profile.city_name||'|'||
tel_profile.state_code||'|'||
tel_profile.serv_class_code||'|'||
customer_profile.customer_segment_code||'|'
from
temp_ogbs_fahmi,
tel_profile,ptt,exchange,customer_accounts,customer_profile
where
temp_ogbs_fahmi.service_num = tel_profile.service_num and
temp_ogbs_fahmi.connect_date = tel_profile.connect_date and
tel_profile.ptt_num = ptt.network_ptt_num(+) and
tel_profile.exchange_ident_code = exchange.exchange_ident_code(+) and
tel_profile.account_num = customer_accounts.account_num(+) and
customer_accounts.customer_id = customer_profile.customer_id(+);
set head off;
set pagesize 0;
set linesize 200;
set feedback off;
set termout off;
set pause off;
spool /bistari/dw/sds2/rosrita/fahmi/ogbz02.txt;
select distinct
ptt.marketing_ptt_num||'|'||
exchange.exchange_ident_name||'|'||
exchange.exchange_ident_code||'|'||
tel_profile.service_num||'|'||
customer_profile.customer_name||'|'||
tel_profile.apartment_num||'|'||
tel_profile.lot_num||'|'||
tel_profile.house_num||'|'||
tel_profile.floor_level_code||'|'||
tel_profile.building_name||'|'||
tel_profile.street_num_code||'|'||
tel_profile.street_type||'|'||
tel_profile.street_name||'|'||
tel_profile.postal_code||'|'||
tel_profile.section_name||'|'||
tel_profile.city_name||'|'||
tel_profile.state_code||'|'||
tel_profile.serv_class_code||'|'||
customer_profile.customer_segment_code||'|'
from
temp_ogbz_fahmi,
tel_profile,ptt,exchange,customer_accounts,customer_profile
where
temp_ogbz_fahmi.service_num = tel_profile.service_num and
temp_ogbz_fahmi.connect_date = tel_profile.connect_date and
tel_profile.ptt_num = ptt.network_ptt_num(+) and
tel_profile.exchange_ident_code = exchange.exchange_ident_code(+) and
tel_profile.account_num = customer_accounts.account_num(+) and
customer_accounts.customer_id = customer_profile.customer_id(+);
exit;
|
|
|
|
|
|
|
|
|
Re: how to get this data in a one line of output [message #280861 is a reply to message #280859] |
Thu, 15 November 2007 01:08   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I think I *know* what's wrong - columns should be FORMATTED properly, using one of functions Oracle provided to do that. For example, instead of thisSQL> SELECT 123.00 ||' '|| ADD_MONTHS(SYSDATE, -4) FROM dual
2 UNION ALL
3 SELECT 23.00 ||' '|| ADD_MONTHS(SYSDATE, -1) FROM dual;
123.00||''||
------------
123 15.07.07
23 15.10.07 he'd like to have thisSQL> SELECT TO_CHAR(123.00, '9990.00') ||' '||
2 TO_CHAR(ADD_MONTHS(SYSDATE, -4), 'dd.mm.yyyy')
3 FROM dual
4 UNION ALL
5 SELECT TO_CHAR(23.00, '9990.00') ||' '||
6 TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'dd.mm.yyyy')
7 FROM dual;
TO_CHAR(123.00,'999
-------------------
123.00 15.07.2007
23.00 15.10.2007
SQL>
|
|
|
Re: how to get this data in a one line of output [message #280867 is a reply to message #280861] |
Thu, 15 November 2007 01:16   |
slipzst
Messages: 7 Registered: November 2007 Location: KELANA JAYA
|
Junior Member |

|
|
the output that went out like this..not in a line
JRS|BENUT RSU|BUTDR|00706902446|GITN SDN BHD||270|||||JLN|PARIT ISMAIL|82200||BENUT|JH|P|C40|
JRS|BENUT RSU|BUTDR|00706902449|GITN SDN BHD|||||SEK MEN RENDAH BENUT|JEA6005|JLN|PARIT
should be like this in one line..how is it
JRS|BENUT RSU|BUTDR|00706902446|GITN SDN BHD|270|JLN|PARIT SMAIL|82200||BENUT|JH|P|C40|
JRS|BENUT RSU|BUTDR|00706902449|GITN SDN BHD|SEK MEN RENDAH BENUT|JEA6005|JLN|PARIT
[EDITED by LF: added [code] tags]
[Updated on: Thu, 15 November 2007 01:32] by Moderator Report message to a moderator
|
|
|
Re: how to get this data in a one line of output [message #280873 is a reply to message #280861] |
Thu, 15 November 2007 01:25   |
slipzst
Messages: 7 Registered: November 2007 Location: KELANA JAYA
|
Junior Member |

|
|
sholuld be like this
JRS|AIR TAWAR LIMA RLU|ATLDR|00708953016|ABDUL HAMID BIN HAJI ARSHAD |6 KEDAI BATU 81900 FELDA AIR TAWAR 5 KOTA TINGGI JH |P |S10|
JRS|AIR TAWAR LIMA RLU|ATLDR|00708953555|AIDIL BIN MAHADI |21A KPG KG JOHOR LAMA|81940TELOK SENGAT KOTA TINGGI JH |K |R10|
JRS|AIR TAWAR LIMA RLU|ATLDR|00708954177|MOHAMAD ALI HANAFIAH BIN MOHD. IDROS |431 81900 FELDA AIR TAWAR 5 KOTA TINGGI JH |K |R10|
[EDITED by LF: added [code] tags]
[Updated on: Thu, 15 November 2007 01:29] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|