Home » SQL & PL/SQL » SQL & PL/SQL » Query in 9i & 10g (10g Rel 2)
Query in 9i & 10g [message #394396] Thu, 26 March 2009 15:04 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

We have this issue in 10g DB, in Development Region,
The same query works in 9i and gets rows,
But in 10g it runs without Errors But NO ROWS.
Data ,tables and indexes are same in both versions, infact we copied the table again to see,

This view has 26K rows as of now,
Well i don't know why it's view and structure of the view is like this, It's my first few days, just trying to figure out,
Please see the Create View statment
CREATE OR REPLACE VIEW VW_STG_DFU
(LVL, DMDUNIT, LOC, DMDGROUP, DISC, 
 EFF, CEU_FACTOR, FRZ_PROFILE, CEU_DESC, PRODMGR_EMAIL, 
 COMDIR_EMAIL, SOURCE_EMAIL, SAMPLE, ASP_USD, PREDCSR, 
 UDC_DELETEME, LAST_UPD_BY, CREATED_BY, LAST_UPD_DATE, CREATION_DATE, 
 RECORD_TYPE, ASP_LC, GEN_STA, NEW_DFU, ACTIVE, 
 INVOICE_LOC, DATAMART_LAST_UPD_BY, DATAMART_LAST_UPD_DATE, SCALING_FACTOR, SF_UPDATED_DATE)
AS 
SELECT
c.lvl,
stg_dfu.dmdunit,
stg_dfu.loc,
stg_dfu.dmdgroup,
stg_dfu.disc,
stg_dfu.eff,
stg_dfu.ceu_factor,
stg_dfu.frz_profile,
stg_dfu.ceu_desc,
stg_dfu.prodmgr_email,
stg_dfu.comdir_email,
stg_dfu.source_email,
stg_dfu.SAMPLE,
stg_dfu.asp_usd,
stg_dfu.predcsr,
stg_dfu.udc_deleteme,
stg_dfu.last_upd_by,
stg_dfu.created_by,
stg_dfu.last_upd_date,
stg_dfu.creation_date,
stg_dfu.record_type,
stg_dfu.asp_lc,
stg_dfu.gen_sta,
stg_dfu.new_dfu,
stg_dfu.active,
stg_dfu.invoice_loc,
stg_dfu.datamart_last_upd_by,
stg_dfu.datamart_last_upd_date,
stg_dfu.scaling_factor,
stg_dfu.sf_updated_date
FROM STAGING.stg_dfu,
(
SELECT MAX(LVL) LVL, DMDUNIT
FROM(
SELECT LEVEL lvl, DMDUNIT
FROM STAGING.STG_DFU
CONNECT BY PRIOR PREDCSR=DMDUNIT
)
GROUP BY DMDUNIT HAVING  max(LVL)=1
ORDER BY 1) c
WHERE ((stg_dfu.dmdunit = c.dmdunit(+)))



Wondering if this syntax doesn't work in 10g or what.



SELECT 
A.SUCCESSOR_ITEM,A.PREDECESSOR_ITEM,CASE WHEN B.CUMULATIVE_FACTOR IS NULL THEN 1 ELSE B.CUMULATIVE_FACTOR END CUMULATIVE_FACTOR
FROM
(SELECT 
 LVL,SUBSTR(scbp,1,INSTR(scbp,'.')-1) SUCCESSOR_ITEM, PREDECESSOR_ITEM,CUMULATIVE_FACTOR
 FROM
  (
   SELECT LEVEL LVL,DMDUNIT PREDECESSOR_ITEM, SCALING_FACTOR,
   LTRIM(SYS_CONNECT_BY_PATH(DMDUNIT,'.'),'.') ||'.' scbp,
   eval(SYS_CONNECT_BY_PATH(SCALING_FACTOR,'/')) CUMULATIVE_FACTOR
   FROM STAGING.VW_STG_DFU
   START WITH VW_STG_DFU.LVL='1'
   CONNECT BY PRIOR PREDCSR=DMDUNIT
   )  
ORDER BY 1 DESC) A,
(SELECT 
 LVL,SUBSTR(scbp,1,INSTR(scbp,'.')-1) SUCCESSOR_ITEM, PREDECESSOR_ITEM,CUMULATIVE_FACTOR
 FROM
  (
   SELECT LEVEL LVL,DMDUNIT PREDECESSOR_ITEM, SCALING_FACTOR,
   LTRIM(SYS_CONNECT_BY_PATH(DMDUNIT,'.'),'.') ||'.' scbp,
   eval(SYS_CONNECT_BY_PATH(SCALING_FACTOR,'/')) CUMULATIVE_FACTOR
   FROM STAGING.VW_STG_DFU
   START WITH VW_STG_DFU.LVL='1'
   CONNECT BY PRIOR PREDCSR=DMDUNIT
   )
ORDER BY 1 DESC) B
WHERE (A.SUCCESSOR_ITEM=B.SUCCESSOR_ITEM(+)) and (A.LVL-1 = B.LVL(+))



Any suggestions what could be the reason that we are getting 0 Rows when we run in 10g.What things can we look into...

Thanks

[Updated on: Thu, 26 March 2009 15:09]

Report message to a moderator

Re: Query in 9i & 10g [message #394402 is a reply to message #394396] Thu, 26 March 2009 16:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
VW_STG_DFU.LVL='1'






regards,
oli

[Updated on: Thu, 26 March 2009 16:43]

Report message to a moderator

Re: Query in 9i & 10g [message #394404 is a reply to message #394402] Thu, 26 March 2009 17:38 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks, Agreed that it's a number and we are making that a string, But removing '' didn't changed the output, Still there are 0 Rows when i run in 10g and IT fetches rows when i run in 9i Version.


Thanks
Re: Query in 9i & 10g [message #394406 is a reply to message #394396] Thu, 26 March 2009 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Here is the most basic question.

faiz_hyd,
Do you think what you are seeing is a
Oracle bug
or
pilot problem?

>The same query works in 9i and gets rows,
How many rows from 9i?

Provide proof that the data is the same in both versions.
Re: Query in 9i & 10g [message #394519 is a reply to message #394406] Fri, 27 March 2009 10:16 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks all for the time,
Please see that there are records in both the Versions,
But still it gets 0 rows in 10g,
Second thing is if i remove this line from the query in 10g version it works " START WITH VW_STG_DFU.LOC='1'" Looks like it is some thing to with START WITH .. Not sure

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select count(*) from VW_STG_DFU;

  COUNT(*)
----------
     19281

SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> conn staging@scmdsd
Enter password: *********
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select count(*) from VW_STG_DFU;

  COUNT(*)
----------
     26314



Thanks
Re: Query in 9i & 10g [message #394539 is a reply to message #394519] Fri, 27 March 2009 13:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
faiz_hyd wrote on Fri, 27 March 2009 11:16

Second thing is if i remove this line from the query in 10g version it works " START WITH VW_STG_DFU.LOC='1'"


I do not see that anywhere in your query.
Re: Query in 9i & 10g [message #394549 is a reply to message #394539] Fri, 27 March 2009 14:18 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Joy for follow up,

you don't see that because i didn't posted that,
what i wanted to say is , after some workarounds and multiple try's , i removed that START with on 10g Version DB and ran the same query it Runs and gets the rows,
Which makes me to think that START WITH could be the culprit.

And another what i have seen in 10g Database setting is, The value of compatible parameter is 10.2.0.4.0.

should it be some thing to 9.2.0.8 , IF so can we alter it on without rebooting to test.

Thanks
Re: Query in 9i & 10g [message #394562 is a reply to message #394549] Fri, 27 March 2009 15:23 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems your colleague is also concerned with the same issue Wink
http://www.orafaq.com/forum/t/142792/42800/
I cannot reproduce the case with 10.2.0.1.0.
But I had already set the hidden parameter (For a similar issue).

[Updated on: Fri, 27 March 2009 15:24]

Report message to a moderator

Previous Topic: difference between dropping and unusable index
Next Topic: URGENT: ORA-24778 - Cannot open connections error
Goto Forum:
  


Current Time: Sat Dec 03 05:49:27 CST 2016

Total time taken to generate the page: 0.13138 seconds