MS SQL to MySQL select

[SenTnel]

Hello!

In order for me to achive the goal of migrating a MS SQL based application to web based php / mysql I need to understand a little bit MS SQL syntax and MySQL counterpart expression. If is not too much to ask one of you guys could "translate" this MS SQL select to MySQL? Here's the challenge:

CREATE VIEW dbo.equipos
AS
SELECT     TOP 100 PERCENT dbo.OBJETOSPATRIMONIO.objpatCodigo AS Expr1, dbo.OBJETOSPATRIMONIO.objpatDenom AS Expr2,
                      dbo.OBJETOSPATRIMONIO.objpatEstadoTec AS Expr3, dbo.ESTRUCTURAPATRIMONIO.clsnodopatID,
                      dbo.[01 LOCALIZACIONES OBJETOSPATRIM].Localizacion
FROM         dbo.[01 LOCALIZACIONES OBJETOSPATRIM] INNER JOIN
                      dbo.OBJETOSPATRIMONIO ON dbo.[01 LOCALIZACIONES OBJETOSPATRIM].objpatID = dbo.OBJETOSPATRIMONIO.objpatID INNER JOIN
                      dbo.OBJETOSPATRIMEQUIPOS ON dbo.OBJETOSPATRIMONIO.objpatID = dbo.OBJETOSPATRIMEQUIPOS.equipID INNER JOIN
                      dbo.ESTRUCTURAPATRIMONIO ON dbo.OBJETOSPATRIMONIO.objpatNodoEnlazID = dbo.ESTRUCTURAPATRIMONIO.nodopatrimID
ORDER BY dbo.OBJETOSPATRIMONIO.objpatCodigo


Really appreciate your help! Thanks!

[Martijn Tonies]

Hey,

> In order for me to achive the goal of migrating a MS SQL based application
> to web based php / mysql I need to understand a little bit MS SQL syntax
and
> MySQL counterpart expression. If is not too much to ask one of you guys
> could "translate" this MS SQL select to MySQL? Here's the challenge:

Yes, that is too much. This is a very basic query, if you cannot translate
it yourself, I wish you good luck on your conversion project.

Is there anything -specific- that you want to have translated here?

Would it help if I said that this query prefixes each object with its owner
(dbo)
and for MySQL you should remove that?

eg:

create view equipos ...

select ... OBJETOSPATRIMONIO.objpatCodigo
...
from `01 LOCALIZACIONES OBJETOSPATRIM` INNER JOIN
...

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> CREATE VIEW dbo.equipos
> AS
> SELECT     TOP 100 PERCENT dbo.OBJETOSPATRIMONIO.objpatCodigo AS Expr1,
> dbo.OBJETOSPATRIMONIO.objpatDenom AS Expr2,
>                       dbo.OBJETOSPATRIMONIO.objpatEstadoTec AS Expr3,
> dbo.ESTRUCTURAPATRIMONIO.clsnodopatID,
>                       dbo.[01 LOCALIZACIONES OBJETOSPATRIM].Localizacion
> FROM         dbo.[01 LOCALIZACIONES OBJETOSPATRIM] INNER JOIN
>                       dbo.OBJETOSPATRIMONIO ON dbo.[01 LOCALIZACIONES
> OBJETOSPATRIM].objpatID = dbo.OBJETOSPATRIMONIO.objpatID INNER JOIN
>                       dbo.OBJETOSPATRIMEQUIPOS ON
> dbo.OBJETOSPATRIMONIO.objpatID = dbo.OBJETOSPATRIMEQUIPOS.equipID INNER

JOIN
>                       dbo.ESTRUCTURAPATRIMONIO ON
> dbo.OBJETOSPATRIMONIO.objpatNodoEnlazID =
> dbo.ESTRUCTURAPATRIMONIO.nodopatrimID
> ORDER BY dbo.OBJETOSPATRIMONIO.objpatCodigo
>
>
> Really appreciate your help! Thanks!


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[SenTnel]

Thanks Martijn!

That I understood, sorry I wasn't clear enough, I was more confused about the "TOP 100 PERCENT" and this was a bad example anyways, but after posting I did some deeper research and found an excellent migration white paper describing this type of expressions and mysql counterparts. Tomorrow I'll post the url as I don't have it right now on my laptop, I saved the link on my desktop. Anyways I thank you for your comments.

Martijn Tonies wrote:

Hey,

> In order for me to achive the goal of migrating a MS SQL based application
> to web based php / mysql I need to understand a little bit MS SQL syntax
and
> MySQL counterpart expression. If is not too much to ask one of you guys
> could "translate" this MS SQL select to MySQL? Here's the challenge:

Yes, that is too much. This is a very basic query, if you cannot translate
it yourself, I wish you good luck on your conversion project.

Is there anything -specific- that you want to have translated here?

Would it help if I said that this query prefixes each object with its owner
(dbo)
and for MySQL you should remove that?

eg:

create view equipos ...

select ... OBJETOSPATRIMONIO.objpatCodigo
...
from `01 LOCALIZACIONES OBJETOSPATRIM` INNER JOIN
...

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> CREATE VIEW dbo.equipos
> AS
> SELECT     TOP 100 PERCENT dbo.OBJETOSPATRIMONIO.objpatCodigo AS Expr1,
> dbo.OBJETOSPATRIMONIO.objpatDenom AS Expr2,
>                       dbo.OBJETOSPATRIMONIO.objpatEstadoTec AS Expr3,
> dbo.ESTRUCTURAPATRIMONIO.clsnodopatID,
>                       dbo.[01 LOCALIZACIONES OBJETOSPATRIM].Localizacion
> FROM         dbo.[01 LOCALIZACIONES OBJETOSPATRIM] INNER JOIN
>                       dbo.OBJETOSPATRIMONIO ON dbo.[01 LOCALIZACIONES
> OBJETOSPATRIM].objpatID = dbo.OBJETOSPATRIMONIO.objpatID INNER JOIN
>                       dbo.OBJETOSPATRIMEQUIPOS ON
> dbo.OBJETOSPATRIMONIO.objpatID = dbo.OBJETOSPATRIMEQUIPOS.equipID INNER
JOIN
>                       dbo.ESTRUCTURAPATRIMONIO ON
> dbo.OBJETOSPATRIMONIO.objpatNodoEnlazID =
> dbo.ESTRUCTURAPATRIMONIO.nodopatrimID
> ORDER BY dbo.OBJETOSPATRIMONIO.objpatCodigo
>
>
> Really appreciate your help! Thanks!


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=[e-mail]