Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: a too long query MAX(CASE WHEN

    Hello

    I am using an allready Full database MS SQL 2000

    my 3 tables -->

    Report :
    ReportID (PK)
    RName
    RValue

    Product :
    PName
    Category
    ReportID (FK)

    Infos :
    IComments
    IVaLue



    my query (to get a new table with only columns, or a .NETcollection) -->

    SELECT

    Report.ReportID AS RID,
    Report.RName AS RN,
    Report.RValue AS RV,

    Infos.Commentar AS IC,

    MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50,
    MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54,
    MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78,
    MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540,
    MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421

    FROM

    Report INNER JOIN Product ON Report.ReportID = Product.ReportID
    LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue

    WHERE (Report.ReportID = 10)

    GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.IComments



    Report.ReportID = Product.ReportID --> Primary Key to Foreign Key
    Report.RValue = Infos.IValue --> only on full text (100 char)

    they are not indexed

    in Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infos

    it can be very long
    how can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)

    thank you
    Last edited by anselme; 09-25-06 at 01:26.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    This is some sort of odd pivot but I don't understand what your problem is or what you would like to accomplish. Please could you elaborate?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    on 3 tables i have columns or rows , i want to get only columns
    MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421 makes a column from a row

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It does indeed. So what is the problem?

    EDIT - oh hang on - do you mean you want zero rows????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    i want to find a better way if exists

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You could try a UNION and see if that works any better.

    Code:
    SELECT Report.ReportID AS RID, 
    Report.RName AS RN, 
    Report.RValue AS RV, 
    Infos.Commentar AS IC,
    Product.PName AS P50, 
    '' AS P54, 
    '' AS P78, 
    '' AS P540, 
    ''AS P1421
    FROM 
    Report INNER JOIN Product ON Report.ReportID = Product.ReportID 
    LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue 
    WHERE Category = 50
    UNION
    SELECT Report.ReportID AS RID, 
    Report.RName AS RN, 
    Report.RValue AS RV, 
    Infos.Commentar AS IC,
    '' AS P50, 
    Product.PName AS P54, 
    '' AS P78, 
    '' AS P540, 
    ''AS P1421
    FROM 
    Report INNER JOIN Product ON Report.ReportID = Product.ReportID 
    LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue 
    WHERE Category = 54
    .....
    .....
    ????
    Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    you think a union will be really faster ?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    you think a union will be really faster ?
    I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion.... it removes the processing of aggregates so may improve performance there.

    If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2005
    Posts
    266
    ok i try it

    thank you

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by pootle flump
    I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion.... it removes the processing of aggregates so may improve performance there.

    If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.
    Just FYI,
    Two basic rules for combining the result sets of two queries with UNION are:

    The number and the order of the columns must be identical in all queries.
    The data types must be compatible
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by pootle flump
    You could try a UNION and see if that works any better.

    Code:
    SELECT Report.ReportID AS RID, 
    Report.RName AS RN, 
    Report.RValue AS RV, 
    Infos.Commentar AS IC,
    Product.PName AS P50, 
    '' AS P54, 
    '' AS P78, 
    '' AS P540, 
    ''AS P1421
    FROM 
    Report INNER JOIN Product ON Report.ReportID = Product.ReportID 
    LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue 
    WHERE Category = 50
    UNION
    SELECT Report.ReportID AS RID, 
    Report.RName AS RN, 
    Report.RValue AS RV, 
    Infos.Commentar AS IC,
    '' AS P50, 
    Product.PName AS P54, 
    '' AS P78, 
    '' AS P540, 
    ''AS P1421
    FROM 
    Report INNER JOIN Product ON Report.ReportID = Product.ReportID 
    LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue 
    WHERE Category = 54
    .....
    .....
    ????
    Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

    HTH

    UNION ALL if no dupes.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As mentioned in my next post
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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