View problem - 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

View problem

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-20 : 16:05:39

I try to create a view that has a query that sets a column that is based on a select query that involves a case.
I would want to name the returned value from the case query to be named col2. How can I manage to do that?

The select query looks like this.

create view myView as
col1,
(SELECT
CASE WHEN u.description IS NOT NULL and u.uppmatkod like 'ISOLFANGTYP' THEN u.description ELSE 'Felregistrerat värde' END
FROM
ss1_olja1 o
LEFT OUTER JOIN
ss1_uppmatvardelista u
ON COALESCE(o.[uppmat_isolfangtyp_lbn], -1) = COALESCE(u.[value], -1) and u.uppmatkod like 'ISOLFANGTYP')

from "svanen"."dbo"."SS1_VY_FAKTA",

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-20 : 16:13:22

create view myView as
SELECT
col1,
(SELECT
CASE WHEN u.description IS NOT NULL and u.uppmatkod like 'ISOLFANGTYP' THEN u.description ELSE 'Felregistrerat värde' END
FROM
ss1_olja1 o
LEFT OUTER JOIN
ss1_uppmatvardelista u
ON COALESCE(o.[uppmat_isolfangtyp_lbn], -1) = COALESCE(u.[value], -1) and u.uppmatkod like 'ISOLFANGTYP') as col2

from "svanen"."dbo"."SS1_VY_FAKTA"

Tara Kizer
http://weblogs.sqlteam.com/tarad/

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-20 : 16:42:24

I have already tried that and then i get the following error message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How du I correct this the eb sure that only one value returns as col2?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-20 : 16:48:37

Perhaps you need to get the highest value using MAX.

It just depends on your requirements. Please show us a data example of what you want.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-20 : 17:00:02

ss1_uppmatvardelista has the following values (example)
id uppmatkod value description
1 ISOLFANGTYP 1 ISOL text1
2 ISOLFANGTYP 2 ISOL text2
3 ISOLFANGTYP NULL text missing
4 ISOLAVSER 1 IOIL text1
5 ISOLAVSER 2 IOIL text2
........

The column col2 should have the value from ss1_uppmatvardelista.description where u.value = o.uppmat_isolfangtyp_lbn

The view should return rows like:
ISOLAVSER text1
ISOLFANGTYP text missing
.......

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-20 : 17:29:36

We don't have enough information to help you. Please show us detailed information.

Check out this post that I created about 2.5 years ago:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516

Notice how I provide the information that is needed so that other SQLTeamers can copy my problem on their machines and work on it. We need that same thing from you.

Also notice how quickly I got answers to my problem.

NOTE: what I posted is not exactly how my table looks. I simplied it so that only the information that was needed to describe my problem was included in it that way other people do not get confused by the extra stuff.

I spent about an hour figuring out the simplest way to describe my problem. The create table and insert into statements are added so that another poster here can copy that code into their environments and pretend that they have the same table as me.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-20 : 18:00:47

Ok, here comes all querys that you should need to set up on your testenvironment.

CREATE TABLE [olja] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[value] [decimal](14, 4) NULL ,
PRIMARY KEY(id)
)

Insert values in table olja
insert into olja(value) values(1)
insert into olja(value) values(2)
insert into olja(value) values(NULL)
insert into olja(value) values(17111)

CREATE TABLE [uppmatvardelista] (
[uppmatkod] [varchar] (15) NOT NULL ,
[value] [decimal](14, 4) NULL ,
[description] [varchar] (50) NOT NULL
)

Insert values in table uppmatvardelista
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLFANGTYP', 1, 'ISOL text1')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLFANGTYP', 2, 'ISOL text2')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLFANGTYP', 3, 'ISOL text3')
insert into uppmatvardelista(uppmatkod, description) values('ISOLFANGTYP', 'text missing')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLAVSER', 1, 'OIL text1')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLAVSER', 2, 'OIL text2')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLAVSER', 3, 'OIL text3')
insert into uppmatvardelista(uppmatkod, description) values('ISOLAVSER', 'text missing')

create view testView as
select
olja.id,
uppmatvardelista.uppmatkod,
olja.value,
(SELECT
CASE WHEN u.description IS NOT NULL and u.uppmatkod like 'ISOLFANGTYP' THEN u.description ELSE 'wrong value' END
FROM
olja o
LEFT OUTER JOIN
uppmatvardelista u
ON COALESCE(o.[value], -1) = COALESCE(u.[value], -1) and u.uppmatkod like 'ISOLFANGTYP') as testCol
from olja

Estimated result in view:
id uppmatkod value description
1 ISOLFANGTYP 1.0000 ISOL text1
2 ISOLFANGTYP 2.0000 ISOL text2
3 ISOLFANGTYP NULL text missing
4 ISOLFANGTYP 17111 wrong value

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-20 : 18:09:48

Your insert statements are generating errors. Please correct.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-20 : 18:33:48

They worked fine with me, but here it comes again!
[id] [int] IDENTITY (1, 1) NOT NULL ,
[value] [decimal](14, 4) NULL ,
PRIMARY KEY(id)
)

insert into olja(value) values(1)
insert into olja(value) values(2)
insert into olja(value) values(NULL)
insert into olja(value) values(17111)

CREATE TABLE [uppmatvardelista] (
[uppmatkod] [varchar] (15) NOT NULL ,
[value] [decimal](14, 4) NULL ,
[description] [varchar] (50) NOT NULL
)

insert into uppmatvardelista(uppmatkod, value, description) values('ISOLFANGTYP', 1, 'ISOL text1')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLFANGTYP', 2, 'ISOL text2')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLFANGTYP', 3, 'ISOL text3')
insert into uppmatvardelista(uppmatkod, description) values('ISOLFANGTYP', 'text missing')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLAVSER', 1, 'OIL text1')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLAVSER', 2, 'OIL text2')
insert into uppmatvardelista(uppmatkod, value, description) values('ISOLAVSER', 3, 'OIL text3')
insert into uppmatvardelista(uppmatkod, description) values('ISOLAVSER', 'text missing')

yumyum113
Starting Member

31 Posts

Posted - 2007-04-20 : 20:02:18

Hi Maevr,

Not sure if this is what you are looking for: Left outer join of olja table to all rows of upppmatvardelista table which has a value of ISOLFANGTYP ON uppmatkod column, and that the resulting dataset would include a new column based on the value of uppmatkod.description. If this is what you need then perhaps the query below would work, if not then you can just ignore this reply.

create view testView as
select
o.id,
u.uppmatkod,
o.value,isnull(u.[description],'Wrong value') as 'Col2'
from olja o
LEFT OUTER JOIN
uppmatvardelista u
ON COALESCE(o.[value], -1) = COALESCE(u.[value], -1) and u.uppmatkod like 'ISOLFANGTYP'

-yumyum113

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-21 : 15:59:02

No, that wont work since uppmatvardelista is used to set the description based on the olja.value value. If the value does not exist in the uppmatvardelista based on the column uppmatkod and value then the value must be fault (but still registered in the database, and cannot/should not be removed) therefore it should be returned as 'wrong value'.

The select does work but it returns multiple values and I only want one value to be returned and set to a new column Col2.

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-22 : 15:57:24

Hi, nobody that can give me any hint on how to get around this problem witj multiple return values.... I am stuck.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-22 : 17:16:07

Doesn't yumyum's SELECT return EXACTLY what you said you wanted based on the sample data you provided? If that's not what you want, then you need to provide alternate sample data (and desired results) that reflects what you are after, since the sample data and results you've given is worthless if solving for that doesn't help you at all.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-23 : 12:58:38

The issue is that the to get all the values out of olja you will run into a situation where uppmatkod is NULL. so, you cannot select the value of uppmatkod without a COALESCE or just specifing the uppmatkod you are looking for. This returns your sampel output, but I'm not sure if it is as extensable as you are looking for:

SELECT 
	o.id,
	'ISOLFANGTYP' AS uppmatkod,
	o.value,
	COALESCE(u.[description], 'Missing Value') AS Description
FROM 
	olja o
LEFT OUTER JOIN
	uppmatvardelista u
	ON COALESCE(o.[value], -1) = COALESCE(u.[value], -1)
WHERE
	u.uppmatkod = 'ISOLFANGTYP' 
	OR u.uppmatkod IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources