Home » SQL & PL/SQL » SQL & PL/SQL » Analytic Function (Oracle 10 g)
Analytic Function [message #445225] Fri, 26 February 2010 09:32 Go to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
Hi

I want to build a hierarchy and output it into a sub-report. Since the data in few hierarchy fields exceeds 4000 characters and therefore m geting the ORA-01489 error and the entire report was failing.

So i have used user aggregate function to print the out put.
I have also used row_number() to print sequence in query output.

the query that i am using is

SELECT pr_id,
REPLACE (REPLACE (twadmin.STRAGG2(distinct RPAD (to_number(curr), 2, ' ') || ')' || NVL (DECODE (medical, NULL, clinical, medical), 'UNKNOWN') || '###' ||
RPAD (' Causality as per rport ', 50, ' ') || ': ' || reporter || '###' ||
RPAD (' Causality as per report2 ', 50, ' ') || ': ' || hospital || '###' ||
RPAD (' Dechallenge', 50, ' ') || ': '|| dechallenge || '###' ||
RPAD (' Rechallenge', 50, ' ') || ': '|| rechallenge), ',', CHR(10)), '###', CHR (10)) causality
FROM
(SELECT /*+ no_unnest*/ data.pr_id, pst.name status,
MAX (DECODE (df.name, 'Dechallenge', lst.name, NULL)) dechallenge,
MAX (DECODE (df.name, 'Rechallenge', lst.name, NULL)) rechallenge,
MAX (DECODE (df.name, 'Reporter', lst.name, NULL)) reporter ,
MAX (DECODE (df.name, 'Hospital', lst.name, NULL)) hospital,
MAX (DECODE (df.name, 'Medical', meddicaltab.llt_name||' ('||meddicaltab.pt_name||')', NULL)) medical,
MAX (DECODE (df.name, 'Clinical', meddicaltab.llt_name||' ('||meddicaltab.pt_name||')', NULL)) clinical,
row_number() OVER (PARTITION BY data.pr_id order by gd.seq_no) curr
FROM pr prod_pr, grid_data gd, twcr_pr_addtl_data data, medical_util medicaltab, addtl_type lst,
data_fields df,data_fields gd1, pr_status_type pst
WHERE gd1.name = 'Reactions Grid'
AND gd1.id = gd.grid_id
AND df.name IN ('Dechallenge', 'Rechallenge', 'Reporter', 'Hospital', 'Medical', 'Clinical Medical')
AND data.data_field_id = df.id
AND gd.pr_id = data.pr_id
AND gd.data_field_id = data.data_field_id
AND gd.pr_addtl_data_id = data.id
AND gd.pr_id = prod_pr.id
AND prod_pr.status_type = pst.id
AND pst.name <> 'Voided'
AND data.n_value = medicaltab.id (+)
AND data.pr_id = medicaltab.pr_id (+)
AND data.n_value = lst.id (+)
AND prod_pr.parent_id = NVL(twcr_interface.get_parent_pr (), prod_pr.parent_id)
GROUP BY data.pr_id,gd.seq_no,pst.name
) react
GROUP BY pr_id

the output of the query is
ID Causality
-- ------
375510 1 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
2 )Mental status changes (Mental status changes)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
3 )Renal insufficiency (Renal failure)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
375516 1 )Asystole (Cardiac arrest)
Causality as per report1 : Not Reported
Causality as per report2 : Probably Not
Dechallenge : Not Applicable
Rechallenge : Not Applicable
10)Mucositis (Mucosal inflammation)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
11)Sloughing (Skin exfoliation)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
12)Renal failure (Renal failure)
Causality as per report1 : Not Reported
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
13)Fever (Pyrexia)
Causality as per report1 : Not Reported
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
2 )Multiorgan failure (Multi-organ failure)
Causality as per report1 : Not Reported
Causality as per report2 : Probably Not
Dechallenge :
Rechallenge :
3 )Comatose (Coma)
Causality as per report1 : Not Reported
Causality as per report2 : Probably Not
Dechallenge :
Rechallenge :

but the expected out put is

