Topics Topics Help/Instructions Help Edit Profile Profile Member List Register Paatha Gnyapakaalu - Archives from Old DB  
Search New Posts 1 | 2 | 8 Hours Search New Posts 1 | 3 | 7 Days Search Search Tree View Tree View Latest tweets Live Tweets
Bewarse Talk Discussion Board * Archives - 2011 * Cine Talk - Reviews, Gossips, Insider Info etc. * Archive through December 09, 2011 * New SQL Query help < Previous Next >

Author Message
Top of pagePrevious messageNext messageBottom of page Link to this message

Sam
Kurra Bewarse
Username: Sam

Post Number: 3688
Registered: 03-2004
Posted From: 12.176.249.232

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 3:44 pm:    Edit Post Delete Post View Post/Check IP
Ravanabrahma:

its working even for the std table query also



good stuff...
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18079
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 3:39 pm:    Edit Post Delete Post View Post/Check IP
its working even for the std table query also..:-)
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18075
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 1:28 pm:    Edit Post Delete Post View Post/Check IP
annai..I created a test table and used the created function..its working good..but not working with the main tables from my query..edo theda koduthundi..basic ga concept ardamipoindi annai..will let you know.
Top of pagePrevious messageNext messageBottom of page Link to this message

Sam
Kurra Bewarse
Username: Sam

Post Number: 3686
Registered: 03-2004
Posted From: 12.176.249.232

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 1:14 pm:    Edit Post Delete Post View Post/Check IP
final gaa work avuthundaaaa inkaa ledaaa?
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18074
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 12:46 pm:    Edit Post Delete Post View Post/Check IP
sam annai..

I used below type and function..

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
l_string VARCHAR2(32767);
BEGIN
FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
IF i != p_varchar2_tab.FIRST THEN
l_string := l_string || p_delimiter;
END IF;
l_string := l_string || p_varchar2_tab(i);
END LOOP;
RETURN l_string;
END tab_to_string;
/

next I used

tab_to_string(CAST(COLLECT(TO_CHAR(je_line_num)) AS t_varchar2_tab)) as je_line_num,

edo konchem work ayyelagane undi kani..result inka 3 rows ee vasthunnay..group by or something theda unnattundi..chostha aagu annai
Top of pagePrevious messageNext messageBottom of page Link to this message

Nitho
Mudiripoyina Bewarse
Username: Nitho

Post Number: 20229
Registered: 12-2004
Posted From: 65.223.175.131

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 12:27 pm:    Edit Post Delete Post View Post/Check IP
ee Hemu gadu DB help thisukoni bathikestunnaru ayyyyaaaMOVIEART--bemmi.boodida
Jai Rajanna, Jai Jai Jagan anna
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18073
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 12:27 pm:    Edit Post Delete Post View Post/Check IP
Sam:

wm_comcat work avatam ledaaa....

I don't have oracle to try....



ledu ba..work avvatam ledu.
Top of pagePrevious messageNext messageBottom of page Link to this message

Sam
Kurra Bewarse
Username: Sam

Post Number: 3683
Registered: 03-2004
Posted From: 12.176.249.232

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 12:23 pm:    Edit Post Delete Post View Post/Check IP
wm_comcat work avatam ledaaa....

I don't have oracle to try....
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18072
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 12:13 pm:    Edit Post Delete Post View Post/Check IP
Sam:

result ila raaavochu from my example of data....

14, 1, 2, 300
15,1,2,300
16, 1,2,300
ee rows moodu contact cheyyali.....
antha quick gaa emi thattatam ledu.



prasthutham naku koda ilage vasthundi annai..concatenate chestaniki

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php#collect

ee link follow avvalemo theledu..chosthunna..
Top of pagePrevious messageNext messageBottom of page Link to this message

Sam
Kurra Bewarse
Username: Sam

