Problem with the creation of a view

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

Problem with the creation of a view

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-18 : 14:47:41

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 14:59:49

I think this does it:

CREATE VIEW ss1_view_oil
AS
SELECT ss1_oil.id, ss1_uppmatvardelista.description
FROM ss1_oil
INNER JOIN ss1_uppmatvardelista
ON ss1_oil.value = ss1_uppmatvardelista.id

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

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-18 : 15:39:34

Thanks!
But how do you get the null values to get the text?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 15:41:46

I don't understand what you mean.

The query that I posted will give you the output that you requested. If that isn't the output that you want, then please provide a better example.

Here is what you requested:
examplevalues:
id description
1 text1
2 text3

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

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-18 : 15:43:24

If I understand your question correctly, try this:

CREATE VIEW ss1_view_oil
AS
SELECT ss1_oil.id, ss1_uppmatvardelista.description
FROM ss1_oil
INNER JOIN ss1_uppmatvardelista
ON ss1_oil.value = ss1_uppmatvardelista.id OR
(ss1_oil.value IS NULL AND ss1_uppmatvardelista.id IS NULL)

SQL Server Helper
http://www.sql-server-helper.com

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-18 : 16:01:34

Thank you both tkizer and sshelper, it works fine. Any idéa how to handle values that have been inserted that do not match any of the values from the ss1_uppmatvardelista, for example 1.7568. I would want to give these a description like "fault registered" or something like that. My first thought was a case inside the select query

Your help is much appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 16:05:03

You would use an OUTER JOIN for the ones that don't have matches. Just replace the NULL value (which you get from the outer join for the table that doesn't have a match) with the value you want. If you could provide a clear example of what you want, then we could write the query.

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

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-18 : 16:19:35

Ok I give it a try!
ss1_oil has the column value that only should have the values null, 1, 2, 3 but there exists som values that should belong there such as 1.6788, 0.3455 for example. null, 1, 2 and 3 have descriptions specified but how can yoy take care of the others, I would want to place a description on those to.

ss1_oil
1, 1.789
2, 2
3, 1

ss1_uppmatvardelista
0, invalid
1, text1
2, text2
3, text3
null, text missing
Others, fault registered

view
1, fault registered
2, text2
3, text1

Hope this will describe better what I'm looking for.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 16:23:39

SELECT ss1_oil.id, CASE WHEN ss1_uppmatvardelista.description IS NOT NULL THEN ss1_uppmatvardelista.description ELSE 'fault registered' END
FROM ss1_oil
LEFT OUTER JOIN ss1_uppmatvardelista
ON ss1_oil.value = ss1_uppmatvardelista.id

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-18 : 18:42:20

Cross Post:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82287[/url]

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-19 : 08:32:10

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)

inserted values:
isolfangtyp, 1, text1
isolfangtyp, 2, text2
isolavser, 1, text11
isolavser, 2, text22

----------------------------------
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'
----------------------------------

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-19 : 08:56:42

You should place that table on the LEFT side of LEFT JOIN for which you want all records to be returned irrespective of whether there is matching record in the joined table. Run the example below to get the better idea on LEFT JOINS:

declare @emp table
(
	empid int,
	ename varchar(20),
	deptid int
)

declare @dept table
(
	deptid int,
	dname varchar(20)
)

insert @emp
select 1, 'aaa', 1 union all
select 2, 'bbb', 2 union all
select 3, 'ccc', 6 union all
select 4, 'ddd', 8

insert @dept
select 1, 'Accounts' union all
select 3, 'Sales'  union all
select 5, 'HR' union all
select 8, 'IT'

-- To see all employees irrespective of related departments
select e.*, d.*
from @emp e left join @dept d on e.deptid = d.deptid

-- To see all depts irrespective of related employees
select d.*, e.*
from @dept d left join @emp e  on e.deptid = d.deptid

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-19 : 14:31:16

I dont really understand the left outer joins could anyone please redo this so it fits the solution above since that is what I've been doing the last couple of days without success.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 14:33:41

Which solution are you referring to?

If you don't understand outer joins, then you need to learn them rather than come up with a different solution that doesn't use outer joins.

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

maevr
Posting Yak Master

169 Posts

Posted - 2007-04-19 : 14:46:25

I think I understand tham but what I dont understand is why the resultset becomes different when I add the line below:
where u.uppmatkod like 'ISOLFANGTYP' (look at previous post), when the 17315.0000 value seems to disappear, it should get the value fault registered.

Also when I add a new row in ss1_uppmatvardelista containing values below and run the query against isolavser then I the above appear again.
ss1_uppmatvardelista
isolfangtyp, 1, text1
isolfangtyp, 2, text2
isolavser, 1, text11

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-19 : 17:16:12

quote:
Originally posted by maevr

I think I understand tham but what I dont understand is why the resultset becomes different when I add the line below:
where u.uppmatkod like 'ISOLFANGTYP' (look at previous post), when the 17315.0000 value seems to disappear, it should get the value fault registered.

Also when I add a new row in ss1_uppmatvardelista containing values below and run the query against isolavser then I the above appear again.
ss1_uppmatvardelista
isolfangtyp, 1, text1
isolfangtyp, 2, text2
isolavser, 1, text11

I'm not sure I understand what is happening. Could you provide sample data, expected results and actual results to help us trouble shoot?

maybe the issue is the WHERE clause? Try this:

SELECT 
o.id, 
u.[description]
FROM 
@ss1_oil o
LEFT OUTER JOIN
@ss1_uppmatvardelista u
ON COALESCE(o.[value], -1) = COALESCE(u.[value], -1)
AND u.uppmatkod like 'ISOLFANGTYP'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources