Create a view from two other views

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)

Create a view from two other views

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-20 : 07:39:21

I have two views.
The code for the first view is this one :

SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

The code for the second view is this one :

SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

I want to create a view that will have the following columns :
bedalog, tmpRm, rbedaposo, rbedaposo1, rbedaposo + rbedaposo1

for every record of the previous two views
where it is not have a value it should show 0.

Thnx a lot!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-20 : 07:47:53

Something like this?

Select 
 bedalog, tmpRm, sum(rbedaposo) as rbedaposo, sum(rbedaposo1) as rbedaposo1, sum(rbedaposo+rbedaposo1) as rbedaposo_Total
From
(
SELECT bedalog, tmpRm, rbedaposo, 0 as rbedaposo1
from view1
union all
SELECT bedalog, tmpRm, 0 as rbedaposo, rbedaposo1
from view2
) t
Group by bedalog, tmpRm

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-20 : 07:51:53

Msg 207, Level 16, State 1, Line 8
Invalid column name 'bedaposo1'.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-21 : 06:46:09

Can you tell me why I get this error?Thnx again!

vitoco
Starting Member

22 Posts

Posted - 2008-02-21 : 08:31:53

quote:

 1: Select 
 2:  bedalog, tmpRm, sum(rbedaposo) as rbedaposo, sum(rbedaposo1) as rbedaposo1, sum(rbedaposo+rbedaposo1) as rbedaposo_Total
 3: From
 4: (
 5: SELECT bedalog, tmpRm, rbedaposo, 0 as rbedaposo1
 6: from view1
 7: union all
 8: SELECT bedalog, tmpRm, 0 as rbedaposo, rbedaposo1
 9: from view2
10: ) t
11: Group by bedalog, tmpRm

Just guessing...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 08:33:09

quote:

Originally posted by vitoco

quote:

 1: Select 
 2:  bedalog, tmpRm, sum(rbedaposo) as rbedaposo, sum(rbedaposo1) as rbedaposo1, sum(rbedaposo+rbedaposo1) as rbedaposo_Total
 3: From
 4: (
 5: SELECT bedalog, tmpRm, rbedaposo, 0 as rbedaposo1
 6: from view1
 7: union all
 8: SELECT bedalog, tmpRm, 0 as rbedaposo, rbedaposo1
 9: from view2
10: ) t
11: Group by bedalog, tmpRm

Just guessing...

I already made correction to my earlier reply, in case you haven't noticed.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vitoco
Starting Member

22 Posts

Posted - 2008-02-21 : 08:43:05

I didn't notice... it seems that neither skiabox, he posted a second reply 1 day later.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources