Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: SELECT Query including JOIN & CASE Expression

    Hi

    I am new to this forum, so hopefully im in the right place and thank you in advance.

    Overview: An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).

    I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.

    If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.

    The scripts for creating the two tables are as follows:

    tblErrors_ER
    ---------------
    CREATE TABLE tblErrors_ER
    {
    ErrorID int,
    ErrorType varchar(255),
    DateLogged datetime,
    }

    tblPolicyNumbers
    ----------------------

    CREATE TABLE tblPolicyNumbers_ER
    {
    PolicyNumberID int,
    ErrorID int,
    PolicyNumber varchar(10)
    }

    My ASP.Net page is titled Dashboard.aspx which contains the Gridview - I configure the datasource using the smart tags. When given the option I write a custom SQL string.

    My first SQL string was:

    SELECT tblErrors_ER.ErrorID, tblErrors_ER.ErrorType, tblErrors_ER.DateLogged, CASE WHEN Count(*) = 1 THEN tblPolicyNumbers.PolicyNumber ELSE 'MULTIPLE' END
    FROM tblErrors_ER INNER JOIN tblPolicyNumbers_ER ON
    tblErrors_ER.ErrorID = tblPolicyNumbers_ER.ErrorID
    GROUP BY tblErrors_ER.ErrorID, tblErrors_ER.ErrorType, tblErrors_ER.DateLogged, tblPolicyNumbers_ER.PolicyNumbers.PolicyNumbers

    This generates the following results in the GridView:

    ErrorID ErrorType DateLogged PolicyNumber
    ---------------------------------------------------------------
    1 Test 08/08/2012 1234567xx
    2 Test 08/08/2012 123458xx
    2 Test 08/08/2012 999999xx

    The desired results would be:

    ErrorID ErrorType DateLogged PolicyNumber
    ---------------------------------------------------------------
    1 Test 08/08/2012 1234567xx
    2 Test 08/08/2012 Multiple

    I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.

    If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:

    ErrorID ErrorType DateLogged PolicyNumber
    ---------------------------------------------------------------
    1 Test 08/08/2012 Multiple
    2 Test 08/08/2012 Multiple

    this would suggest the original syntax is close to being accurate but I can not get it to work.


    Any help would be greatly appreciated.

    Thanks

    Carl

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    solution: remove policynumber from the GROUP BY
    Code:
    SELECT tblErrors_ER.ErrorID
         , tblErrors_ER.ErrorType
         , tblErrors_ER.DateLogged
         , CASE WHEN Count(*) = 1 
                THEN MIN(tblPolicyNumbers.PolicyNumber)
                ELSE 'MULTIPLE' END AS PolicyNumber
      FROM tblErrors_ER 
    INNER 
      JOIN tblPolicyNumbers_ER 
        ON tblPolicyNumbers_ER.ErrorID = tblErrors_ER.ErrorID 
    GROUP 
        BY tblErrors_ER.ErrorID
         , tblErrors_ER.ErrorType
         , tblErrors_ER.DateLogged
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2012
    Posts
    2

    Thanks!!

    Thanks for the assistance, works perfectly! I had been tearing my hair out all weekend for that!!

Posting Permissions

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