Query not taking alphabets - 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)

Query not taking alphabets

Author

Topic

ricky_1605
Starting Member

30 Posts

Posted - 2010-10-28 : 02:45:09

Hi guys
The stored procedure is as follows:

DECLARE @auxReportTable1 TABLE
(
value1 varchar(500), value2 varchar(500), value3 varchar(500)
)

select
value1 = max(CASE WHEN location = 'Vadodara' THEN (AllLocations.Domestic) ELSE 'NA' END),
value2 = max(CASE WHEN location = 'Vadodara' THEN (AllLocations.Owners) ELSE 'NA' END),
value3 = max(CASE WHEN location = 'Vadodara' THEN (AllLocations.Contractors) ELSE 'NA' END)

FROM AllLocations WITH (nolock)

WHERE AllLocations.DateId = @Id

GROUP BY AllLocations.Dateid

The problem is:
The data in these columns is fetched properly if the data starts with alphabets n,o,p,q,r,s,t,u,v,w,x,y,z but if the data starts with any other alphabet before n i.e. a-m then it show NA.
Please help

Thanks

Nipun Chawla

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-28 : 03:01:59

Try this logic

value1 = stuff(max(CASE WHEN location = 'Vadodara' THEN 'Z'+(AllLocations.Domestic) ELSE 'NA' END),1,1,'')

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-10-28 : 03:34:33

value1 = COALESCE(max(CASE WHEN location = 'Vadodara' THEN (AllLocations.Domestic) ELSE NULL END), 'NA'),

Might give you ANSI Warnings if the expression is NULL though.

So long as there is no "valid" empty string in your data you could use:

value1 = COALESCE(NullIf(max(CASE WHEN location = 'Vadodara' THEN (AllLocations.Domestic) ELSE '' END), ''), 'NA'),

at which point Madhi's suggestion starts to look less Geeky than I first thought!

"WITH (nolock)"

Shudder! Don't use that. Ever. If you know what I am talking about then fine, if you don't then stop using it, implement READ_COMITTED_SNAPSHOT instead and remove NOLOCK from all your code.

ricky_1605
Starting Member

30 Posts

Posted - 2010-10-28 : 04:42:54

@madhivanan & kristen

Thanks a lot both the solutions worked.

but i still could not understand the function of COALESCE.

Nipun Chawla

Kristen
Test

22859 Posts

Posted - 2010-10-28 : 04:45:13

COALESCE(Param1, Param2, Param3, ...)

returns the first parameter that is NOT NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources