none
Query with No Records RRS feed

  • Question

  • Experts -

    I have a query with 1 field that will sometimes return no results, and sometimes will.  When it doesn't return results, rather than showing nothing I need it to return dummy values.  The query is as follows:

    SELECT Sub_Category_1

    FROM table WHERE Date_Completed IS NULL AND Date_Assigned IS NOT NULL AND Sub_Category_1 = 'ABC' GROUP BY Sub_Category_1


    I am trying to do this:

    SELECT
    IIF(COUNT(Sub_Category_1) = 0, 'ABC', Sub_Category_1)
    FROM table
    WHERE Date_Completed IS NULL
    AND Date_Assigned IS NOT NULL
    AND Sub_Category_1 = 'ABC'
    GROUP BY Sub_Category_1

    But don't get any results.  If I remove the GROUP BY, I get the following error:

    "You tried to execute a query that does not include the specified expression 'IIF(COUNT(Sub_Category_1) = 0, 'ABC', Sub_Category_1) as part of an aggregate expression"

    Any idea what the solution is?

    Thanks!


    Bonediggler

    Wednesday, June 15, 2016 8:40 PM

Answers

  • You can use a UNION operation to return a dummy row in the event of the query not returning any rows.  The following is a simple example which will return the first names of any contacts named 'Smith' if at least one exists, or 'No such contact' if none exist.

    SELECT FirstName
    FROM Contacts
    WHERE LastName = "Smith"
    UNION
    SELECT "No such contact"
    FROM Contacts
    WHERE NOT EXISTS
       (SELECT *
         FROM Contacts
         WHERE LastName = "Smith");

    Note that each part of the UNION operation must return the same number of columns, so if the first part  returns additional columns and you only want to show a dummy value in one column include NULLs in the second part's SELECT clause for the additional columns, e.g.

    SELECT FirstName,LastName, DoB
    FROM Contacts
    WHERE LastName = "Smith"
    UNION
    SELECT "No such contact", NULL, NULL
    FROM Contacts
    WHERE NOT EXISTS
       (SELECT *
         FROM Contacts
         WHERE LastName = "Smith");

    Ken Sheridan, Stafford, England

    Wednesday, June 15, 2016 9:29 PM

All replies

  • You can use a UNION operation to return a dummy row in the event of the query not returning any rows.  The following is a simple example which will return the first names of any contacts named 'Smith' if at least one exists, or 'No such contact' if none exist.

    SELECT FirstName
    FROM Contacts
    WHERE LastName = "Smith"
    UNION
    SELECT "No such contact"
    FROM Contacts
    WHERE NOT EXISTS
       (SELECT *
         FROM Contacts
         WHERE LastName = "Smith");

    Note that each part of the UNION operation must return the same number of columns, so if the first part  returns additional columns and you only want to show a dummy value in one column include NULLs in the second part's SELECT clause for the additional columns, e.g.

    SELECT FirstName,LastName, DoB
    FROM Contacts
    WHERE LastName = "Smith"
    UNION
    SELECT "No such contact", NULL, NULL
    FROM Contacts
    WHERE NOT EXISTS
       (SELECT *
         FROM Contacts
         WHERE LastName = "Smith");

    Ken Sheridan, Stafford, England

    Wednesday, June 15, 2016 9:29 PM
  • Hi. First, your original query does not need to have a GROUP BY clause since you're not using it. Second, for what purpose are you using the query? Why do you need to open it to see the data? User interface is better off using just forms and reports. Tables and queries should be hidden from the users.
    Wednesday, June 15, 2016 9:30 PM
  • Hi Bonediggler,

    here I would recommend you to check the suggestion given by the Ken Sheridan

    I had check and test the suggestion given by him and find that it can solve your issue.

    please check it and let us know it worked for you or not.

    if it worked then I would recommend you to mark the suggestion of Ken Sheridan as an Answer.

    if not then we will try to provide further help to solve your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 16, 2016 12:52 AM
    Moderator