ID Causality
-- ------
375510 1 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
2 )Mental status changes (Mental status changes)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
3 )Renal insufficiency (Renal failure)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
375516 1 )Asystole (Cardiac arrest)
Causality as per report1 : Not Reported
Causality as per report2 : Probably Not
Dechallenge : Not Applicable
Rechallenge : Not Applicable
2 )Multiorgan failure (Multi-organ failure)
Causality as per report1 : Not Reported
Causality as per report2 : Probably Not
Dechallenge :
Rechallenge :
3 )Comatose (Coma)
Causality as per report1 : Not Reported
Causality as per report2 : Probably Not
Dechallenge :
Rechallenge :
................................
.................................................
10)Mucositis (Mucosal inflammation)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
11)Sloughing (Skin exfoliation)
Causality as per report1 : Probable
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
12)Renal failure (Renal failure)
Causality as per report1 : Not Reported
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
13)Fever (Pyrexia)
Causality as per report1 : Not Reported
Causality as per report2 : Possible
Dechallenge :
Rechallenge :
The problem is arising when the no of rows for one particular id is more than 10. The order is displayed as
1)
10)
11)
12)
2)
3)
4)

but it should be as
1)
2)
3)
..
..
..
10)
11)
12) for the values in the causality field

Looking for some help.

Thanks in advance
Re: Analytic Function [message #445227 is a reply to message #445225] Fri, 26 February 2010 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Analytic Function [message #445230 is a reply to message #445225] Fri, 26 February 2010 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.
This is unreproducible.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Analytic Function [message #445236 is a reply to message #445225] Fri, 26 February 2010 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The problem is arising when the no of rows for one particular id is more than 10. The order is displayed as

How my previous answer to your same question do not fit your requirement?
Did you try it?
http://www.orafaq.com/forum/mv/msg/122056/445198/102589/#msg_445198

Regards
Michel
Re: Analytic Function [message #445241 is a reply to message #445236] Fri, 26 February 2010 10:11 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
i tried it it did not work
Re: Analytic Function [message #445243 is a reply to message #445225] Fri, 26 February 2010 10:17 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if you told us exactly what you tried and what it did we would be able to tell you what you did wrong.
Re: Analytic Function [message #445244 is a reply to message #445243] Fri, 26 February 2010 10:23 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
well i tried to add
order by to_number(substr(causality,instr(causality,' ')-1))
after the last group by
and the error was invalid number.

so then i modifie it in the following manner
order by to_number(substr(causality,instr(causality,')',1)-1))
still it is giving me inalid number error
Re: Analytic Function [message #445245 is a reply to message #445225] Fri, 26 February 2010 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've told it to get all the characters from the character before the ')', you want to tell it to get all the characters untill the ')'

try this:
order by to_number(substr(causality,1,instr(causality,')',1)-1)) 
Re: Analytic Function [message #445246 is a reply to message #445245] Fri, 26 February 2010 10:32 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
still the output is same.
Am i clear on the expected output? or should i try explaining it again
Re: Analytic Function [message #445249 is a reply to message #445244] Fri, 26 February 2010 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
preetm1 wrote on Fri, 26 February 2010 17:23
well i tried to add
order by to_number(substr(causality,instr(causality,' ')-1))
after the last group by
and the error was invalid number.

so then i modifie it in the following manner
order by to_number(substr(causality,instr(causality,')',1)-1))
still it is giving me inalid number error

LOL!
Try again.
Pick up one of your strings and try to get the first number with instr and substr. It is not so hard.
To help you there is also Database SQL Reference.

As you refuse to post a test case, you have to do it by yourself.

Regards
Michel

[Updated on: Fri, 26 February 2010 10:48]

Report message to a moderator

Re: Analytic Function [message #445250 is a reply to message #445249] Fri, 26 February 2010 11:02 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
there is no change i output.
the sequence is still the same.
Re: Analytic Function [message #445251 is a reply to message #445250] Fri, 26 February 2010 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.
When I say "post" I mean use SQL*Plus and copy and paste what you tried and do it in the way explained in OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel
Re: Analytic Function [message #445325 is a reply to message #445251] Sun, 28 February 2010 13:56 Go to previous messageGo to next message
starz
Messages: 2
Registered: February 2010
Location: Chicago
Junior Member
I got the point as i am also struggling with the same thing. Here, the following post is used to concatenate the hierarchy rows -

asktomdotoracledotcom/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336#16551777586484

Refer the post Re: Sorted aggregates June 14, 2005 - 8am Central time zone Bookmark | Bottom | TopReviewer: Laurent Schneider from Switzerland

Per the post, i used Distinct to get the ascending order sorting in the concatenated value. But when it comes to numbers, it is sorting in ASCII characters and not in numbers. For example, if in the example (in link), instead of the output shown

DEPTNO ENAME
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


If we have numbers, it will sort something like -

DEPTNO ENAME
---------- --------------------------------------------------
10 1CLARK,2KING,3MILLER
20 1ADAMS,10FORD,11JONES,2SCOTT,3SMITH
30 1ALLEN,10BLAKE,11JAMES,12MARTIN,2TURNER,3WARD

Whereas, the required output is -

DEPTNO ENAME
---------- --------------------------------------------------
10 1CLARK,2KING,3MILLER
20 1ADAMS,2SCOTT,3SMITH,10FORD,11JONES
30 1ALLEN,2TURNER,3WARD,10BLAKE,11JAMES,12MARTIN

Additionally, the row is expected to increase in character length and therefore SYS_CONNECT_BY_PATH cannot be used. So, the standard asktom stragg function is used with clobs to overcome that limitation.

Hope it makes the things more clear and help you to suggest a solution.
Re: Analytic Function [message #445326 is a reply to message #445325] Sun, 28 February 2010 14:12 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Analytic Function [message #445327 is a reply to message #445326] Sun, 28 February 2010 16:13 Go to previous messageGo to next message
starz
Messages: 2
Registered: February 2010
Location: Chicago
Junior Member
thanks. Let me try to explain the initial requirement and the steps that i took till now. The below DDL is a sample. The actual values are fetched from a complex data structure through a subquery in the from section. So, the requirement is -

DDL -
create table xx_test (sno number(3),seq number(5), text varchar2(4000));

Insert Values -

insert into xx_test values(10,1,'CLARK');
insert into xx_test values(10,2,'KING');
insert into xx_test values(10,3,'MILLER');
insert into xx_test values(20,1,'ADAMS');
insert into xx_test values(20,2,'FORD');
insert into xx_test values(20,3,'JONES');
insert into xx_test values(20,4,'SCOTT');
insert into xx_test values(20,5,'SMITH');
insert into xx_test values(30,1,'ALLEN');
insert into xx_test values(30,2,'BLAKE');
insert into xx_test values(30,3,'JAMES');
insert into xx_test values(30,4,'MARTIN');
insert into xx_test values(30,5,'TURNER');
insert into xx_test values(30,6,'WARD');
insert into xx_test values(30,7,'JOHN');
insert into xx_test values(30,8,'PAUL');
insert into xx_test values(30,9,'ANDREW');
insert into xx_test values(30,10,'CHUCK');
insert into xx_test values(30,11,'TIM');
insert into xx_test values(30,12,'PADDY');
insert into xx_test values(30,13,'MIKE');
insert into xx_test values(30,14,'LOCK');
insert into xx_test values(30,15,'WALT');


****this may extend and a single row output can be way beyond 4000 characters. Therefore, sys_connect_by_path flashes the error ORA-01489: result of string concatenation is too long.

Output Required -

10 1) CLARK,2) KING,3) MILLER
20 1) ADAMS,2) FORD,3) JONES,4) SCOTT,5) SMITH
30 1) ALLEN,2) JAMES,3) TURNER,4) JOHN,5) ANDREW,6) TIM,7) MIKE,Cool WALT,9) LOCK,10) PADDY,11) CHUCK,12) PAUL,13) WARD,14) MARTIN,15) BLAKE

Here, i may have unlimited names and these 3 rows will go as-is onto a report. This was working fine with sys_connect_by_path till the number of characters in a row were < 4000 characters. But my current solution has started giving issues since the number of characters increased.

*****************************************************************
IT WOULD BE GREAT IF YOU CAN PROVIDE A SOLUTION READING TILL THIS POINT.

I HAVE EXPLAINED THE OPTIONS TRIED BY ME THAT HAVE PARTIALLY WORKED IN THE NEXT SECTION
*****************************************************************

I found a solution at asktom to implement it with a custom aggregate type. Therefore, i used the following DDL to create a custom type and aggregate function -

create or replace type clob_agg_type as object
(
total clob,
static function
ODCIAggregateInitialize(sctx IN OUT clob_concat_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT clob_concat_agg_type ,value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN clob_concat_agg_type,
returnValue OUT clob,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT clob_concat_agg_type,
ctx2 IN clob_concat_agg_type)
return number
);


Type created.

create or replace type body clob_concat_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT clob_concat_agg_type)
return number
is
begin
sctx := clob_concat_agg_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT clob_concat_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN clob_concat_agg_type,
returnValue OUT clob,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT clob_concat_agg_type,
ctx2 IN clob_concat_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/

Type body created.


CREATE or replace
FUNCTION stragg2(input varchar2 )
RETURN clob
PARALLEL_ENABLE AGGREGATE USING clob_concat_agg_type;


