• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

SQL 2000 equivalent to MySQL LIMIT clause.

IT seems no matter how I do this, this will be quite complex.


I know from google the net that  in MySQL the command: SELECT emp_id,lname,fname FROM employee LIMIT 20,10 can be written as follow in MS SQL 2000.


select * from (
 select top 10 emp_id,lname,fname from (
    select top 30 emp_id,lname,fname    from employee
   order by lname asc
 ) as newtbl order by lname desc
) as newtbl2 order by lname asc


But these are for simple query, I have been spending 2 hours on my query and yet with no luck..

This is the oringal query without the Limit apply to it

SELECT e.Message_Id, e.Ordinal, e.Reference, CONVERT(varchar(20),CONVERT(smalldatetime,e.Transmit_Date),107) AS Transmit_Date, e.Amount_Cdn, e.Queue, ISNULL(b.Status, 0) AS Status,right(e.corrected ,1), 'true' as ReviewerFlag,'' as ArchiveDB, e.Fintrac_ref as Fintrac_ref
FROM  ..boaeft e LEFT JOIN ..batcheft b ON (e.Reference=b.Reference AND b.MostRecent=1)  
WHERE e.MostRecent=1 AND e.EFT_Type='EFTO'   AND e.Queue!=0 AND e.Queue!=1  
ORDER BY e.Transmit_Date DESC, e.Reference ASC       

The problem I see here is mainly because fo the join i have.


Here is the query which I attempt to build.

select * FROM (select top 20 e.Message_Id, e.Ordinal, e.Reference, CONVERT(varchar(20), CONVERT(smalldatetime,e.Transmit_Date),107) AS Transmit_Date,
e.Amount_Cdn, e.Queue, ISNULL(b.Status, 0) AS Status,right(e.corrected ,1),'true' as ReviewerFlag,'' as ArchiveDB, e.Fintrac_ref as Fintrac_ref  
FROM ( select top 50 e.Message_Id, e.Ordinal, e.Reference, CONVERT(varchar(20),
CONVERT(smalldatetime,e.Transmit_Date),107) AS Transmit_Date, e.Amount_Cdn, e.Queue, ISNULL(b.Status, 0) AS Status,right(e.corrected ,1),'true' as ReviewerFlag,'' as ArchiveDB, e.Fintrac_ref as Fintrac_ref  
FROM ..boaeft e LEFT JOIN ..batcheft b
ON (e.Reference=b.Reference AND b.MostRecent=1)  
WHERE e.MostRecent=1 AND e.EFT_Type='EFTO'   AND e.Queue=2 AND corrected!=2  
ORDER BY e.Transmit_Date ASC, e.Reference ASC
)as t1 ORDER BY Transmit_Date ASC, Reference ASC
)as t2 ORDER BY Transmit_Date ASC,Reference ASC


But I kept getting error like:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'e' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'e' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'e' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1


Please assist me if you know how? if I can put more then 500 points for this question , I would put 2000 :)
0
fylix0000
Asked:
fylix0000
  • 3
  • 2
1 Solution
 
Atlanta_MikeCommented:
You're almost there...

I think this is what you're looking for.


 SELECT TOP 20
        t1.Message_Id,  
        t1.Ordinal,  
        t1.Reference,  
        t1.Transmit_Date,
        t1.Amount_Cdn,  
        t1.Queue,
        t1,Status,
        t1.ReviewerFlag,
        t1.ArchiveDB,  
        t1.Fintrac_ref  
FROM (
        select top 50
        e.Message_Id,
        e.Ordinal,  
        e.Reference,  
        CONVERT(varchar(20),
        CONVERT(smalldatetime,e.Transmit_Date),107) AS Transmit_Date,  
        e.Amount_Cdn,  
        e.Queue,  
        ISNULL(b.Status, 0) AS Status,
        right(e.corrected ,1),
        'true' as ReviewerFlag,'' as ArchiveDB,  
        e.Fintrac_ref as Fintrac_ref  
FROM ..boaeft e
LEFT JOIN ..batcheft b
ON (e.Reference=b.Reference AND b.MostRecent=1)  
WHERE e.MostRecent=1 AND e.EFT_Type='EFTO'  
 AND e.Queue=2 AND corrected!=2  
ORDER BY e.Transmit_Date ASC, e.Reference ASC)
ORDER BY Transmit_Date ASC, Reference ASC
0
 
Atlanta_MikeCommented:
Or actually:

   SELECT TOP 20
        t1.Message_Id,
        t1.Ordinal,  
        t1.Reference,  
        CONVERT(varchar(20), t1.Transmit_Date,107) AS Transmit_Date,
        t1.Amount_Cdn,  
        t1.Queue,  
        ISNULL(b.Status, 0) AS Status,
        right(t1.corrected ,1),
        'true' as ReviewerFlag,
        '' as ArchiveDB,  
        t1.Fintrac_ref as Fintrac_ref  
    FROM (
            select top 50  
            e.Message_Id,  
            e.Ordinal,  
            e.Reference,  
            CONVERT(varchar(20),
            CONVERT(smalldatetime,e.Transmit_Date),107) AS Transmit_Date,  
            e.Amount_Cdn,  
            e.Queue,  
            ISNULL(b.Status, 0) AS Status,
            right(e.corrected ,1),
            'true' as ReviewerFlag,
            '' as ArchiveDB,  
            e.Fintrac_ref as Fintrac_ref  
            FROM ..boaeft e
            LEFT JOIN ..batcheft b
            ON e.Reference=b.Reference  
            AND b.MostRecent=1
            WHERE e.MostRecent=1
            AND e.EFT_Type='EFTO'  
            AND e.Queue=2
            AND corrected!=2  
            ORDER BY e.Transmit_Date ASC,e.Reference ASC )as t1
    ORDER BY Transmit_Date ASC, Reference ASC

0
 
fylix0000Author Commented:
I ran your code and I assumed complains about ISNULL(b.Status, 0) AS Status in the Select TOP 20  part of the code,   b in this case is not recognized.

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'b' does not match with a table name or alias name used in the query.

So i replaced b with t1 and I get

Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 8 of 't1'.

The join alias still not fit in with our code it seems.
0
 
Atlanta_MikeCommented:
try this :

  SELECT TOP 20
        t1.Message_Id,
        t1.Ordinal,  
        t1.Reference,  
        CONVERT(varchar(20), t1.Transmit_Date,107) AS Transmit_Date,
        t1.Amount_Cdn,  
        t1.Queue,  
        ISNULL(b.Status, 0) AS Status,
       t1.corrected,
        'true' as ReviewerFlag,
        '' as ArchiveDB,  
        t1.Fintrac_ref as Fintrac_ref  
    FROM (
            select top 50  
            e.Message_Id,  
            e.Ordinal,  
            e.Reference,  
            CONVERT(varchar(20),
            CONVERT(smalldatetime,e.Transmit_Date),107) AS Transmit_Date,  
            e.Amount_Cdn,  
            e.Queue,  
            ISNULL(b.Status, 0) AS Status,
            right(e.corrected ,1) as corrected,
            'true' as ReviewerFlag,
            '' as ArchiveDB,  
            e.Fintrac_ref as Fintrac_ref  
            FROM ..boaeft e
            LEFT JOIN ..batcheft b
            ON e.Reference=b.Reference  
            AND b.MostRecent=1
            WHERE e.MostRecent=1
            AND e.EFT_Type='EFTO'  
            AND e.Queue=2
            AND corrected!=2  
            ORDER BY e.Transmit_Date ASC,e.Reference ASC )as t1
    ORDER BY Transmit_Date ASC, Reference ASC
0
 
fylix0000Author Commented:
Thanks Mike, sorry for the long delay of points,, I got it working with Identity though I have to use a temp table for it, the above method is quite good though.  The only down side is when it comes to complicate statement, it will be a nightmare to get it working :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now