Creating VIEWS (from Oracle to SQL Server)

Cordial greetings,

Again i need help with a couple of issues in migrating from Oracle to SQL Server 2005. I need the equivalent sentence in SQL Server of the following sentence:

CREATE OR REPLACE VIEW IBA_MPDATOSGENERALES AS (
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND (IBA_MPACCIONESXREQ.CERRADAAXR = 'S'
OR (IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND ((IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL) OR (IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL))))
)

I know that (+) is for outer joins (left or right). I also know that UNION may mean FULL OUTER JOIN. but this query? The real query is a lot more complex, well actually there are just more fields needed from more tables but with this exmple is enough. The bottom line is that i need data from various tables, most of them involved in a JOIN clause. Also check out that in SQL Server doesnt exists the REPLACE word or does it?
Thank you in advance,

Fernando Martinez

First I want to tell you few things,

1. (+) is for outer join (left/right)

Yes .. You are correct. It always best practice to follow the ANSI standard, it is very simple when you migrate from one database to another. As of now you can delete all the (+) signs and use =* on the corresponding logical expression...

example..

AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO (+)

change it to

AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO =* IBA_MPACCIONESXREQ.IDREQUERIMIENTO

2. Union may mean FULL OUTER JOIN.

Wrong . In any database Union means Union (set operator) only. So you need not to worry about this.

3.REPLACE Word in SQL Server.

Yes. SQL Server doesn't support REPLACE (Create or replace)

Instead of Create or Replace follow these,

When you create the View (at first time) use Create View .. statement.

If you want to replace the existing view (after created) use the Alter View.. statement. (Simple isn't).

I am not interested to change your query. (Its simple for me). Learn your self before doing anything. I know you are a Oracle developer and not interested to learn SQL Server(May be you assigned temp for this migration project ). If you learn the basic difference you can do it your self Buddy..!

All the best.

-Mani

|||

well there is not much alteration in ur query, while shifting it to sql server...

just replace the create or replace by CREATE (while creating the view) , and later replace the CREATE by ALTER when u need to make any changes..(as explained by mani )

(consider the rest as three different queries..union just puts the result together...:::

union vs Join -> union appends the ROWS to the result of first query...while a join will append columns based on some condition u give)

second the plus used in ur case is right outer joins...so replace them with the ANSI syntax...tableA right outer join tableB on tableA.col = tableB.col . (preferred)..else u can also use.. *= (aka += of oracle)for left outer and =* from rite outer join..

u'll get all the info for poring in sql server books online...including sqlserver equivalents (..and their usage) of oracle specific syntax (eg if u encounter rownum or tochar etc)..

|||Thank you guys, i apreciatte your help.