Function created.


Thereafter, I used this query to fetch the output -

select sno,stragg2(seq || ') ' || text) from xx_test group by sno;

Output -

SNO STRAGG2(SEQ||')'||TEXT)

10 1) CLARK,2) KING,3) MILLER
20 1) ADAMS,5) SMITH,4) SCOTT,2) FORD,3) JONES
30 1) ALLEN,3) JAMES,5) TURNER,7) JOHN,9) ANDREW,11) TIM,13) MIKE,15) WALT,14) LOCK,12) PADDY,10) CHUCK,Cool PAUL,6) WARD,4) MARTIN,2) BLAKE

As you can see, this is not sorted. I want this concatenation in a sorted order. I read the posts further and applied a distinct for an ascending sorting as -

select sno,stragg2(distinct seq || ') ' || text) from xx_test group by sno;

Output now is -

SNO STRAGG2(DISTINCTSEQ||')'||TEXT)

10 1) CLARK,2) KING,3) MILLER
20 1) ADAMS,2) FORD,3) JONES,4) SCOTT,5) SMITH
30 1) ALLEN,10) CHUCK,11) TIM,12) PADDY,13) MIKE,14) LOCK,15) WALT,2) BLAKE,3) JAMES,4) MARTIN,5) TURNER,6) WARD,7) JOHN,Cool PAUL,9) ANDREW


It sorted but the sorting is ASCII..... 1) 10) 11) 12)..... 2) 3)
which is not what my expected output is. I want it to be sorted numerically as 1) 2) 3)..... 11) 12) 13) 14) 15).... I am OK to implement any solution to get the output like -

10 1) CLARK,2) KING,3) MILLER
20 1) ADAMS,2) FORD,3) JONES,4) SCOTT,5) SMITH
30 1) ALLEN,2) JAMES,3) TURNER,4) JOHN,5) ANDREW,6) TIM,7) MIKE,Cool WALT,9) LOCK,10) PADDY,11) CHUCK,12) PAUL,13) WARD,14) MARTIN,15) BLAKE


