MySQL to MSSQL problem

P1mm0s

Recently we changed from MySQL to MSSQL everything went fine except:
- The LIMIT function is not supported in MSSQL, i searched alot of forums to find out what trick is used to get it working with MSSQL, the solution was found with the TOP function, I have a query working:

SELECT *
FROM (SELECT TOP 5 menu_naam, menu_link, menu_id
FROM (SELECT TOP 20 menu_naam, menu_link, menu_id
FROM menu
ORDER BY menu_id) AS tbl1
ORDER BY menu_id DESC) tbl2
ORDER BY menu_id

But the problem is how am I going to put this method into alot more advanced code like:

SELECT TOP 100 white_subscriber.*, (housenumber) AS housenr, count(white_info.id) AS infocnt FROM white_subscriber LEFT JOIN white_info ON white_info.id = white_subscriber.id WHERE postalcode LIKE '0000__' GROUP BY white_subscriber.id, white_subscriber.category, white_subscriber.title, white_subscriber.firstname, white_subscriber.infix, white_subscriber.lastname, white_subscriber.streetname, white_subscriber.housenumber, white_subscriber.postalcode, white_subscriber.city, white_subscriber.phone, white_info.id ORDER BY postalcode,housenr

I self have made the code like this:

SELECT white_subscriber., (housenumber) AS housenr, count(white_info.id) AS infocnt FROM (SELECT TOP 100 white_subscriber., (housenumber) AS housenr, count(white_info.id) AS infocnt FROM (SELECT TOP 200 white_subscriber.*, (housenumber) AS housenr, count(white_info.id) AS infocnt FROM white_subscriber LEFT JOIN white_info ON white_info.id = white_subscriber.id
ORDER BY postalcode,housenr ASC) AS tbl1
ORDER BY postalcode,housenr DESC) AS tbl2
ORDER BY postalcode,housenr ASC

This code is needed to search the DB Phonebook, this code gives errors like:
- The column prefix 'white_subscriber' does not match with a table name or alias name used in the query
- - The column prefix 'white_info' does not match with a table name or alias name used in the query

Anyone know a solution or working order of this code then I would be very gratefull. Thanks in advance for all support.

drstrangelove

I will be struggling with this same exact issue in a short time. I haven't put a lot of research into it, but the first place I am going to try to find a solution is with ADODB.
Here is a quote from their site concerning the limit function of MySQL and emulating it for other databases:

Support many enterprise features such as database backed sessions (with session expiry notification), SQL code generation, pivot tables, SELECT LIMIT emulation for all databases, performance monitoring.

I hope this information leads you to a solution. Please post here your results of researching ADODB for this purpose.
HTH,
drstrangelove

Sxooter

Does MSSQL server have any kind of artificial row numbers generated by queries? In Oracle, you get an invisible rownum, and you can do something like:

select * from sometable where rownum<100

for a limit ( I think it's rownum. been a while, could just be row or rownumber)

P1mm0s

The solution was pretty wierd, but I havent editted the query at all, to use this in alot more advanced code, I just have used the for loop I keep it looping until he reeaches that row wich i want him to start from since the db isnt that big it doesnt take too long loading. Even better it doesnt even load long at all. I load 3000 records wich takes a while, when I select the 100 records per page it loads alot faster then with the 3000 records. So it doesnt matter alot. Though it would be nice if Microsoft is going to implement the LIMIT function, goodluck to you guys and thank you for the support.

Sxooter

I'm sure that MS has cursors, which are the other way of handling this as well. That might help if you're handling larger data sets.

Roger_Ramjet

When you use a sub-query to return a table like that you have to give it an alias. So the outer query must refer to the query-table by that alias. When you alias column in such a sub-query, the table reterned will have that alias as the column name so there is no need to alias it again. All that means is that you can just write it like this to elliminate those naming errors.

SELECT * FROM
(SELECT TOP 100 * FROM 
(SELECT TOP 200 white_subscriber.*, (housenumber) AS housenr, count(white_info.id) AS infocnt FROM white_subscriber LEFT JOIN white_info ON white_info.id = white_subscriber.id
ORDER BY postalcode,housenr ASC) AS tbl1
ORDER BY postalcode,housenr DESC) AS tbl2
ORDER BY postalcode,housenr ASC

As to the limit problem. In mysql you would use a stored procedure and a cursor to select either absolute record number or relative to current cursor position. Making your recordset persist from page to page will be the biggest problem in this php/web environment.

P1mm0s

I tryed the php code you sended and I received the following errors:

Warning: mssql_query() [function.mssql-query]: message: Column 'white_subscriber.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. (severity 16) in fs2005.php on line 810

Warning: mssql_query() [function.mssql-query]: message: Column 'white_subscriber.category' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. (severity 16) in fs2005.php on line 810

and so on for every column. It is expecting a group by clause or something.
But Im thinking that your code should work if there is a group by clause, but were to put it is another story.

TF_TheMoon

Yup that query needs a GROUP BY as you use a COUNT

  white_subscriber.*,
  housenumber AS housenr, 
  count(white_info.id) AS infocnt

That means before your ORDER BY you need a GROUP BY
then you need to group by all the fields in the white_subscriber table as well as house number.