Post Number: 3682
Registered: 03-2004
Posted From: 12.176.249.232

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 12:11 pm:    Edit Post Delete Post View Post/Check IP
select table1.line_id, q1.gl_header, q1.trx_number, q1.amount from (
select sum(trx_amount) as amount, trx_number, gl_header from table1
group by trx_number, gl_header) as q1, table1
where table1.trx_number = q1.trx_number and table1.gl_header= q1.gl_header

result ila raaavochu from my example of data....

14, 1, 2, 300
15,1,2,300
16, 1,2,300
ee rows moodu contact cheyyali.....
antha quick gaa emi thattatam ledu....
Top of pagePrevious messageNext messageBottom of page Link to this message

Symbol_of_king
Kurra Bewarse
Username: Symbol_of_king

Post Number: 2096
Registered: 12-2005
Posted From: 166.147.116.53

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:43 am:    Edit Post Delete Post View Post/Check IP
Use analytical function
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18071
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:40 am:    Edit Post Delete Post View Post/Check IP
Sam:

enni vunte anni amount sum sesi , trx_number, heder meeda group chesi, line_id anni contact cheyyalaaa?

in my example

300, 1, 2, 14 15 16
anthenaaaa?



anthe annai..exactly..
Top of pagePrevious messageNext messageBottom of page Link to this message

Sam
Kurra Bewarse
Username: Sam

Post Number: 3681
Registered: 03-2004
Posted From: 12.176.249.232

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:39 am:    Edit Post Delete Post View Post/Check IP
enni vunte anni amount sum sesi , trx_number, heder meeda group chesi, line_id anni contact cheyyalaaa?

in my example

300, 1, 2, 14 15 16
anthenaaaa?
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18070
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:36 am:    Edit Post Delete Post View Post/Check IP
Sam:

line_id lu rende vuntayaa, anthakanna ekkuva unde chance vundaaaa?



ekkuve untay annai..example ki 2 icha..

for the same trx_number and header_id and for different line_num we need to show the output in one line with sum(trx_amount) with the line_num concatenated in that single row
Top of pagePrevious messageNext messageBottom of page Link to this message

Sam
Kurra Bewarse
Username: Sam

Post Number: 3680
Registered: 03-2004
Posted From: 12.176.249.232

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:34 am:    Edit Post Delete Post View Post/Check IP
example,

100, 1, 2, 14
100, 1, 2, 15
--
100, 1, 2, 16
.
.
.
.
.
ala
Top of pagePrevious messageNext messageBottom of page Link to this message

Sam
Kurra Bewarse
Username: Sam

Post Number: 3679
Registered: 03-2004
Posted From: 12.176.249.232

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:33 am:    Edit Post Delete Post View Post/Check IP
line_id lu rende vuntayaa, anthakanna ekkuva unde chance vundaaaa?
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18069
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:30 am:    Edit Post Delete Post View Post/Check IP
Looks like even I can't use wm_concat :-(
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18068
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:26 am:    Edit Post Delete Post View Post/Check IP
I am using 10g database, so I can't use the function LISTAGG to concatenate the column row values.

Looks like I may have to write function to do this function and call the function..any other alternatives?
Top of pagePrevious messageNext messageBottom of page Link to this message

Ravanabrahma
Mudiripoyina Bewarse
Username: Ravanabrahma

Post Number: 18067
Registered: 06-2004
Posted From: 98.151.191.253

Rating: N/A
Votes: 0 (Vote!)

Posted on Wednesday, December 07, 2011 - 11:24 am:    Edit Post Delete Post View Post/Check IP
naa query data ila return chesthundi..

trx_amount, trx_number gl_header_id gl_line_id

100 1 2 14
200 1 2 15

I want the output to be

trx_amount, trx_number gl_header_id gl_line_id

300 1 2 14, 15

emi use cheyyali query lo?

Topics | Last Hour | Last Day | Last Week | Tree View | Search | Help/Instructions | Program Credits Administration