Home » SQL & PL/SQL » SQL & PL/SQL » Same SQL script, same login, different workstation, different results!
Same SQL script, same login, different workstation, different results! [message #189466] Thu, 24 August 2006 13:32 Go to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Good afternoon.

I've returned to this invaluable place to see if anyone can shed some light on a somewhat baffling scenario.

This is regarding Oracle 8i v 8.1.7 and SQL*Plus v. 8.1.7

There is a rather long SQL script that is saved to a file and called from within SQL*Plus. The initial result dataset was suspect but there didn't seem to be a problem with the syntax or logic. Two minor changes were made and the file was rerun from another workstation and the result set was much larger than the initial result set. The minor changes that were made could not have produced this change so the original unmodified file was run on this second workstation only to find that the results were almost identical to the second larger set and not at all like the initial set. Just to make sure that we were not hallucinating, we ran both the initial SQL script file and the modified SQL script file on the original workstation and again received the smaller dataset.

After rerunning the files on several other workstations, we are certain that the original workstation is where the problem lies. The question is what should we be looking for? What would make a client workstation produce different results when the SQL file and the login and password is exactly the same?

The file is rather larger but the criteria mainly deals with date comparisons (latest transaction date on or after 1/1/2006) and transaction amount comparisons (largest transaction amount between 10000 and 99999.99).

Any light you can shed on this issue would be greatly appreciated!

Sincerely,
Karen

[Updated on: Thu, 24 August 2006 13:50]

Report message to a moderator

Re: Same SQL script, same login, different workstation, different results! [message #189472 is a reply to message #189466] Thu, 24 August 2006 14:44 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Let's see the query!

Many factors can be affecting this. Your local glogin.sql or login.sql may have something in there, escpecially if it's setting your NLS_DATE_FORMAT and you are using a date in your query. You are using dates in your query as DATEs, right, not trying to compare DATEs with strings?
Re: Same SQL script, same login, different workstation, different results! [message #189475 is a reply to message #189472] Thu, 24 August 2006 14:52 Go to previous messageGo to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Thanks so very much for your reply! I'm barking up that tree as we speak as that is a huge suspicion of mine. I've copied a portion of the query as the main file has many similar ones to this:

It's not the most elegant of scripts but the point is that it works and produces markedly different results on every other workstation that we run it on:

/
UPDATE ADDITIONAL_DEMOGRAPHICS AD SET AD.mcode3 = 'D1'
WHERE AD.mcode3 = 'TEMP' AND AD.IDNUMBER IN
(SELECT G.IDNUMBER FROM
GIFT_VIEW_SC G WHERE
G.IDNUMBER = AD.idnumber AND
G.TRANTYPE <> 'E-P' and
g.usercode1 not in ('110','107')
GROUP BY G.IDNUMBER
HAVING MAX(TO_DATE(G.GIFTDATE)) BETWEEN TO_DATE('01-JAN-1996', 'DD-MON-YYYY') AND
TO_DATE('16-AUG-2006', 'DD-MON-YYYY')) AND
AD.IDNUMBER IN
(SELECT G.IDNUMBER FROM
GIFT_VIEW_SC G WHERE
G.IDNUMBER = ad.idnumber AND
G.TRANTYPE <> 'E-P' and
(g.usercode1 not in ('110','107') OR
G.USERCODE1 IS NULL) AND
G.GIFTAMOUNT < 1000 AND
g.giftdate =(SELECT Max(ga.giftDATE)
FROM gift_VIEW_SC ga WHERE g.IDNUMBER=ga.IDNUMBER) AND
g.TRANSNUM= (SELECT Max(ga2.TRANSNUM) FROM gift_VIEW_SC ga2 WHERE
ga2.IDNUMBER=g.IDNUMBER AND ga2.giftDATE=
(SELECT Max(ga3.giftDATE) FROM gift_VIEW_SC ga3 WHERE ga2.IDNUMBER=ga3.IDNUMBER)))
AND
EXISTS
(SELECT G.IDNUMBER FROM
GIFT_VIEW_SC G WHERE
G.IDNUMBER = AD.idnumber AND
G.TRANTYPE <> 'E-P' and
g.usercode1 not in ('110','107')
GROUP BY G.IDNUMBER
HAVING MAX (G.giftAMOUNT) BETWEEN 10000 AND 99999.99)
AND AD.IDNUMBER NOT IN
( select tv.idnumber
from tracking_view tv
where tv.idnumber = ad.idnumber
and tv.usernumber1 is not null )
/

I'm currently having someone run and compare the results of this query on the problem workstation to one of the other workstations to see if we see anything.

/
select substr(parameter,1,20), substr(value,1,35) from v$nls_parameters
/

Is there anything else you'd suggest I look for?

Thanks again!
Re: Same SQL script, same login, different workstation, different results! [message #189498 is a reply to message #189475] Thu, 24 August 2006 19:00 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
maybe the tnsnames files on the machines are different?

select instance_name, host_name, startup_time from v$instance;
select ('userenv', 'DB_NAME'), sys_context('userenv', 'SESSION_USER'),sys_context('userenv', 'CURRENT_SCHEMA') from dual;

[Updated on: Thu, 24 August 2006 19:02]

Report message to a moderator

Re: Same SQL script, same login, different workstation, different results! [message #189766 is a reply to message #189466] Sat, 26 August 2006 17:56 Go to previous message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Thanks for brainstorming with me! It turned out to indeed be the NLS_DATE_FORMAT. Thanks again!
Previous Topic: Oracle Management Server - won't login
Next Topic: Procedure locks (merged)
Goto Forum:
  


Current Time: Wed Dec 07 06:44:05 CST 2016

Total time taken to generate the page: 0.05131 seconds