Hope this time the post helps. Please help me find an appropriate solution. Again, the data is not the actual data that i am working on but the situation is acactly what i explained above. Thanks!
Re: Analytic Function [message #445328 is a reply to message #445327] Sun, 28 February 2010 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
> Please help me find an appropriate solution
When your only tool is a hammer, every problem is treated as though it is a nail.

While a hammer is a fine tool, it is suboptimal to produce 2 boards starting with a single piece of lumber.

On the surface it appears that the solution could be written in PERL in 2 - 3 dozen lines of code.

Of course, nothing is impossible for the person who actually does not have to do it.
Re: Analytic Function [message #445353 is a reply to message #445327] Mon, 01 March 2010 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Just modify the stragg function to fit your need, for instance:
SQL> select * from xx_test order by sno, seq;
       SNO        SEQ TEXT
---------- ---------- ------------------------------
        10          1 CLARK
        10          2 KING
        10          3 MILLER
        20          1 ADAMS
        20          2 FORD
        20          3 JONES
        20          4 SCOTT
        20          5 SMITH
        30          1 ALLEN
        30          2 BLAKE
        30          3 JAMES
        30          4 MARTIN
        30          5 TURNER
        30          6 WARD
        30          7 JOHN
        30          8 PAUL
        30          9 ANDREW
        30         10 CHUCK
        30         11 TIM
        30         12 PADDY
        30         13 MIKE
        30         14 LOCK
        30         15 WALT

23 rows selected.

SQL> create or replace type my_type as object (
  2    seq  number(5),
  3    text varchar2(4000)
  4  );
  5  /

Type created.

SQL> create or replace type my_tab as table of my_type;
  2  /

Type created.

SQL> create or replace type my_concat_agg_type as object (
  2    vals  my_tab,
  3    static function ODCIAggregateInitialize (
  4      sctx IN OUT my_concat_agg_type
  5    ) return number,
  6    member function ODCIAggregateIterate (
  7      self  IN OUT my_concat_agg_type, 
  8      value IN my_type
  9    ) return number,
 10    member function ODCIAggregateMerge (
 11      self IN OUT my_concat_agg_type,
 12      ctx2 IN my_concat_agg_type
 13    ) return number,
 14    member function ODCIAggregateTerminate (
 15      self IN my_concat_agg_type,
 16      returnValue OUT clob,
 17      flags IN number
 18    ) return number 
 19  );
 20  /

Type created.

SQL> create or replace type body my_concat_agg_type
  2  is
  3    static function ODCIAggregateInitialize (
  4      sctx IN OUT my_concat_agg_type
  5    ) return number
  6    is
  7    begin
  8      sctx := my_concat_agg_type (my_tab());
  9      return ODCIConst.Success;
 10    end;
 11    member function ODCIAggregateIterate (
 12      self  IN OUT my_concat_agg_type, 
 13      value IN my_type
 14    ) return number
 15    is
 16    begin
 17      self.vals.extend;
 18      self.vals(self.vals.count) := value;
 19      return ODCIConst.Success;
 20    end;
 21    member function ODCIAggregateMerge (
 22      self IN OUT my_concat_agg_type,
 23      ctx2 IN my_concat_agg_type
 24    )
 25    return number
 26    is
 27    begin
 28      for i in 1..ctx2.vals.count loop
 29        self.vals.extend;
 30        self.vals(self.vals.count) := ctx2.vals(i);
 31      end loop;
 32      return ODCIConst.Success;
 33    end;
 34    member function ODCIAggregateTerminate (
 35      self IN my_concat_agg_type,
 36      returnValue OUT clob,
 37      flags IN number
 38    ) return number
 39    is
 40    begin
 41      for rec in (select * from table(vals) order by seq) loop
 42        returnValue := returnValue || ', ' || rec.seq || ') ' || rec.text;
 43      end loop;
 44      returnValue := ltrim(returnValue,', ');
 45      return ODCIConst.Success;
 46    end;
 47  end;
 48  /

Type body created.

SQL> CREATE or replace FUNCTION mystragg (input my_type)
  2  RETURN clob
  3  PARALLEL_ENABLE AGGREGATE 
  4  USING my_concat_agg_type;
  5  /

Function created.

SQL> select sno, mystragg(my_type(seq,text)) texts
  2  from xx_test
  3  group by sno
  4  order by sno
  5  /
       SNO TEXTS
---------- ---------------------------------------------------------------------------------
        10 1) CLARK, 2) KING, 3) MILLER
        20 1) ADAMS, 2) FORD, 3) JONES, 4) SCOTT, 5) SMITH
        30 1) ALLEN, 2) BLAKE, 3) JAMES, 4) MARTIN, 5) TURNER, 6) WARD, 7) JOHN, 8) PAUL, 9)
            ANDREW, 10) CHUCK, 11) TIM, 12) PADDY, 13) MIKE, 14) LOCK, 15) WALT

3 rows selected.

