Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2009
    Posts
    23

    Unanswered: Select Query - Debug

    Hallo,

    I have an error with this select query -- the error is at from bas highlighted. I want to read th eprac_no and col_uid from the above select statement... any help please.. Thanks

    Code:
    select b.*
    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(dbo.QryColProcessing.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,dbo.QryColProcessing.system_time 
    HAVING      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
                          (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
    ORDER BY dbo.QryColProcessing.prac_no) b
    inner join
    (select prac_no, Max(col_uid) as Maxcol_uid
        from b
        group by prac_no, col_uid
    ) m 
    on b.prac_no = m.prac_no and b.col_uid = m.Maxcol_uid
    inner join
    (select prac_no,col_uid, Max(sys_time) as Maxsys_time
    from b
       group by prac_no, col_uid, sys_time
    )y
    on m.prac_no = y.prac_no and m.Maxcol_uid = y.col_uid and b.sys_time = y.Maxsys_time

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    ; WITH b 
         ( prac_no
         , col_uid
         , audit_end
         , status
         , stage
         , audit_start
         , AS sys_time ) AS
    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
         , MAX(dbo.QryColProcessing.system_time) AS sys_time
      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
         , dbo.QryColProcessing.system_time 
           )
    SELECT b.*
      FROM b
    INNER 
      JOIN ( SELECT prac_no
                  , MAX(col_uid) AS MAXcol_uid
               FROM b
             GROUP 
                 BY prac_no
                  , col_uid ) m 
        ON b.prac_no = m.prac_no 
       AND b.col_uid = m.MAXcol_uid
    INNER 
      JOIN ( SELECT prac_no
                  , col_uid
                  , MAX(sys_time) as MAXsys_time
               FROM b
             GROUP 
                 BY prac_no
                  , col_uid
                  , sys_time ) y
        ON m.prac_no = y.prac_no 
       AND m.MAXcol_uid = y.col_uid 
       AND b.sys_time = y.MAXsys_time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    23
    It gives me an error

    Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'AS'.
    Msg 102, Level 15, State 1, Line 27
    Incorrect syntax near ')'.

  4. #4
    Join Date
    Jul 2009
    Posts
    23
    I removes the AS infront of Sys_time and it works. But the results is not correct.

    The query in post #1 brings 3 records ONLY which is correct.

    However, the query in post #2 which your forwarded brings forward 42 records. It brings all the completed and cancelled / completed and stage 8 combination of the selected prac_no.

    That should not be the case.

    Any help!!!

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dr223
    The query in post #1 brings 3 records ONLY which is correct.
    no, this is not true

    according to you, the query in post #1 has an error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2009
    Posts
    23
    Sorry my mistake, got confused....Ok let me be clearer (I hope) the code below gives the results as shown below;

    323 21255 10809711 Completed Stage 8 10809712 2009-07-06 19:46:16.747
    926 6768 7818384 Completed Stage 8 7818385 2009-02-05 23:52:39.697
    21923 20465 7564365 Completed Cancelled 7564366 2009-03-23 12:39:54.950
    21923 20745 7655776 Completed Stage 8 7655777 2009-07-07 13:03:52.283


    But the correct should exclude this record as the col_uid and sys_time are not the Maximum;

    21923 20465 7564365 Completed Cancelled 7564366 2009-03-23 12:39:54.950



    ; WITH b
    ( prac_no
    , col_uid
    , audit_end
    , status
    , stage
    , audit_start
    , sys_time ) AS
    (
    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(dbo.QryColProcessing.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')
    ORDER BY dbo.QryColProcessing.prac_no)
    SELECT b.*
    FROM b
    INNER
    JOIN ( SELECT prac_no
    , MAX(col_uid) AS MAXcol_uid
    FROM b
    GROUP
    BY prac_no
    , col_uid ) m
    ON b.prac_no = m.prac_no
    AND b.col_uid = m.MAXcol_uid
    INNER
    JOIN ( SELECT prac_no
    , col_uid
    , MAX(sys_time) as MAXsys_time
    FROM b
    GROUP
    BY prac_no
    , col_uid
    , sys_time ) y
    ON m.prac_no = y.prac_no
    AND m.MAXcol_uid = y.col_uid
    AND b.sys_time = y.MAXsys_time

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am sorry, i have no idea why

    all i did was translate the syntax

    if the semantics are wrong, you'll have to examine the logic yourself

    you've got several joins there, maybe you're joining to the wrong stuff

    oh, and by the way, ORDER BY is useless in a subquery, and TOP 100 PERCENT is just plain silly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2009
    Posts
    23
    The logic seems fine there might be a slight problem but I cant see it.. please assist

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dr223
    please assist
    i already did, as far as possible

    any further debugging, you'll have to figure out from the logic of what you originally wrote

    one place to start is the GROUP BY in the b subquery

    you have Max(dbo.QryColProcessing.system_time)AS sys_time in the SELECT clause, but you also have dbo.QryColProcessing.system_time in the GROUP BY clause -- this will give you the same value for the MAX as the underlying column

    and what's the idea of moving the static conditions back to the HAVING clause? they belong in the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2009
    Posts
    23
    This code brings results as #post 6

    ; WITH b
    ( prac_no
    , col_uid
    , audit_end
    , status
    , stage
    , audit_start
    , sys_time ) AS
    (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
    , MAX(dbo.QryColProcessing.system_time) AS sys_time
    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
    )
    SELECT b.*
    FROM b
    INNER
    JOIN ( SELECT prac_no
    , MAX(col_uid) AS MAXcol_uid
    FROM b
    GROUP
    BY prac_no
    , col_uid ) m
    ON b.prac_no = m.prac_no
    AND b.col_uid = m.MAXcol_uid
    INNER
    JOIN ( SELECT prac_no
    , col_uid
    , MAX(sys_time) as MAXsys_time
    FROM b
    GROUP
    BY prac_no
    , col_uid
    , sys_time ) y
    ON m.prac_no = y.prac_no
    AND m.MAXcol_uid = y.col_uid
    AND b.sys_time = y.MAXsys_time

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    come on, man, do some work yourself here...

    why do you have this --
    Code:
    INNER 
    JOIN ( SELECT prac_no
    , MAX(col_uid) AS MAXcol_uid
    FROM b
    GROUP 
    BY prac_no
    , col_uid ) m
    why do you have col_uid in the GROUP BY if you're trying to find the MAX of it for each prac_no?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2009
    Posts
    23
    Thank you very much r937..

    I managed to do it

    This is gave me the result

    ; WITH b
    ( prac_no
    , col_uid
    , audit_end
    , status
    , stage
    , audit_start
    , sys_time ) AS
    (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
    , MAX(dbo.QryColProcessing.system_time) AS sys_time
    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
    )
    SELECT b.*
    FROM b
    INNER
    JOIN ( SELECT prac_no
    , MAX(col_uid) AS MAXcol_uid
    FROM b
    GROUP
    BY prac_no) m
    ON b.prac_no = m.prac_no
    AND b.col_uid = m.MAXcol_uid
    INNER
    JOIN ( SELECT prac_no
    , col_uid
    , MAX(sys_time) as MAXsys_time
    FROM b
    GROUP
    BY prac_no
    , col_uid ) y
    ON m.prac_no = y.prac_no
    AND m.MAXcol_uid = y.col_uid
    AND b.sys_time = y.MAXsys_time

    Thanks

  13. #13
    Join Date
    Jul 2009
    Posts
    23
    The code I have below works now .. It gives me the right results, but when I try to save the query in a view I receive the following error;

    Query Definition Differ
    The following errors were encountered while parsing the contents of the SQL pane: Unable to parse the quert text.

    The query cant be represented graphically in the Diagram and Criteria pane.

    When you Ok, it prompts for the name of the view, when you enter for example Qrytest and ok. It gives you the following error :

    'Incorrect Syntax near ';' when you OK - it prompts "User Canceled out of save dialogue" and it cancels the save process... How to sort this out?

    Thanks

Posting Permissions

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