Error with case statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Error with case statement

Author

Topic

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 16:55:05

I am moving a DB form one instance of SQL2005 to another and am having a problem with on of the views. The problem seems to be with the following 3 statements

CASE WHEN (dbo.UnitResult.Source = 'Combined') THEN CASE WHEN (dbo.UnitResult.fPassed = 1 AND dbo.OffJobUnitResult.fPassed = 1) THEN CASE WHEN (dbo.UnitResult.vCreationTime < dbo.OffJobUnitResult.vCreationTime) THEN dbo.OffJobUnitResult.vCreationTime ELSE dbo.Max(dbo.UnitResult.fDateCompleted , dbo.Max(dbo.UnitResult.vCreationTime , dbo.OffJobUnitResult.fDateCompleted)) END ELSE NULL END ELSE dbo.UnitResult.vCreationTime END

CASE WHEN dbo.UnitResult.Source = 'Combined' THEN CASE WHEN dbo.UnitResult.fPassed = 1 AND dbo.OffJobUnitResult.fPassed = 1 THEN dbo.Max(dbo.UnitResult.fDateCompleted , dbo.OffJobUnitResult.fDateCompleted) ELSE NULL END ELSE dbo.UnitResult.fDateCompleted END

CASE WHEN (dbo.UnitResult.Source = 'Combined') THEN CASE WHEN (dbo.UnitResult.fPassed = 1 AND dbo.OffJobUnitResult.fPassed = 1) THEN dbo.Max(dbo.UnitResult.fDateEntered , dbo.OffJobUnitResult.fDateEntered) ELSE NULL END ELSE dbo.UnitResult.fDateEntered END

With an error coming up around the dbo.Max part of the statements.

I am transferring the DB from an older installed instance of SQL2005 am unsure why the view works fine in the older instance and not the new.

Can anyone offer a correction to the statements so that they will work in the newer version?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 17:12:14

Could you post the exact error and perhaps the view definition?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 17:12:38

Also, what does SELECT @@VERSION show for both instances? And what is the database compatibility level for both databases?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 17:26:30

Error is "cannot find either column "dbo" or the user-defined function aggregate "dbo.max", or the name is ambiguous"

Do you mean the view code?

This is the one it doesn't work on
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

This is the one it does work on
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Can see that the service packs are different which i thought it might be, but didn't know how to check

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 17:28:27

Oh and the view was originally done in SQL2000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 17:29:38

You can't use dbo.Max since Max is a column. Just use Max or tableName.Max or Alias.Max.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 17:46:53

If i use just MAX then i get the Error "The MAX function requires 1 argument"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 17:50:38

Put square brackets around it. You've named a column the same as a reserved word; MAX is a built-in SQL function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 17:59:32

I'm using MAX as a function as in the following statment it is bringing back the max date of one of the columns in the table

dbo.Max(dbo.UnitResult.fDateEntered , dbo.OffJobUnitResult.fDateEntered)