Regards
Michel
Re: Analytic Function [message #445662 is a reply to message #445353] Wed, 03 March 2010 04:31 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
Hi Michel,

As shown in the above example the values for the seq field is not fetched from a table but it is fetched from the function row_number() in my scenario.

Thanks
Re: Analytic Function [message #445664 is a reply to message #445662] Wed, 03 March 2010 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what does this change?
Did you try to use what I posted?
Which problem had you?

Regards
Michel
Re: Analytic Function [message #445667 is a reply to message #445664] Wed, 03 March 2010 04:58 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
HI

I tried to use whats was given by you

the first thingis that the value for the seq field is generated by the row_number() function and the value for text is a result of the concatenation for various fields.

so im getting the error invalid identifier

Thanks
Re: Analytic Function [message #445668 is a reply to message #445667] Wed, 03 March 2010 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one can debug a query that he/she don't see.
The only thing that can be said is: fix the query.

As you could see with test case I can do more.

Regards
Michel

[Updated on: Wed, 03 March 2010 05:16]

Report message to a moderator

Re: Analytic Function [message #445670 is a reply to message #445668] Wed, 03 March 2010 05:30 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
SELECT   pr_id,
         REPLACE
            (REPLACE
                (twadmin.mystragg
                               (my_type (curr,
                                            NVL (DECODE (medical,
                                                         NULL, clinical,
                                                         medical
                                                        ),
                                                 'UNKNOWN'
                                                )
                                         || '###'
                                         || RPAD (' Causality as per rport ',
                                                  50,
                                                  ' '
                                                 )
                                         || ': '
                                         || reporter
                                         || '###'
                                         || RPAD
                                                (' Causality as per report2 ',
                                                 50,
                                                 ' '
                                                )
                                         || ': '
                                         || hospital
                                         || '###'
                                         || RPAD (' Dechallenge', 50, ' ')
                                         || ': '
                                         || dechallenge
                                         || '###'
                                         || RPAD (' Rechallenge', 50, ' ')
                                         || ': '
                                         || rechallenge
                                        )
                               ),
                 ',',
                 CHR (10)
                ),
             '###',
             CHR (10)
            ) causality
    FROM (SELECT   /*+ no_unnest*/
                   DATA.pr_id, pst.NAME status,
                   MAX (DECODE (df.NAME, 'Dechallenge', lst.NAME, NULL)
                       ) dechallenge,
                   MAX (DECODE (df.NAME, 'Rechallenge', lst.NAME, NULL)
                       ) rechallenge,
                   MAX (DECODE (df.NAME,
                                'Reporter', lst.NAME,
                                NULL
                               )) reporter,
                   MAX (DECODE (df.NAME,
                                'Hospital', lst.NAME,
                                NULL
                               )) hospital,
                   MAX (DECODE (df.NAME,
                                'Medical', meddicaltab.llt_name
                                 || ' ('
                                 || meddicaltab.pt_name
                                 || ')',
                                NULL
                               )
                       ) medical,
                   MAX (DECODE (df.NAME,
                                'Clinical', meddicaltab.llt_name
                                 || ' ('
                                 || meddicaltab.pt_name
                                 || ')',
                                NULL
                               )
                       ) clinical,
                   ROW_NUMBER () OVER (PARTITION BY DATA.pr_id ORDER BY gd.seq_no)
                                                                         curr
              FROM pr prod_pr,
                   grid_data gd,
                   twcr_pr_addtl_data DATA,
                   medical_util medicaltab,
                   addtl_type lst,
                   data_fields df,
                   data_fields gd1,
                   pr_status_type pst
             WHERE gd1.NAME = 'Reactions Grid'
               AND gd1.ID = gd.grid_id
               AND df.NAME IN
                      ('Dechallenge',
                       'Rechallenge',
                       'Reporter',
                       'Hospital',
                       'Medical',
                       'Clinical Medical'
                      )
               AND DATA.data_field_id = df.ID
               AND gd.pr_id = DATA.pr_id
               AND gd.data_field_id = DATA.data_field_id
               AND gd.pr_addtl_data_id = DATA.ID
               AND gd.pr_id = prod_pr.ID
               AND prod_pr.status_type = pst.ID
               AND pst.NAME <> 'Voided'
               AND DATA.n_value = medicaltab.ID(+)
               AND DATA.pr_id = medicaltab.pr_id(+)
               AND DATA.n_value = lst.ID(+)
               AND prod_pr.parent_id =
                      NVL (twcr_interface.get_parent_pr (), prod_pr.parent_id)
          GROUP BY DATA.pr_id, gd.seq_no, pst.NAME) react
GROUP BY pr_id


is the query that i tried to use


CM: added code tags

[Updated on: Wed, 03 March 2010 05:35] by Moderator

Report message to a moderator

Re: Analytic Function [message #445671 is a reply to message #445225] Wed, 03 March 2010 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the exact error text is?
Remember we don't have your tables so we can't run your sql.

And can you please use code tags when posting code. I fixed it for you this time but I'm not going to make a habit of it. read the orafaq forum guide if you're not sure how.
Re: Analytic Function [message #445672 is a reply to message #445671] Wed, 03 March 2010 05:44 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
the exact error ORA-00904:: Invalid identifier and MYSTRAGG is hilighted.
Re: Analytic Function [message #445673 is a reply to message #445672] Wed, 03 March 2010 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you create the objects as in my post and have you the privilege to execute it?

Regards
Michel
Re: Analytic Function [message #445676 is a reply to message #445673] Wed, 03 March 2010 05:55 Go to previous message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
It Worked!!!!!!!!!!!!!
I executed the query in the schema in which i created the objects and it worked...

Thanks a lot for your help...and thanks a lot for your patience.
Previous Topic: Query Help - String Manipulation
Next Topic: executing a procedure containg a global temporary rable
Goto Forum:
  


Current Time: Sun Sep 25 07:46:42 CDT 2016

Total time taken to generate the page: 0.15420 seconds