Problem with case query! - 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 2000 Forums

Transact-SQL (2000)

Problem with case query!

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-18 : 09:21:36

I need help writing the query below. ss1_oil has a column storing values in the decimal format (1, 2 and 3) and ss1_uppmatvardelista har the corresponding text for these stored in description column.
My problem is that I have to get the text and insert it into a view to be able to build an Analysis cube.

I get the error message that the case query returns multiple values, how do you fix this?

SELECT description as Oil_type
FROM ss1_uppmatvardelista JOIN ss1_oil
ON value = uppmatType
and value = (select description =
CASE value
WHEN 0 THEN 'text1'
WHEN 1 THEN 'text2'
WHEN 2 THEN 'text3'
WHEN 3 THEN 'text4'
ELSE 'Unknown value'
END
FROM ss1_uppmatvardelista)

I have tried all day to fix this problem but never succeeded.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 09:26:46

How about this?

SELECT description as Oil_type
FROM ss1_uppmatvardelista JOIN ss1_oil 
ON 
	Case value
		WHEN 0 THEN 'text1'
		WHEN 1 THEN 'text2'
		WHEN 2 THEN 'text3'
		WHEN 3 THEN 'text4'
		ELSE 'Unknown value'
	END = uppmatType

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-18 : 09:38:53

Thanks for quick response, but it does not work, the resultset is empty.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 09:45:05

Post table structure, sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-18 : 14:46:17

I need help writing the query below. ss1_oil has a column storing values in the decimal format (1, 2 and 3) and ss1_uppmatvardelista har the corresponding text for these stored in description column.
My problem is that I have to get the text and insert it into a view to be able to build an Analysis cube.

ss1_uppmatvardelista
value decimal(14,4)
description varchar(50)

Examplevalues:
value description
0 invalid
1 text1
2 text2
3 text3
null text missing
--------------------------------------

ss1_oil
id int
value decimal(14,4)
Examplevalues:
id value
1 1
2 3
--------------------------------------

I want to create a view that presents the following values taken from ss1_uppmatvardelista and ss1_oil:
ss1_view_oil
id int
description varchar(50)

examplevalues:
id description
1 text1
2 text3

Can this be done and how?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-18 : 15:54:13

I'd suggest they you redesign you tables, but here are two queries that work. I suspect that QUERY 2 would perform better, but you should test on your data to verify:

-- SET UP
DECLARE @ss1_uppmatvardelista TABLE([value] decimal(14,4), description varchar(50))
DECLARE @ss1_oil TABLE (id int, [value] decimal(14,4))

INSERT @ss1_uppmatvardelista
SELECT 0, 'invalid' UNION ALL
SELECT 1, 'text1' UNION ALL
SELECT 2, 'text2' UNION ALL
SELECT 3, 'text3' UNION ALL
SELECT NULL, 'text missing' 


INSERT @ss1_oil
SELECT 1, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, NULL

--QUERY 1
SELECT 
	o.id, 
	COALESCE(u.[description], (SELECT TOP 1 description FROM @ss1_uppmatvardelista WHERE [value] IS NULL)) AS description
FROM 
	@ss1_oil o
LEFT OUTER JOIN
	@ss1_uppmatvardelista u
	ON o.[value] = u.[value]

--QUERY 2
SELECT 
	o.id, 
	u.[description]
FROM 
	@ss1_oil o
LEFT OUTER JOIN
	@ss1_uppmatvardelista u
	ON COALESCE(o.[value], -1) = COALESCE(u.[value], -1)

Cheers,

-Ryan

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-19 : 04:12:39

Hi I have added a column in ss1_uppmatvardelista, it looks like this now:
----------------------------------
ss1_uppmatvardelista
uppmatkod varchar(15) not null,
value decimal(14,4)
description varchar(50)
----------------------------------
When I try to add the last row (look query below), the query does not work for values that is not specified in the ss1_uppmatvardelista table, why?
----------------------------------
SELECT
o.id,
u.[description]
FROM
@ss1_oil o
LEFT OUTER JOIN
@ss1_uppmatvardelista u
ON COALESCE(o.[value], -1) = COALESCE(u.[value], -1)
where u.uppmatkod like 'ISOLFANGTYP'
----------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources