Home » Developer & Programmer » Reports & Discoverer » workbook 'Show SQL' and workbook give different results
| workbook 'Show SQL' and workbook give different results [message #168375] |
Thu, 20 April 2006 02:22  |
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
|
Member |
|
|
Hi All
background (simplified)
have two views, one called suppliers and one called supplier sites. A supplier might have 0 to many sites. Both have a LAST_UPDATE field. I have created a view (SUPP_SITE) joining these two views as such
SELECT supp.*, site.*
FROM
supplier supp,
supplier_site site
WHERE supp.supplier_id = site.supplier_id(+)
which gives me 1273 rows, which is the correct result.
Problem
I need to create a workbook based on this view, with two conditions which can filter by either of the last updates. SQL for this is as follows (in the NVL function I have included
NULL, however in the actual workbook this is set to a parameter)
(note this is the actual SQL from discoverer minus the alias's)
SELECT supplier_id,
supplier_name,
supplier_last_update_date,
supplier_site_name,
site_last_update_date
FROM apps.xxoss_r_supplier_site_test
WHERE(site_last_update_date = to_date(nvl(null, site_last_update_date)))
AND(supplier_last_update_date = to_date(nvl(null, supplier_last_update_date)))
when I run this SQL in raptor it gives me 1266 rows, which you would expect (it drops off the rows where there is supplier_site, which is what you would expect it to do).
My issue is with Discoverer...when I run the workbook that I got the SQL from, it returns 1273 rows...different to what I get if I run the SQL it generates in Raptor. Effectively it is telling me it is using the above SQL, when infact it is running something like this...
SELECT supplier_id,
supplier_last_update_date,
site_supplier_id,
site_last_update_date
FROM apps.xxoss_r_supplier_site_test
WHERE supplier_last_update_date = supplier_last_update_date
AND(site_last_update_date = site_last_update_date OR site_last_update_date IS NULL)
anybody have some ideas why it does this??
cheers
cameron
|
|
|
|
| Re: workbook 'Show SQL' and workbook give different results [message #168824 is a reply to message #168375] |
Sun, 23 April 2006 08:37  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Cameron,
Actually, since there is an outer join between suppliers and sites, Discoverer query is correct...
(you are asking "give me all suppliers with their sites, if any, oh and by the way, the sites have to be updated -> if there is no site how can he figure out whether it has been updated? so, you get the null values for sites too).
So either remove the outer join from the view or create another one for this workbook (since you don't want the outer join in this case).
The remaining problem is why the show SQL part of Discoverer is not showing you the outer join, when it is in fact (correctly) using one... Since the outer join is in the view, it's a miracle in the first place that you can see the view text at all. I would expect something like "select * from view_name where column1 = x and column2 = y".
Could you please check again? Are you sure the viewtext is not in a custom folder from Discoverer or something?
Regards,
Sabine
|
|
|
|
| Re: workbook 'Show SQL' and workbook give different results [message #172137 is a reply to message #168824] |
Mon, 15 May 2006 02:19 |
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
|
Member |
|
|
Hi Sabine, thanks for the response
had put this report to the back of my mind...along with this post (which after three days of no reponse I assumed was going to stay that way).
have isolated where the problem is occuring. Can't remember how the folders where originally set up, however now it's one folder based on a view (where the outer join resides). So if you run the workbook with no parameters you get all 1278 records. Still however had a bit of an issue with how the sql statement in the workbook is working...if I use the parameters with a NVL statement, then leave both parameters blank, I get all records, even where supplier_site (the table outer joined in the view is null). If I substitute NULL in for the parameter I drop the records where supplier site is null.
Returns 1278 rows
SELECT COUNT(O344183.SUPPLIER_ID)
FROM APPS.XXOSS_R_SUPPLIER_SITE_TEST O344183
WHERE ( O344183.SUPPLIER_LAST_UPDATE_DATE = NVL(:SUPPLIER_UPDATE,O344183.SUPPLIER_LAST_UPDATE_DATE) ) AND ( O344183.SITE_LAST_UPDATE_DATE = NVL(:SITE_UPDATE,O344183.SITE_LAST_UPDATE_DATE) )
;
Returns 1271 rows
SELECT COUNT(O344183.SUPPLIER_ID)
FROM APPS.XXOSS_R_SUPPLIER_SITE_TEST O344183
WHERE ( O344183.SUPPLIER_LAST_UPDATE_DATE = NVL(:SUPPLIER_UPDATE,O344183.SUPPLIER_LAST_UPDATE_DATE) ) AND ( O344183.SITE_LAST_UPDATE_DATE = NVL(NULL,O344183.SITE_LAST_UPDATE_DATE) )
;
I think the difference is caused by null evaluation. If in the first example you enter nothing, I assume discoverer assigns the paramater a value of x (where x is something I don't know!) and you get NULL = X (in the cases where site_update is null), and this record is shown, where as if I use the second statement, you end up with NULL = NULL and this seems to return unknown (and doesn't show)
so I think (and it's a thought only based on my limited SQL undertanding) that if I can figure out what value NVL(:SITE_UPDATE,O344183.SITE_LAST_UPDATE_DATE) equates to where :SITE_UPDATE is left blank I will be able to explain the discrepency (which is the whole point, in terms of the workbook actually working it's fine 
cheers
cameron
|
|
|
|
| Re: workbook 'Show SQL' and workbook give different results [message #172157 is a reply to message #172137] |
Mon, 15 May 2006 05:14 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Cameron,
(an immediate answer to make sure you read it 
Your assumption is almost correct. But if you look at the two statements, the difference is this:
... update_date = nvl(:param,update_date) ...
... update_date = nvl(null,update_date) ...
the key to this is in the nvl function. The first statement in English is: compare update_date with paramater value, if parameter value is null/empty/not known, then use update_date instead. And update_date = update_date is obviously true. Now in the first statement, that never happens (how can the parameter value be empty?), in the second statement, that ALWAYS happens (since you state: compare null to update_date, if null happens to be null - surprise! - than use update_date instead).
That's the reason that the second statement doesn't do anything, it is always true. The first statement does do something: pick the records with a value for site update date and "no site" (outerjoin values) doesn't have a site update date....
Regards,
Sabine
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 12:30:38 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
