SQL & PL/SQL

Hi,

Please refer the below given script.

CREATE TABLE ITLINEQBOOKEDPROFIT
(DATAAREAID	VARCHAR2(200),
EQUITYCODE	VARCHAR2(200),
PORTFOLIOID	VARCHAR2(200),
PROFIT NUMBER);

CREATE TABLE ITLINEQMISTABLE
(DATAAREAID	VARCHAR2(200),
EQUITYCODE	VARCHAR2(200),
PORTFOLIOID	VARCHAR2(200),
UNBOOKEDPROFIT NUMBER);

INSERT INTO ITLINEQMISTABLE
VALUES(
'ifn',	'EQ_001103',	'HP0'	,1.332031);

INSERT INTO ITLINEQMISTABLE
VALUES('ifn',	'EQ_001103',	'HP1',	1.561525006196);

INSERT INTO ITLINEQMISTABLE
VALUES('ifn','EQ_001103','H1',	-62410958.4803);
COMMIT;

SELECT   
		 COALESCE (ITLP.EQUITYCODE, ITLUP.EQUITYCODE) EQUITYCODE,
         COALESCE (ITLP.PORTFOLIOID, ITLUP.PORTFOLIOID) PORTFOLIOID, 
         ITLP.PROFIT,
         ITLUP.UNBOOKEDPROFIT,
         (NVL (ITLP.PROFIT, 0) + NVL (ITLUP.UNBOOKEDPROFIT, 0)) TOTAL,
         DENSE_RANK () OVER (ORDER BY (  NVL (ITLP.PROFIT, 0)
                                       + NVL (ITLUP.UNBOOKEDPROFIT, 0)) DESC) RNK
    FROM (SELECT   A.DATAAREAID, A.EQUITYCODE, A.PORTFOLIOID,
                   NVL (SUM (A.PROFIT), 0) PROFIT
              FROM ITLINEQBOOKEDPROFIT A
             WHERE  A.DATAAREAID = 'ifn'
             and A.EQUITYCODE = 'EQ_001103'
          GROUP BY A.DATAAREAID, A.EQUITYCODE, A.PORTFOLIOID) ITLP
         FULL OUTER JOIN
         (SELECT   B.DATAAREAID, B.EQUITYCODE, B.PORTFOLIOID,
                   NVL (SUM (UNBOOKEDPROFIT), 0) UNBOOKEDPROFIT
              FROM ITLINEQMISTABLE B
             WHERE B.DATAAREAID = 'ifn'
                   AND B.EQUITYCODE = 'EQ_001103'
          GROUP BY B.DATAAREAID, B.EQUITYCODE, B.PORTFOLIOID) ITLUP
         ON UPPER (ITLP.DATAAREAID) = UPPER (ITLUP.DATAAREAID)
       AND UPPER (ITLP.EQUITYCODE) = UPPER (ITLUP.EQUITYCODE)
       AND UPPER (ITLP.PORTFOLIOID) = UPPER (ITLUP.PORTFOLIOID);
       
SELECT *
FROM (
SELECT   
		 COALESCE (ITLP.EQUITYCODE, ITLUP.EQUITYCODE) EQUITYCODE,
         COALESCE (ITLP.PORTFOLIOID, ITLUP.PORTFOLIOID) PORTFOLIOID, 
         ITLP.PROFIT,
         ITLUP.UNBOOKEDPROFIT,
         (NVL (ITLP.PROFIT, 0) + NVL (ITLUP.UNBOOKEDPROFIT, 0)) TOTAL,
         DENSE_RANK () OVER (ORDER BY (  NVL (ITLP.PROFIT, 0)
                                       + NVL (ITLUP.UNBOOKEDPROFIT, 0)) DESC) RNK
    FROM (SELECT   A.DATAAREAID, A.EQUITYCODE, A.PORTFOLIOID,
                   NVL (SUM (A.PROFIT), 0) PROFIT
              FROM ITLINEQBOOKEDPROFIT@axplive A
             WHERE  A.DATAAREAID = 'ifn'
             and A.EQUITYCODE = 'EQ_001103'
          GROUP BY A.DATAAREAID, A.EQUITYCODE, A.PORTFOLIOID) ITLP
         FULL OUTER JOIN
         (SELECT   B.DATAAREAID, B.EQUITYCODE, B.PORTFOLIOID,
                   NVL (SUM (UNBOOKEDPROFIT), 0) UNBOOKEDPROFIT
              FROM ITLINEQMISTABLE@axplive B
             WHERE B.DATAAREAID = 'ifn'
                   AND B.EQUITYCODE = 'EQ_001103'
          GROUP BY B.DATAAREAID, B.EQUITYCODE, B.PORTFOLIOID) ITLUP
         ON UPPER (ITLP.DATAAREAID) = UPPER (ITLUP.DATAAREAID)
       AND UPPER (ITLP.EQUITYCODE) = UPPER (ITLUP.EQUITYCODE)
       AND UPPER (ITLP.PORTFOLIOID) = UPPER (ITLUP.PORTFOLIOID)
       );

SELECT *
FROM  V$VERSION;

And

CREATE TABLE succeeded.
CREATE TABLE succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
commited
EQUITYCODE PORTFOLIOID PROFIT                 UNBOOKEDPROFIT         TOTAL                  RNK                    
---------- ----------- ---------------------- ---------------------- ---------------------- ---------------------- 
EQ_001103  HP1         1.561525006196                                1.561525006196         1                      
EQ_001103  HP0         1.332031                                      1.332031               2                      
EQ_001103  H1                                 -369234076.3318        -369234076.3318        3                      

EQUITYCODE PORTFOLIOID PROFIT                 UNBOOKEDPROFIT         TOTAL                  RNK                    
---------- ----------- ---------------------- ---------------------- ---------------------- ---------------------- 
                       1.561525006196                                1.561525006196         1                      
                       1.332031                                      1.332031               2                      
EQ_001103  H1                                 -369234076.3318        -369234076.3318        3                      

BANNER                                                           
---------------------------------------------------------------- 
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production 
PL/SQL Release 9.2.0.8.0 - Production                            
CORE	9.2.0.8.0	Production                                          
TNS for Solaris: Version 9.2.0.8.0 - Production                  
NLSRTL Version 9.2.0.8.0 - Production

When I add Select * From (..) the displayed result-set changes completely, can somebody tell me the cause, and the solution. The same SQLs work fine on 11gR2 (on my local machine).

*009*