Solved

Weird Sql Question !!

Posted on 2002-07-11
24
2,447 Views
Last Modified: 2012-06-22
Ok this is bizarre. A 'Buisiness Objects' report running against an Oracle Applications Tables on an Oracle 8.1.6.1 (EE) server returns rows when submitted from some PC's and not from others. I can't understand why. Code attached. Any suggestions welcome !!

SELECT
  OFBO.PA_PROJECTS_ALL.NAME,
  decode(PO.PO_HEADERS_ALL.SEGMENT1,
  null,
  AP.AP_INVOICE_DISTRIBUTIONS_ALL.ATTRIBUTE1,
  decode(Line_Releases.RELEASE_NUM,
  null,
  PO.PO_HEADERS_ALL.SEGMENT1,
  PO.PO_HEADERS_ALL.SEGMENT1 || '-' || Line_Releases.RELEASE_NUM)),
  (PO.PO_VENDORS.VENDOR_NAME || ' - ' || PO.PO_VENDORS.SEGMENT1),
  PO.PO_HEADERS_ALL.AUTHORIZATION_STATUS,
  TRUNC(PO.PO_HEADERS_ALL.CREATION_DATE),
  upper(decode(INV.MTL_SYSTEM_ITEMS.SEGMENT1,
  null,
  AP.AP_INVOICE_DISTRIBUTIONS_ALL.ATTRIBUTE11,
  INV.MTL_SYSTEM_ITEMS.SEGMENT1)),
  AP.AP_INVOICES_ALL.EXPENDITURE_TYPE,
  PO.PO_HEADERS_ALL.CURRENCY_CODE,
  OFBO.GL_CODE_COMBINATIONS.SEGMENT1 || '-' || OFBO.GL_CODE_COMBINATIONS.SEGMENT2 || '-' || OFBO.GL_CODE_COMBINATIONS.SEGMENT3 || '-' || OFBO.GL_CODE_COMBINATIONS.SEGMENT4,
  decode(PO.PO_LINES_ALL.LINE_NUM,
  null,
  to_number(AP.AP_INVOICE_DISTRIBUTIONS_ALL.ATTRIBUTE2),
  PO.PO_LINES_ALL.LINE_NUM),
  sum(nvl(AP.AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT,
  0)),
  decode(PO.PO_LINE_TYPES.LINE_TYPE,
  'Services',
  ( PO.PO_LINES_ALL.QUANTITY ),
  (PO.PO_LINES_ALL.UNIT_PRICE)) * decode(PO.PO_LINE_TYPES.LINE_TYPE,
  'Services',
  PO.PO_LINES_ALL.UNIT_PRICE,
  (PO.PO_DISTRIBUTIONS_ALL.QUANTITY_ORDERED - PO.PO_DISTRIBUTIONS_ALL.QUANTITY_CANCELLED))
FROM
  OFBO.PA_PROJECTS_ALL,
  PO.PO_HEADERS_ALL,
  AP.AP_INVOICE_DISTRIBUTIONS_ALL,
  OFBO.PO_RELEASES  Line_Releases,
  PO.PO_VENDORS,
  INV.MTL_SYSTEM_ITEMS,
  AP.AP_INVOICES_ALL,
  OFBO.GL_CODE_COMBINATIONS,
  PO.PO_LINES_ALL,
  PO.PO_DISTRIBUTIONS_ALL,
  PO.PO_LINE_TYPES,
  OFBO.PA_PROJECT_PLAYERS,
  OFBO.PER_PEOPLE_F,
  OFBO.HR_EMPLOYEES,
  PO.PO_LINE_LOCATIONS_ALL
WHERE
  ( OFBO.PA_PROJECTS_ALL.PROJECT_ID=OFBO.PA_PROJECT_PLAYERS.PROJECT_ID  )    AND
  ( OFBO.PA_PROJECT_PLAYERS.PERSON_ID=OFBO.PER_PEOPLE_F.PERSON_ID  )    AND
  ( PO.PO_HEADERS_ALL.VENDOR_ID=PO.PO_VENDORS.VENDOR_ID(+)  )    AND
  ( PO.PO_DISTRIBUTIONS_ALL.PO_HEADER_ID=PO.PO_HEADERS_ALL.PO_HEADER_ID(+)  )    AND
  ( PO.PO_DISTRIBUTIONS_ALL.PO_LINE_ID=PO.PO_LINES_ALL.PO_LINE_ID(+)  )    AND
  ( OFBO.PA_PROJECTS_ALL.PROJECT_ID=PO.PO_DISTRIBUTIONS_ALL.PROJECT_ID(+)  )    AND
  ( PO.PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID=AP.AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+)  )    AND
  ( AP.AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID=AP.AP_INVOICES_ALL.INVOICE_ID(+)  )    AND
  ( PO.PO_HEADERS_ALL.AGENT_ID=OFBO.HR_EMPLOYEES.EMPLOYEE_ID(+)  )    AND
  ( PO.PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID=OFBO.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID(+)  )    AND
  ( PO.PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID=Line_Releases.PO_RELEASE_ID(+)  )    AND
  ( PO.PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID=PO.PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID(+)  )    AND
  ( PO.PO_LINES_ALL.ITEM_ID=INV.MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID(+)  )    AND
  ( PO.PO_LINES_ALL.LINE_TYPE_ID=PO.PO_LINE_TYPES.LINE_TYPE_ID  )    AND
  OFBO.PA_PROJECTS_ALL.NAME  = '26204-B767-004 Redelivery/Deli'
GROUP BY    OFBO.PA_PROJECTS_ALL.NAME,     decode(PO.PO_HEADERS_ALL.SEGMENT1, null, AP.AP_INVOICE_DISTRIBUTIONS_ALL.ATTRIBUTE1, decode(Line_Releases.RELEASE_NUM, null, PO.PO_HEADERS_ALL.SEGMENT1, PO.PO_HEADERS_ALL.SEGMENT1 || '-' || Line_Releases.RELEASE_NUM)),     (PO.PO_VENDORS.VENDOR_NAME || ' - ' || PO.PO_VENDORS.SEGMENT1),     PO.PO_HEADERS_ALL.AUTHORIZATION_STATUS,     TRUNC(PO.PO_HEADERS_ALL.CREATION_DATE),     upper(decode(INV.MTL_SYSTEM_ITEMS.SEGMENT1, null, AP.AP_INVOICE_DISTRIBUTIONS_ALL.ATTRIBUTE11, INV.MTL_SYSTEM_ITEMS.SEGMENT1)),     AP.AP_INVOICES_ALL.EXPENDITURE_TYPE,     PO.PO_HEADERS_ALL.CURRENCY_CODE,     OFBO.GL_CODE_COMBINATIONS.SEGMENT1 || '-' || OFBO.GL_CODE_COMBINATIONS.SEGMENT2 || '-' || OFBO.GL_CODE_COMBINATIONS.SEGMENT3 || '-' || OFBO.GL_CODE_COMBINATIONS.SEGMENT4,     decode(PO.PO_LINES_ALL.LINE_NUM, null, to_number(AP.AP_INVOICE_DISTRIBUTIONS_ALL.ATTRIBUTE2), PO.PO_LINES_ALL.LINE_NUM),     decode(PO.PO_LINE_TYPES.LINE_TYPE, 'Services',( PO.PO_LINES_ALL.QUANTITY ), (PO.PO_LINES_ALL.UNIT_PRICE)) * decode(PO.PO_LINE_TYPES.LINE_TYPE, 'Services', PO.PO_LINES_ALL.UNIT_PRICE, (PO.PO_DISTRIBUTIONS_ALL.QUANTITY_ORDERED - PO.PO_DISTRIBUTIONS_ALL.QUANTITY_CANCELLED))
0
Comment
Question by:Mick_Rice
  • 6
  • 6
  • 3
  • +6
24 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7146063
? what does this mean ?
>when submitted from some PC's and not from others

sounds like some PC's commits the data and some not
0
 

Author Comment

by:Mick_Rice
ID: 7146109
As the code merely selects I would have thought that I'd get the same set of rows returned (or no rows) no matter what client PC is used. This is not the casehere as sometimes I get a number of rows back (59) and sometimes none depending upon which client PC submits the code. The data is essentially static. I've extracted this sql code from the Business Objects report and am now submitting it independently from the 'Business Objects' reporting tool (via SQL*Plus and TOAD). I hear what you're saying about uncommitted changes in some sessions that wouldn't be visible to others but that doesn't apply here as this issue appears on PC's with no pre-existing sessions.
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 7146141
How stable is your network?

Run the Trace, etc.
0
 

Author Comment

by:Mick_Rice
ID: 7146182
OK new info....the difference between is NLS_LANGUAGE on the client but we're still not sure why ... the points are still there !!!
0
 
LVL 27

Assisted Solution

by:kretzschmar
kretzschmar earned 50 total points
ID: 7146364
maybe this causes that an entry is stored in another way,
so that the compare in your whereclause does not hit the record(s)

just a guess

meikl ;-)
0
 

Author Comment

by:Mick_Rice
ID: 7146392
This is probably the reason. With a LANGUAGE setting of AMERICAN the code returns rows. With a setting of ENGLISH the code returns no rows. AFAIK NLS_LANGUAGE affects sorting and some aspects of how dates and times are handled (AD, BC, AM, PM) etc buthow exactly this affects my code I can't work out. Can you !
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7146432
puu, no,
how will i do this,
i don't know the types nor the possible entries,
and its a long select and there may some implicit-conversions (that would be the first, i would look for)

maybe someother

meikl ;-)
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7146675
I don't have the answer (it looks like you are on your way to figuring it out with the NLS_LANGUAGE difference you noticed) - just a comment.  Queries like this one are what give consultants their job opportunities and hardware vendors their opportunites to sell bigger servers.  Any time that there is this much complexity in an SQL statement, the opportunities for ambiguity or unexpected results is quite high.  Also the performance of statements like this can be terrible.  I'm glad I usually don't have to deal with SQL statements this ugly.
0
 
LVL 1

Assisted Solution

by:Bashar
Bashar earned 200 total points
ID: 7147375
Hi,
I guess you are running Oracle Applications 11i (11.5.3, I got it from the database release!).
Let me understand something, what Apps user do you use to connect to Oracle Applications?!
Did you run it from the TOAD on different clients with the SAME username and got different results?
I don't see a reason for a query to return different results from different clients when the NLS_LANGUAGE is different!
Maybe it has something to do with the client's configuration (NET80 or SQL*NET), but this is not the case when you log on to Oracle Applications, because you don't need to install any software besides the JInitiator.
What is the Database configuration (Init.ora, Character set, etc.)?
Are you using Parallel Query option?
Is Partitioning being used?
Please provide me with those info, also please provide us with the hardware, OS configurations used.

A note for markgeer: This ugly SQL statement is one of thousands of other statements that are used in Oracle Applications 11i (One of the biggest ERP packages in the world, with about 8,200 Tables, 17,000 Packages). I have been working on this suite for a some period of time, and it is optimized and you won't believe how fast to run such queries (and even much more complicated ones) without noticing a drop in the performance (Most of the time!).
It is a matter of a good design and the utilization of the knowledge you have to make anything possible!
After all, it is your personal preference to use another way to accomplish the task!

Regards,
Bashar.
0
 

Author Comment

by:Mick_Rice
ID: 7148513
Although this query hits Applications tables it has been extracted from the Business Objects reporting tool. Although I'm not an Apllications expert I believe that connections are generally made under the user APPS, but that shouldn't have a direct bearing here. You are correct that I have run this code from different clients, under the same username and have had different results. When I issue an 'alter session set NLS_LANGUAGE = 'AMERICAN';' (change from 'ENGLISH') the result set changes. In other words with the 'AMERICAN' setting this query returns rows and with a setting of 'ENGLISH' it returns none. I'm not a newbie by any means here folks but I've never seen anything quite like this. One question that's are coming into my mind for instance is 'can NLS affect how decode works'.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 7149045
"Can NLS affect how decode works"?  I don't know, but I do know that in Oracle8.0.5 at least, "decode" was affected by the optional init*.ora parameter: cursor_sharing.  I tried setting that to the non-default value, "FORCE", and that caused ORA-00600 errors on statements that included decode.  We're on Oracle8.1.7 now and we don't have that problem anymore with decode and cursor_sharing.

I'm just letting you know that I have seen "decode" affected before by something that (to me at least) seemed totally unrelated.
0
 

Author Comment

by:Mick_Rice
ID: 7149068
I'll raise a TAR and see what Mr.Ellison's people have to say !
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:rkogelhe
ID: 7149278
Are all your date fields really date fields? Could implicit date conversion be occurring?
0
 
LVL 3

Assisted Solution

by:rkogelhe
rkogelhe earned 50 total points
ID: 7149281
Ahhh... I wanted to imply that you should check for nls problems in the dates as well...
0
 

Author Comment

by:Mick_Rice
ID: 7149368
There's only one date used in the code and its not quoted in the where clause. I've tried selecting that date back in a sqlplus window with both NLS_LANGUAGE settings in effect and I can't see any difference.

select trunc(creation_date) from po.po_headers_all;
0
 
LVL 3

Accepted Solution

by:
rkogelhe earned 50 total points
ID: 7149733
Huh... kindof thought you would have checked that. If it was me, I'd carefully go through and check for any other implicit and explicit conversions where nls can play. My gut instinct is that it isn't the decode. check to be sure you know the type of all the where conditions. worst case you can decompose it and try it under both NLSes until you find the culprit...

not a fun problem, but interesting ... i think i'll go get a guiness now :)
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7151024
Is row level security enabled in the database?


Andrew
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7368163
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7368167
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7368168
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7368170
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7368171
0
 
LVL 5

Expert Comment

by:gmyers
ID: 7368179
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9392127
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
delete & no refund
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now