CASE WHEN MAX - 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

General SQL Server Forums

New to SQL Server Programming

CASE WHEN MAX

Author

Topic

tictoc
Starting Member

7 Posts

Posted - 2011-03-11 : 13:02:24

Normaly I use
CASE WHEN RPT_CMP_RIT_RAW.[233Windows Server 2003 (DSI)] IS NULL THEN 'not selected'
else '' + RPT_CMP_RIT_RAW.[233Windows Server 2003 (DSI)]END AS 'PF Windows Server 2003 (DSI)' ,

pretty straight forward and works everytime but now I have to use MAX

CASE WHEN MAX([11CTC_classification]) IS NULL THEN 'NOT PROVIDED'
else '' + MAX([11CTC_classification])
END AS 'CTC CLASSIFICATION' ,
And my Value I am trying to putin 'NOT PROVIDED'
does not come through. What is the MAX doing to me?

Also I am trying to do this on two columns
CASE WHEN MAX([11CTC_classification],[11CTC_classification])
else '' + MAX([11CTC_classification],[11CTC_classification])
And it's protesting about it. What is wrong?

thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-12 : 22:20:40

>> pretty straight forward and works every time but now I have to use MAX <

Actually it is a nightmare. You put display text in square brackets, data element names that begin with numerals to destroy portability. You have no idea about ISO-11179 rules. This is 1950's COBOL, where data, process and display were all in the monolithic program. Also why is the keyword ELSE is lowercase? Why did you use the term “_classification” which is a collective noun, instead of the correct “-class”, which is a scalar that will fit into a column ?

The expression “'' + MAX(ctc_class)” does nothing; that is the definition of the empty string and concatenation.

CASE WHEN MAX(ctc_class) IS NULL
THEN 'not provided'
ELSE MAX(ctc_class)
END AS ctc_class

might be as simple as COALESCE (MAX(ctc_class), 'not provided') if you want to violate a tiered architecture.

>> What is wrong? <<

Your approach to SQL programming :)

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

tictoc
Starting Member

7 Posts

Posted - 2011-03-13 : 01:50:20

First. I know from reading you other posts your a gruff or just a pent up person so I look beyond that and accept it as is and appreciate the helpfull infomation you have given me.

One the other hand in an enterprise enviroment no one single person has full control of a database. In this instance the data is Imported from an even Larger nightmare. 12 million rows of data in a text file. The numbers are from them not from me and so are the names. I just use what I have and plan out a better version of the database. A nd some of your tips will be asked of our MS rep next week furing the review.
And if one of my engineers sat around complaining this was not ISO or that all day I would fire him. Nothing personal just how it is. Everything costs money and must be justified to the penny.

So thank you for the help I appreciate it!!

Oh.. 1950's COBAL?? Now your making me feel old! Maybe that could be your next book

SQL FOR OLD PEOPLE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources