Results 1 to 13 of 13

Thread: Code Change

  1. #1
    Join Date
    Jul 2009
    Posts
    23

    Unanswered: Code Change

    Hallo,

    The following code shown below works very fine. However, it uses the OVER syntax which brings about the error syntax of OVER SQL construct or statement is not supported.

    Please could anyone modify the select query to yield the same result but avoid using the OVER sql construct.

    Thanks in Advance.

    Code:
    select prac_no,col_uid,audit_end,[status],stage,audit_start
    from
    (
    select 
    row_number() over (partition by prac_no order by col_uid desc) as rownum,
    *
    from
    (
    SELECT     TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end) 
                          AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
    FROM         gprdsql.TblColProcessing INNER JOIN
                          dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
    GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
    HAVING      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
                          (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
    ) as dt
    ) as dt2
    where rownum = 1

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Over is fine if you use the correct version and compatibility. What version are you running this code under? What does it do?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    23
    Microsoft SQL 2005

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please can you run this:
    Code:
    sp_dbcmptlevel 'yourDatabaseName'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Posts
    23
    The current compatability is 90

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just played with your SQL. The below is more efficient, however it is also semantically different to your query. I believe it had an error:
    Code:
    select prac_no,col_uid,audit_end,[status],stage,audit_start
    from
        (
        select 
        row_number() over (partition by prac_no order by col_uid desc) as rownum,
        *
        from
            (
                SELECT     dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end) 
                                      AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
                FROM         gprdsql.TblColProcessing INNER JOIN
                                      dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
                WHERE      gprdsql.TblColProcessing.status = 'completed' AND gprdsql.TblColProcessing.stage IN('cancelled', 'stage 8')
                GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
            ) as dt
        ) as dt2
    where rownum = 1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dr223
    The current compatability is 90
    When do you get the error? At compilation or on execution?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Basically, you are using the correct database in the correct compatibility mode, so it should compile fine.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2009
    Posts
    23
    I get the error on execution. Also, when I try to open the view the error is prompted before the results are displayed

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dr223
    I get the error on execution. Also, when I try to open the view the error is prompted before the results are displayed
    Is the view you are referring to QryColProcessing? If so, please can you post the SQL for QryColProcessing?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2009
    Posts
    23
    SELECT TOP (100) PERCENT gprdsql.TblCollections.col_date, gprdsql.TblCollections.media_type, gprdsql.TblCollections.col_type,
    gprdsql.TblColProcessing.colprc_uid, gprdsql.TblColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage,
    MAX(gprdsql.TblColProcessing.system_time) AS Expr1, gprdsql.TblColProcessing.col_uid, gprdsql.TblColProcessing.add_info,
    gprdsql.TblCollections.system_time, gprdsql.TblCollections.audit_end
    FROM gprdsql.TblCollections INNER JOIN
    gprdsql.TblColProcessing ON gprdsql.TblCollections.col_uid = gprdsql.TblColProcessing.col_uid INNER JOIN
    dbo.TblCurrent ON gprdsql.TblCollections.prac_no = dbo.TblCurrent.Prac_No
    GROUP BY gprdsql.TblCollections.col_date, gprdsql.TblCollections.media_type, gprdsql.TblCollections.col_type, gprdsql.TblColProcessing.colprc_uid,
    gprdsql.TblColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, gprdsql.TblColProcessing.col_uid,
    gprdsql.TblColProcessing.add_info, gprdsql.TblCollections.system_time, gprdsql.TblCollections.audit_end
    ORDER BY gprdsql.TblColProcessing.prac_no

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is that the view you are referring to in post #9? I'm guessing not...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jul 2009
    Posts
    23
    The view is:

    select prac_no,col_uid,audit_end,[status],stage,audit_start, sys_time
    from
    (
    select
    row_number() over (partition by prac_no order by sys_time desc) as rownum,
    *
    from
    (
    SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
    AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start, Max (gprdsql.TblColProcessing.system_time)as sys_time
    FROM gprdsql.TblColProcessing INNER JOIN
    dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
    GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
    HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
    (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
    ) as dt
    ) as dt2
    where rownum = 1


    and the QryColProcessing is:

    SELECT TOP (100) PERCENT gprdsql.TblCollections.col_date, gprdsql.TblCollections.media_type, gprdsql.TblCollections.col_type,
    gprdsql.TblColProcessing.colprc_uid, gprdsql.TblColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage,
    MAX(gprdsql.TblColProcessing.system_time) AS Expr1, gprdsql.TblColProcessing.col_uid, gprdsql.TblColProcessing.add_info,
    gprdsql.TblCollections.system_time, gprdsql.TblCollections.audit_end
    FROM gprdsql.TblCollections INNER JOIN
    gprdsql.TblColProcessing ON gprdsql.TblCollections.col_uid = gprdsql.TblColProcessing.col_uid INNER JOIN
    dbo.TblCurrent ON gprdsql.TblCollections.prac_no = dbo.TblCurrent.Prac_No
    GROUP BY gprdsql.TblCollections.col_date, gprdsql.TblCollections.media_type, gprdsql.TblCollections.col_type, gprdsql.TblColProcessing.colprc_uid,
    gprdsql.TblColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, gprdsql.TblColProcessing.col_uid,
    gprdsql.TblColProcessing.add_info, gprdsql.TblCollections.system_time, gprdsql.TblCollections.audit_end
    ORDER BY gprdsql.TblColProcessing.prac_no


    Please help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •