Forum Stats

  • 3,734,487 Users
  • 2,246,984 Discussions
  • 7,857,323 Comments

Discussions

Query not returning correct values

buggleboy007
buggleboy007 Member Posts: 246 Bronze Badge
edited Jun 16, 2020 8:38PM in SQL & PL/SQL

I have a sitution where in I am supposed to retrieve only 1 row i.e. if the count of dimension id/size id is 1 then that row should be retrieved. If it is greater than 1 then it should not retrieve.

For example from the screen shot only STYLE_ID =00000000456792 and 00MELANIE should be retrieved and not others. The following is the query that I have used (which is retrieving 00000000011112 and that should not be the case because it has 4 different sizes).

  select s.business_unit_id,s.size_availability_id,s.style_id,count(s.size_id), count(s.dimension_id)  from size_availabilities s where s.business_unit_id = 65   AND s.de_activated_ind = 'N'  group by s.business_unit_id,s.size_availability_id,s.style_id  having (count(size_id) =1 and count(s.dimension_id) in(0,1))  order by s.style_id;

6-16-2020 4-48-37 PM.png

How can this be corrected?

Best Answer

  • buggleboy007
    buggleboy007 Member Posts: 246 Bronze Badge
    edited Jun 16, 2020 7:48PM Accepted Answer

    Anyways I re-wrote the query and was able to extract the correct results. Here's the modified query:

    SELECT s.business_unit_id,        s.style_id,       san.dimension_id,       san.size_id,       s.de_activated_ind FROM   size_availabilities s, (SELECT s2.style_id, count(s2.size_id)as size_id, count(s2.dimension_id) as dimension_id                               FROM   size_availabilities s2 group by s2.style_id       )sanWHERE s.style_id = san.style_idand s.business_unit_id = 65and s.de_activated_ind = 'N'AND SAN.SIZE_ID = 1 AND san.dimension_id IN (0,1); 

    Closing this topic and many thanks for everyone's assistance on this.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,515 Red Diamond
    edited Jun 16, 2020 6:03PM

    Hi,

    buggleboy007 wrote:I have a sitution where in I am supposed to retrieve only 1 row i.e. if the count of dimension id/size id is 1 then that row should be retrieved. If it is greater than 1 then it should not retrieve.For example from the screen shot only STYLE_ID =00000000456792 and 00MELANIE should be retrieved and not others. The following is the query that I have used (which is retrieving 00000000011112 and that should not be the case because it has 4 different sizes).
    1. selects.business_unit_id,s.size_availability_id,s.style_id,count(s.size_id),count(s.dimension_id)
    2. fromsize_availabilitiesswheres.business_unit_id=65
    3. ANDs.de_activated_ind='N'
    4. groupbys.business_unit_id,s.size_availability_id,s.style_id
    5. having(count(size_id)=1andcount(s.dimension_id)in(0,1))
    6. orderbys.style_id;
     select s.business_unit_id,s.size_availability_id,s.style_id,count(s.size_id), count(s.dimension_id)  from size_availabilities s where s.business_unit_id = 65   AND s.de_activated_ind = 'N'  group by s.business_unit_id,s.size_availability_id,s.style_id  having (count(size_id) =1 and count(s.dimension_id) in(0,1))  order by s.style_id;
    6-16-2020 4-48-37 PM.png

    How can this be corrected?

    Hi,

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 12.2.0.2.0).
    See the forum FAQ:

    If you want to include only style_ids that meet certain criteria, then maybe you should GROUP BY style_id alone.

  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited Jun 16, 2020 6:08PM
    The following is the query that I have used (which is retrieving 00000000011112 and that should not be the case because it has 4 different sizes).

    That's because you include SIZE_AVAILABILITY_ID column in your group definition.

    It appears to be unique in your dataset, therefore each group has exactly one row, so COUNT(SIZE_ID) is always 1 and COUNT(DIMENSION_ID) is either 0 or 1.

    How can this be corrected?

    Depends.

    Do you really want SIZE_AVAILABILITY_ID in your result set?

    If not, remove it from the group.

    Otherwise, you may want to use the analytic version of the COUNT function, and filter on it in a top query.

  • buggleboy007
    buggleboy007 Member Posts: 246 Bronze Badge
    edited Jun 16, 2020 6:48PM

    SIZE_AVAILABILITY_ID can be ignored but that said, it still does not retrieve the correct values. It still retrieves 00000000011112 and that should not be the case because it has 4 different sizes and all those sizes are unique. Anything more than 1(unique or otherwise) should not be displayed.

    I used without SIZE_AVAILABILITY_ID just now and yet it retrieves incorrect results.

    How can this be corrected?

    Capture.JPG

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,515 Red Diamond
    edited Jun 16, 2020 7:13PM

    Hi,

    buggleboy007 wrote:...How can this be corrected?...

    The first step is to post CREATE TABLE and INSERT statements for a little sample data, the exact results you want from that data, and your Oracle version.

  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited Jun 16, 2020 7:28PM
    buggleboy007 wrote:SIZE_AVAILABILITY_ID can be ignored but that said, it still does not retrieve the correct values. It still retrieves 00000000011112 and that should not be the case because it has 4 different sizes and all those sizes are unique. Anything more than 1(unique or otherwise) should not be displayed.I used without SIZE_AVAILABILITY_ID just now and yet it retrieves incorrect results.

    Have you removed it from both the SELECT and GROUP BY clauses?

  • buggleboy007
    buggleboy007 Member Posts: 246 Bronze Badge
    edited Jun 16, 2020 7:48PM Accepted Answer

    Anyways I re-wrote the query and was able to extract the correct results. Here's the modified query:

    SELECT s.business_unit_id,        s.style_id,       san.dimension_id,       san.size_id,       s.de_activated_ind FROM   size_availabilities s, (SELECT s2.style_id, count(s2.size_id)as size_id, count(s2.dimension_id) as dimension_id                               FROM   size_availabilities s2 group by s2.style_id       )sanWHERE s.style_id = san.style_idand s.business_unit_id = 65and s.de_activated_ind = 'N'AND SAN.SIZE_ID = 1 AND san.dimension_id IN (0,1); 

    Closing this topic and many thanks for everyone's assistance on this.

Sign In or Register to comment.