Home » SQL & PL/SQL » SQL & PL/SQL » how to get this data in a one line of output
icon4.gif  how to get this data in a one line of output [message #280843] Thu, 15 November 2007 00:37 Go to next message
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 #280849 is a reply to message #280843] Thu, 15 November 2007 00:47 Go to previous messageGo to next message
slipzst
Messages: 7
Registered: November 2007
Location: KELANA JAYA
Junior Member

its seems dont nobody knows right how to make this job done
Re: how to get this data in a one line of output [message #280850 is a reply to message #280843] Thu, 15 November 2007 00:47 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And is there a problem with it? What does it do? What do you wnat it to do?

Ross Leishman
Re: how to get this data in a one line of output [message #280855 is a reply to message #280849] Thu, 15 November 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't even read piece of code that does not follow the OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: how to get this data in a one line of output [message #280857 is a reply to message #280850] Thu, 15 November 2007 00:59 Go to previous messageGo to next message
slipzst
Messages: 7
Registered: November 2007
Location: KELANA JAYA
Junior Member

my output not in the correct line..so i should have the output like this (example)

xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
xxxxxxxxxxxxx xxxxxxxxxxx xx-xxx-xxxx:xx:xx
Re: how to get this data in a one line of output [message #280858 is a reply to message #280849] Thu, 15 November 2007 01:00 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You waited 10 minutes before you started complaining about no response! Shocked

Do you imagine OraFAQ is like a call-centre somewhere with thousands of Oracle experts (working without pay!) picking questions off the top of the stack as fast as they are posted?
Re: how to get this data in a one line of output [message #280859 is a reply to message #280850] Thu, 15 November 2007 01:01 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 15 November 2007 17:47

And is there a problem with it? What does it do? What do you wnat it to do?

Ross Leishman


Show us what it produces now.
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 Go to previous messageGo to next message
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 this
SQL> 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 this
SQL> 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: how to get this data in a one line of output [message #280876 is a reply to message #280873] Thu, 15 November 2007 01:28 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about reading the OraFAQ Forum Guide to learn how to format your post? You are producing garbage here.

I'll format your last message (using the [code] tags) and delete the rest.
Re: how to get this data in a one line of output [message #280878 is a reply to message #280876] Thu, 15 November 2007 01:30 Go to previous messageGo to next message
slipzst
Messages: 7
Registered: November 2007
Location: KELANA JAYA
Junior Member

croatian dont know the sql...so shut up
Re: how to get this data in a one line of output [message #280879 is a reply to message #280878] Thu, 15 November 2007 01:33 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, I don't know whether you read message ##280861 - I believe solution is there.
Re: how to get this data in a one line of output [message #280881 is a reply to message #280878] Thu, 15 November 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Bingo! You'll win my vote to no more answer.

(Don't worry, French no more know SQL)

Regards
Michel
Re: how to get this data in a one line of output [message #280883 is a reply to message #280881] Thu, 15 November 2007 01:40 Go to previous messageGo to next message
slipzst
Messages: 7
Registered: November 2007
Location: KELANA JAYA
Junior Member

bingo...thanks...no women no cry...so sensitif all of you...cannot be like this u as a senior should not to be hurt..thanks anyway
Re: how to get this data in a one line of output [message #280889 is a reply to message #280883] Thu, 15 November 2007 01:55 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In addition to format your post, please don't use IM speak.

In short, follow the rules.

Regards
Michel
Previous Topic: cross linking in same table
Next Topic: simplify this query
Goto Forum:
  


Current Time: Sat Feb 15 13:49:40 CST 2025