Below is the full view. (Note. I didn't write it)

SELECT dbo.Unit.fMyTrainee, dbo.Trainee.fHOID AS [fHoid Trainee], dbo.Unit.UnitOid AS [Oid Unit], dbo.Unit.fHOID AS [fHoid Unit],
dbo.UnitResult.fMyAgreement AS fMyAgreementFinal,
CASE dbo.UnitResult.Source WHEN 'Combined' THEN (dbo.UnitResult.fPassed & dbo.OffJobUnitResult.fPassed)
ELSE dbo.UnitResult.fPassed END AS Passed,
CASE dbo.UnitResult.Source WHEN 'On Job' THEN dbo.UnitResult.fPassed WHEN 'Combined' THEN dbo.UnitResult.fPassed ELSE NULL
END AS [Passed OnJob], CASE dbo.UnitResult.Source WHEN 'On Job' THEN NULL
WHEN 'Combined' THEN dbo.OffJobUnitResult.fPassed ELSE dbo.UnitResult.fPassed END AS [Passed OffJob],
dbo.UnitStandard.fLevel AS [Credit Level], dbo.UnitStandard.fCredit AS [Credits Achieved], CASE WHEN (dbo.UnitResult.Source = 'Combined')
THEN CASE WHEN (dbo.UnitResult.fPassed = 1 AND dbo.OffJobUnitResult.fPassed = 1)
THEN CASE WHEN (dbo.UnitResult.vCreationTime < dbo.OffJobUnitResult.vCreationTime)
THEN dbo.OffJobUnitResult.vCreationTime ELSE dbo.Max(dbo.UnitResult.fDateCompleted, dbo.Max(dbo.UnitResult.vCreationTime,
dbo.OffJobUnitResult.fDateCompleted)) END ELSE NULL END ELSE dbo.UnitResult.vCreationTime END AS [Date Created],
CASE dbo.UnitResult.Source WHEN 'Off Job' THEN NULL ELSE dbo.UnitResult.vCreationTime END AS [OnJob Created],
CASE dbo.UnitResult.Source WHEN 'Off Job' THEN dbo.UnitResult.vCreationTime WHEN 'Combined' THEN dbo.OffJobUnitResult.vCreationTime ELSE NULL
END AS [OffJob Created], dbo.UnitStandard.fNumber AS [Unit Number], dbo.UnitStandard.fName AS [Unit Name], dbo.UnitResult.Source,
CASE WHEN dbo.UnitResult.Source = 'Combined' THEN CASE WHEN dbo.UnitResult.fPassed = 1 AND
dbo.OffJobUnitResult.fPassed = 1 THEN dbo.Max(dbo.UnitResult.fDateCompleted, dbo.OffJobUnitResult.fDateCompleted) ELSE NULL
END ELSE dbo.UnitResult.fDateCompleted END AS [Date Completed], CASE dbo.UnitResult.Source WHEN 'Off Job' THEN NULL
ELSE dbo.UnitResult.fDateCompleted END AS [OnJob Completed],
CASE dbo.UnitResult.Source WHEN 'Off Job' THEN dbo.UnitResult.fDateCompleted WHEN 'Combined' THEN dbo.OffJobUnitResult.fDateCompleted ELSE
NULL END AS [OffJob Completed], dbo.UnitResult.fPriorResult AS [Prior Result],
CASE dbo.UnitResult.Source WHEN 'Combined' THEN CASE WHEN dbo.OffJobUnitResult.fMyAssessor <> '00000.0000000000' THEN dbo.OffJobUnitResult.fMyAssessor
ELSE dbo.UnitResult.fMyAssessor END ELSE dbo.UnitResult.fMyAssessor END AS fMyAssessor,
CASE dbo.UnitResult.Source WHEN 'Combined' THEN CASE WHEN dbo.OffJobUnitResult.fMyProvider <> '00000.0000000000' THEN dbo.OffJobUnitResult.fMyProvider
ELSE dbo.UnitResult.fMyProvider END ELSE dbo.UnitResult.fMyProvider END AS fMyProvider, dbo.UnitResult.fMyCombinedUnitResult,
dbo.UnitResult.fMyOffJobUnitResult, dbo.UnitResult.fMyCourse, CASE WHEN (dbo.UnitResult.Source = 'Combined')
THEN CASE WHEN (dbo.UnitResult.fPassed = 1 AND dbo.OffJobUnitResult.fPassed = 1) THEN dbo.Max(dbo.UnitResult.fDateEntered,
dbo.OffJobUnitResult.fDateEntered) ELSE NULL END ELSE dbo.UnitResult.fDateEntered END AS [Date Entered],
CASE dbo.UnitResult.Source WHEN 'Off Job' THEN NULL ELSE dbo.UnitResult.fDateEntered END AS [OnJob Entered],
CASE dbo.UnitResult.Source WHEN 'Off Job' THEN dbo.UnitResult.fDateEntered WHEN 'Combined' THEN dbo.OffJobUnitResult.fDateEntered ELSE NULL
END AS [OffJob Entered],
CASE dbo.UnitResult.Source WHEN 'On Job' THEN dbo.UnitResult.fmyAgreement WHEN 'Combined' THEN dbo.UnitResult.fmyAgreement ELSE NULL
END AS [OnJob Agreement], CASE dbo.UnitResult.Source WHEN 'On Job' THEN NULL
WHEN 'Combined' THEN dbo.OffJobUnitResult.fmyAgreement ELSE dbo.UnitResult.fmyAgreement END AS [OffJob Agreement]
FROM dbo.UnitStandard RIGHT OUTER JOIN
dbo.Unit ON dbo.UnitStandard.UnitStandardOid = dbo.Unit.fMyUnitStandard LEFT OUTER JOIN
dbo.Trainee ON dbo.Unit.fMyTrainee = dbo.Trainee.TraineeOid RIGHT OUTER JOIN
dbo.UnitResult ON dbo.Unit.UnitOid = dbo.UnitResult.fMyUnit LEFT OUTER JOIN
dbo.OffJobUnitResult ON dbo.UnitResult.fMyOffJobUnitResult = dbo.OffJobUnitResult.OffJobUnitResultOid

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 18:02:06

This is so confusing. According to the error, dbo.Max doesn't exist as a user-defined function, hence the error. Does dbo.Max exist on the working server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 18:28:09

No dbo.max does not exist on the working server. I have just done a Select * from dbo.[Max] on the old 2005 instance and the 2000 instance

I'm sure it has something to do with the fact that the original veiw was written in SQL2000 and that something with service pack 2 on the latest instance has caused this to now become a problem.

I agree with you that it's confusing. My other option is to re-write the view though had been trying to avoid this due to the complexity

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 18:33:23

Well your function isn't a table function so that query wouldn't work. Try SELECT dbo.[Max](InputParm1, InputParm2, ...)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 18:52:58

No luck

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-16 : 19:00:18

Well it has to exist in the working one, otherwise you would get an error there too.

SELECT * FROM dbNameGoesHere.sys.objects WHERE [name] = 'Max'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 19:46:09

I've run your statment in the old instance and get the following row returned. I get nothing reurned in the new version

I'm not sure what this means

Max 1221579390 NULL 1 0 FN SQL_SCALAR_FUNCTION 2003-11-04 22:08:56.780 2003-11-04 22:08:56.780 0 0 0

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-16 : 19:51:14

You need to have that scalar function copied in new instance with appropriate permission.

sparrow
Starting Member

20 Posts

Posted - 2008-12-16 : 19:58:42

I've found the dbo.max under progammability - Functions - scalar-value Functions of the old version

Now i just need to copy it to the new version

Thanks for all your help. I've learnt something new today

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources