Home » Applications » PeopleSoft, JD Edwards & Siebel » sql query (10.2)
sql query [message #306165] Thu, 13 March 2008 01:50 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I am from Peoplesoft capability. We are experiencing an issue in Oracle 10.2.

Recently we upgraded Peoplesoft 8.3 (Oracle database 8i) to Peoplesoft 8.9 (Oracle database 10.2).

We have been using following query in Oracle database 8i and getting result
SELECT 
TB1.COMPANY 
,TB1.EMPLID         
,TB1.STATE 
,TB1.LOCALITY 
,TB1.TAX_CLASS 
,TB1.TXGRS_YTD 
,TB1.NLGRS_YTD 
,TB1.TAX_YTD 
,T1.BOX 
,T1.AMOUNT_TYPE 
FROM  PS_TAX_BALANCE TB1, PS_TAXFORM_TAX T1, 
      PS_W2_COMPANY C1 
WHERE C1.W2_REPORTING_CO = 'HEU' 
  AND C1.CALENDAR_YEAR   = 2006 
  AND T1.TAXFORM_ID      = 'W' 
  AND T1.EFFDT           =  '01-JAN-2006' 
  AND T1.TAX_CLASS IN ('H','D','E','F','G','J','Q','T','Z','C','K','V','L','M','N','W') 
  AND TB1.EMPLID = '1031026' 
  AND TB1.COMPANY         = C1.COMPANY 
  AND TB1.BALANCE_ID      = 'CY' 
  AND TB1.BALANCE_YEAR    = 2006 
  AND T1.TAX_CLASS       = TB1.TAX_CLASS 
  AND ( (T1.JURISDICTION_TYPE = 'F' AND TB1.STATE  LIKE '$U%') 
    OR  (  (T1.JURISDICTION_TYPE = 'S' 
            AND TB1.STATE <> '$U' 
            AND TB1.LOCALITY  = ' ') 
      AND  (TB1.STATE = T1.STATE OR T1.STATE = ' ') ) 
    OR  (  (T1.JURISDICTION_TYPE = 'L' 
            AND TB1.STATE <> '$U' 
            AND TB1.LOCALITY <> ' ') 
      AND  (TB1.STATE = T1.STATE OR T1.STATE = ' ') 
      AND  (TB1.LOCALITY = T1.LOCALITY or T1.LOCALITY = ' ') ) ) 
  AND TB1.BALANCE_PERIOD = 
      (SELECT MAX(BALANCE_PERIOD) 
       FROM   PS_TAX_BALANCE 
       WHERE  EMPLID        = TB1.emplid 
         AND  COMPANY       = TB1.company 
         AND  BALANCE_YEAR = TB1.balance_year 
         AND  BALANCE_ID = TB1.balance_id 
         AND  STATE         = TB1.state 
         AND  LOCALITY     = ' ' 
         AND  TAX_CLASS     = TB1.tax_class) 
  AND (TB1.TXGRS_YTD <> 0  OR  TB1.TAX_YTD <> 0) 
 ORDER BY TB1.EMPLID, TB1.STATE, TB1.LOCALITY, TB1.TAX_CLASS,TB1.COMPANY


But if execute the same query in Oracle 10.2, we are not getting result. But if we make following modification in the query in the WHERE clause of subquery

we are getting result in 10.2 as well.

WHERE to_char(EMPLID) = to_char(tb1.emplid)
AND to_char(COMPANY) = to_char(tb1.company)
AND to_number(BALANCE_YEAR) = to_number(tb1.balance_year)
AND to_char(BALANCE_ID) = to_char(tb1.balance_id)
AND to_char(STATE) = to_char(tb1.state)
AND to_char(LOCALITY) = ' '
AND to_char(TAX_CLASS) = to_char(tb1.tax_class))


Peoplesoft programs are delivered without using To_char and To_Number functions hence many of the delivered programs are not working now.
Can some one help me to fix this without modifying the programs?( by changing some setting in the database or if any patch needs to be applied etc)

Let me know if you require more details.

Thank you

[Updated on: Thu, 13 March 2008 01:54]

Report message to a moderator

Re: sql query [message #306178 is a reply to message #306165] Thu, 13 March 2008 02:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you please post the structure of the tables used in your select statement ?

Regards

Raj
Re: sql query [message #306189 is a reply to message #306178] Thu, 13 March 2008 03:38 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

I don't know. I receive this query from others.... I saw this query in some other forum...

Thank you
Previous Topic: 3rd Party scheduling of PeopleSoft jobs / processes
Next Topic: JD Edwards Integration
Goto Forum:
  


Current Time: Fri Dec 09 04:24:32 CST 2016

Total time taken to generate the page: 0.13185 seconds