Removing null values from a result set

Hi,

I have following query which is returing null values along with other values. Could you please tell me how I can restrict to not to return null values

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Thanks,

Prudhvi

Prud,

I'm assuming that you want to filter out TotalHours

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Where [Measures].[Total Hours] is not null

Ham

|||

Thanks Ham. I did tried this earlier but got the following error message

Executing the query ...
Query (5, 1) The IS function expects a member expression for the 2 argument. A string or numeric expression was used.
Execution complete

|||

Prud,

Are you using SQL 2000 or SQL 2005? The "is not null" is a valid for SQL in the Where clause.

Ham

|||

SSAS 2005 introduces a HAVING clause:

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]

HAVING [Measures].[Total Hours] <> 0 ON 1
FROM[Labor Metrics]

Or, you can use Filter().

|||

Thanks Teo, it worked.

Removing non-alpha characters & spaces script...

Hi I am trying to strip out any non-alpha characters from a field.

i.e. Field = ABC"_IT8*$ should return: ABCIT8

I am writing a loop to do this for all values of a field. The script runs, but hangs...please could somebody advise on the code below...:

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @.Index SMALLINT,
@.MATCH_Supplier_name varchar(500),
@.Counter numeric,
@.Max numeric
-- @.sqlstring varchar(500)

SET @.Counter = 1
SET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @.Counter <@.Max
BEGIN
SET @.MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @.Counter = DTect_Supplier_SRN)
SET @.Index = LEN(@.MATCH_Supplier_name)
WHILE @.Index > = 1
SET @.MATCH_Supplier_name = CASE
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[0-9]' THEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
ELSE ''
END + @.MATCH_Supplier_name
SET @.Index = @.Index - 1
--PRINT @.MATCH_Supplier_name
SET @.Counter = @.Counter + 1
ENDSET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

Change it to count ...don't have much time now,check for other errors.|||Cheers, its all sorted now.

removing embedded duplicate rows

I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:

> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:

>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to

>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)