query returns different results after udate to sp4

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query returns different results after udate to sp4

Author

Topic

sub4
Starting Member

3 Posts

Posted - 2007-08-23 : 11:34:12

i'm new to this forum - hopefully i can get som help form you for my problem:

my application uses a query that worked fine for years. my client now upgraded sqlserver 2000 from sp3 to sp4+hotfixes (8.0.2189). then they reported an error in my application.

i analyzed the problem and found out, that the query returns rows in sp3 but no rows in sp4. the execution plan is different too.

i can post the query, but without all of the tables it doesnt't make much sense.

my question is, if there is a well known problem (googeling didn't help) or any general advice that i could try?

thanks in advance for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 11:55:42

Post the query.

E 12°55'05.25"
N 56°04'39.16"

sub4
Starting Member

3 Posts

Posted - 2007-08-23 : 13:25:40

ok, here is the query:

SELECT DISTINCT A.ParNo, A.ParID, A.Description, A.EngineerUnit, E.MinValue, E.DefaultValue, E.Maxvalue, A.ParNo ParNoRef, A.ParID ParIDRef, A.Description DescriptionRef, A.EngineerUnit EngineerUnitRef, E.MinValue MinValueRef, E.DefaultValue DefaultValueRef, E.Maxvalue MaxvalueRef
FROM ProcessParameters A
INNER JOIN PhEP_UseDefault E ON A.ParNo=E.ParNo AND E.EntityNo=434 AND E.PhNo=7223
INNER JOIN ClassParameters C ON A.ParNo=C.ParNo
WHERE C.ClassNo=202
AND (EXISTS (SELECT * FROM PhLogic L
WHERE (L.Par1 = 'F'+CAST(A.ParNo AS nvarchar) OR L.Par2 = 'F'+CAST(A.ParNo as nvarchar))
AND L.Step IN (-1,100,100,100,100,200,200,200,200,210,210,210,220,220,220,230,230,230,230,230,240,240,240,250,250,250,250,250,270,270,270,290,290,290,5000,5000,5000,5000,5000,5010,5010,5010,5010,5010,5020,5020,5020,5020,6010,6010,6010,6100,6100,6100,6100,6100,6100,6100)
AND L.PhNo=7223)
OR EXISTS (SELECT * FROM PhaseReports PA WHERE PA.ReportName = 'F'+CAST(A.ParNo AS Nvarchar) AND PA.PhNo=7223)
OR EXISTS (SELECT * FROM PhaseAlarms PA
WHERE PA.PhAlarmName='F'+CAST(A.ParNo AS Nvarchar) AND PA.PhNo=7223))
ORDER BY A.ParID

While playing arround i found out that making some minor changes makes the query work:

- replacing "=" with "like" when comparing the "CAST"-expressions
or
- removing one of the EXISTS-subqueries (no matter which)
or
- removing one of the OR-Clauses in "(L.Par1 = 'F'+CAST(A.ParNo AS nvarchar) OR L.Par2 = 'F'+CAST(A.ParNo as nvarchar)) "

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 14:22:16

[code]SELECT DISTINCT A.ParNo,
A.ParID,
A.Description,
A.EngineerUnit,
E.MinValue,
E.DefaultValue,
E.Maxvalue,
A.ParNo AS ParNoRef,
A.ParID AS ParIDRef,
A.Description AS DescriptionRef,
A.EngineerUnit AS EngineerUnitRef,
E.MinValue AS MinValueRef,
E.DefaultValue AS DefaultValueRef,
E.Maxvalue AS MaxvalueRef
FROM ProcessParameters AS A
INNER JOIN PhEP_UseDefault as E ON A.ParNo=E.ParNo AND E.EntityNo = 434 AND E.PhNo = 7223
INNER JOIN ClassParameters as C ON C.ParNo = A.ParNo AND C.ClassNo=202
WHERE EXISTS (
SELECT *
FROM PhLogic AS L
WHERE 'F' + CONVERT(nvarchar, A.ParNo) IN (L.Par1, L.Par2)
AND L.Step IN (-1, 100, 200, 210, 220, 230, 240, 250, 270, 290, 5000, 5010, 5020, 6010, 6100)
AND L.PhNo = 7223
)
OR EXISTS (
SELECT *
FROM PhaseReports AS PA
WHERE PA.ReportName = 'F' + CONVERT(Nvarchar, A.ParNo)
AND PA.PhNo = 7223
)
OR EXISTS (
SELECT *
FROM PhaseAlarms AS PA
WHERE PA.PhAlarmName = 'F' + CONVERT(nvarchar, A.ParNo)
AND PA.PhNo = 7223
)
ORDER BY a.ParID[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 14:23:34

cast(a.parno as nvarchar)

truncates after first character

E 12°55'05.25"
N 56°04'39.16"

sub4
Starting Member

3 Posts

Posted - 2007-08-23 : 14:33:55

thanks, but did not work (i tried convert before).

what do you mean with "truncates after first character"?
select cast(12345 as nvarchar) returns "12345"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources