Convert "Outer" to SQL?
Does anyone know how to convert the following Informix statement to a
standard SQL Server statement? When I try to execute this in SQL Server
2000 I get an error on the Outer syntax of the statement. Any help is
appreciated
Create view ps_amproj_amt_vw
(business_unit,oprid,asset_id,cost,accum_depr,category,deptid,project_id,as_of_date,depr_ytd,method,db_percent,life,in_service_dt,depr_status,gain_loss,proceeds,retirement_dt,disposal_code)as
select x0.business_unit ,x0.oprid ,x0.asset_id ,x0.cost
,x0.accum_depr
,x0.category ,x0.deptid ,x0.project_id ,x0.as_of_date ,x0.depr_ytd
,x1.method ,x1.db_percent ,x1.life ,x1.in_service_dt
,x1.depr_status
,x2.gain_loss ,x2.proceeds ,x2.retirement_dt ,x2.disposal_code
from ps_asset_nbv_tbl x0 ,ps_book x1 , outer(
ps_retirement x2 ) where (((((((x0.book = 'AMT' )
AND (x0.business_unit = x1.business_unit ) ) AND (x0.book
= x1.book ) ) AND (x0.asset_id = x1.asset_id ) ) AND
(x0.business_unit
= x1.business_unit ) ) AND (x1.asset_id = x2.asset_id ) )
AND (x1.book = x2.book ) )
<barhoc11@gmail.com> wrote in message
news:1140530948.280874.175620@g44g2000cwa.googlegroups.com...
> Does anyone know how to convert the following Informix statement to a
> standard SQL Server statement? When I try to execute this in SQL Server
> 2000 I get an error on the Outer syntax of the statement. Any help is
> appreciated
>
> Create view ps_amproj_amt_vw
> (business_unit,oprid,asset_id,cost,accum_depr,category,deptid,project_id,as_of_date,depr_ytd,method,db_percent,life,in_service_dt,depr_status,gain_loss,proceeds,retirement_dt,disposal_code)> as
> select x0.business_unit ,x0.oprid ,x0.asset_id ,x0.cost
> ,x0.accum_depr
> ,x0.category ,x0.deptid ,x0.project_id ,x0.as_of_date ,x0.depr_ytd
> ,x1.method ,x1.db_percent ,x1.life ,x1.in_service_dt
> ,x1.depr_status
> ,x2.gain_loss ,x2.proceeds ,x2.retirement_dt ,x2.disposal_code
> from ps_asset_nbv_tbl x0 ,ps_book x1 , outer(
> ps_retirement x2 ) where (((((((x0.book = 'AMT' )
> AND (x0.business_unit = x1.business_unit ) ) AND (x0.book
> = x1.book ) ) AND (x0.asset_id = x1.asset_id ) ) AND
> (x0.business_unit
> = x1.business_unit ) ) AND (x1.asset_id = x2.asset_id ) )
> AND (x1.book = x2.book ) )
The following is the preferred ANSI syntax, which works on both Informix and SQL
Server:
CREATE VIEW ps_amproj_amt_vw AS
SELECT x0.business_unit,
x0.oprid,
x0.asset_id,
x0.cost,
x0.accum_depr,
x0.category,
x0.deptid,
x0.project_id,
x0.as_of_date,
x0.depr_ytd,
x1.method,
x1.db_percent,
x1.life,
x1.in_service_dt,
x1.depr_status,
x2.gain_loss,
x2.proceeds,
x2.retirement_dt,
x2.disposal_code
FROM ps_asset_nbv_tbl AS x0
JOIN ps_book AS x1
ON x0.business_unit = x1.business_unit
AND x0.book = x1.book
AND x0.asset_id = x1.asset_id
AND x0.business_unit = x1.business_unit
LEFT OUTER
JOIN ps_retirement AS x2
ON x1.asset_id = x2.asset_id
AND x1.book = x2.book
WHERE x0.book = 'AMT'
You don't need to specify the view's column names as they are identical to the
source tables and will be inherited.
--
Regards,
Doug Lawry
www.douglawry.webhop.org