Query not returning correct values (merged) [message #681105] |
Tue, 16 June 2020 15:54 |
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 |
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 #681110 is a reply to message #681109] |
Tue, 16 June 2020 16:49 |
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 1518 times)
[Updated on: Tue, 16 June 2020 16:50] Report message to a moderator
|
|
|
|
|
Re: Query not returning correct values [message #681114 is a reply to message #681112] |
Tue, 16 June 2020 18:37 |
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 #681116 is a reply to message #681115] |
Tue, 16 June 2020 19:37 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
BlackSwan wrote on Tue, 16 June 2020 19:04Solution 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
|
|
|
|