Creating a view only - 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

Creating a view only

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-03 : 15:48:36

I cannot create this view because the tables are in another database.
Is there a way to link to this other database? Or that I must copy in all of the tables. THis is problematic if there are updates to the database tables.
I wanted to setup a Developers Modifications database which would hold any changes we make as well as eventually use this to link to our many databases from the Reporting Services.

ALTER VIEW [dbo].[Vw_Details]
AS
SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, RM00101.CPRCSTNM, IV40600.UserCatLongDescr AS UserCatLongDescr_IV40600,
CATS.UserCatLongDescr AS UserCatLongDescr_CATS, SOP10200.ITEMNMBR, SOP10200.SOPNUMBE, SOP10200.QUANTITY, SOP10200.OXTNDPRC,
SOP10200.SOPTYPE, SOP10100.DOCDATE, 'Current' AS source, MONTH(SOP10100.DOCDATE) AS ReportMonth, YEAR(SOP10100.DOCDATE)
AS ReportYear
FROM dbo.sop10200 AS SOP10200 INNER JOIN
dbo.iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
dbo.iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
dbo.rm00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
UNION ALL
SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, RM00101.CPRCSTNM, IV40600.UserCatLongDescr, CATS.UserCatLongDescr AS Expr1,
SOP30300.ITEMNMBR, SOP30300.SOPNUMBE, SOP30300.QUANTITY, SOP30300.OXTNDPRC, SOP30300.SOPTYPE, SOP30200.DOCDATE,
'History' AS source, MONTH(SOP30200.DOCDATE) AS ReportMonth, YEAR(SOP30200.DOCDATE) AS ReportYear
FROM dbo.sop30300 AS SOP30300 LEFT OUTER JOIN
dbo.iv00101 AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.sop30200 AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
dbo.iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL LEFT OUTER JOIN
dbo.iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
dbo.rm00101 AS RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-03 : 15:52:40

You wrote that the tables are in another database. It's no problem.
Unless the tables and databas is on another server.

FROM {Database name here}.dbo.sop10200 AS sop10200
INNER JOIN {Database name here}.dbo.iv00101 AS iv00101 ... and so on

N 56°04'39.26"
E 12°55'05.63"

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-03 : 15:55:17

Thank you Peso, didn't know that. For now, it's the same server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources