Home » SQL & PL/SQL » SQL & PL/SQL » Query not returning correct values (merged) (Windows 10 Pro. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0, Forms 11g 32 bit)
Query not returning correct values (merged) [message #681105] Tue, 16 June 2020 15:54 Go to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
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;
How can this be corrected?
Query not returning correct values [message #681106 is a reply to message #681105] Tue, 16 June 2020 15:55 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I have a situation 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;
How can this be corrected?

[Updated on: Tue, 16 June 2020 15:56]

Report message to a moderator

Re: Query not returning correct values [message #681107 is a reply to message #681106] Tue, 16 June 2020 16:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I can't read anything from attachment.
One of 2 realities exists.
1) Oracle is correct & you are mistaken
2) You are correct & need to submit Bug Report to Oracle so they can fix the problem.

In either case you need to provide us Test Case so we can reproduce what you report
Re: Query not returning correct values [message #681108 is a reply to message #681107] Tue, 16 June 2020 16:37 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
/foru/forum/fa/14338/0/

Why are you not able to read anything from the attachment?
I have attached it again here. This is the test case.
  • Attachment: Capture.JPG
    (Size: 154.54KB, Downloaded 1351 times)
Re: Query not returning correct values [message #681109 is a reply to message #681108] Tue, 16 June 2020 16:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
above NOT acceptabl

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Query not returning correct values [message #681110 is a reply to message #681109] Tue, 16 June 2020 16:49 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I really do not know why it is not acceptable.
How else should I attach the image? The code is embedded in code tags. Here it is again:

I have a situation 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;
Download the image and then open it. You will be able to see it. I can see it clearly after I downloaded it.
  • Attachment: Capture.JPG
    (Size: 154.54KB, Downloaded 1280 times)

[Updated on: Tue, 16 June 2020 16:50]

Report message to a moderator

Re: Query not returning correct values [message #681111 is a reply to message #681110] Tue, 16 June 2020 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Perhaps I need a refresher course in basic counting columns. As far as I can see, posted SELECT returns 5 different "columns" & posted picture returns more columns. WHY?
Consider to actually READ the Posting Guidelines & providing what is requested in them.
Re: Query not returning correct values [message #681112 is a reply to message #681111] Tue, 16 June 2020 17:07 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Good catch but no, you are wrong. I brought in all the data because I wanted to show you or others what is the data that is expected (with a black tick) and what is the data that I am seeing (with a black X).

I just want to ensure that the ones in the picture that have a X mark should not be visible in the output.
The problem is between the chair and the keyboard Sad
Re: Query not returning correct values [message #681114 is a reply to message #681112] Tue, 16 June 2020 18:37 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
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
			       )san
WHERE s.style_id = san.style_id
and s.business_unit_id = 65
and s.de_activated_ind = 'N'
AND SAN.SIZE_ID = 1 AND san.dimension_id IN (0,1); 

[Updated on: Tue, 16 June 2020 18:48]

Report message to a moderator

Re: Query not returning correct values [message #681115 is a reply to message #681114] Tue, 16 June 2020 19:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Solution came from folks on Oracle's forum
https://community.oracle.com/thread/4334980
Re: Query not returning correct values [message #681116 is a reply to message #681115] Tue, 16 June 2020 19:37 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
BlackSwan wrote on Tue, 16 June 2020 19:04
Solution came from folks on Oracle's forum
https://community.oracle.com/thread/4334980
Not at all. While it is true that I posted my question there and was also told to use analytic style to extract the answer, I went about my way pondering and going through Oracle manual/books.

Your job is just to instigate a fight or point other people's defects and shortcomings. At least that's what you have been doing in the past with my questions. I think before you open your mouth, you should get your facts right BlackSwan.

[Updated on: Tue, 16 June 2020 19:39]

Report message to a moderator

Re: Query not returning correct values [message #681121 is a reply to message #681108] Wed, 17 June 2020 07:12 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
buggleboy007 wrote on Tue, 16 June 2020 16:37
/foru/foru/forum/fa/14338/0/

Why are you not able to read anything from the attachment?
Some sites block attachments for security reasons.
Many people refuse to open them for the same reason others block them.

Previous Topic: 'Create table' statement is hanging
Next Topic: 2 Rows Data into 1 Row
Goto Forum:
  


Current Time: Thu Mar 28 08:18:18 CDT 2024