Link to home
Start Free TrialLog in
Avatar of eladr
eladr

asked on

problem in fetch in oracle

I have a table in Oracle that contains:
 item_code    string
id    number
value    string

The valid values for column "value" can be only Y/N .
Each item_code can have few id and the id numbers can be different from item_code to another.
For example:

Item_code    id            value
AAA              7               Y
BBB            1               Y
CCC            9              N
CCC                   2             N


I'm looking for all the items that have specific id numbers (that all the given id's exist for this item_code)
I have to fetch all the items and mention for each one of them if it contains all the id numbers and if their value is 'N' or 'Y' .
I succeeded in fetching all the items that are not suitable (it is enough that one value will be 'N' that the item_code returns 'N') :
select distinct item_code
from XX
where id in (1,7(
and value ='N'

but I don't know how to fetch all the items that have (for example) both id 1 and 7 and that the value of both id's is 'Y'
Avatar of Sean Stuber
Sean Stuber

try this...


SELECT item_code
  FROM (SELECT item_code, COUNT( * ) OVER () cntall, COUNT(DECODE(VALUE, 'Y', 1)) OVER () cntyes
          FROM xx
         WHERE id IN (1, 7))
 WHERE cntall = cntyes
>>fetch all the items that have (for example) both id 1 and 7 and that the value of both id's is 'Y'<<
Does this mean that using your example, nothing would be returned since item_code AAA does not have id 1 and item_code BBB does not have id 7?
Avatar of eladr

ASKER

stdtuber:
Thank you for the quick reply.
Can I  fetch in one query both the items that the answer is 'Y'  and the ones of 'N' ?


Thanks.
I'm not sure what you mean.

you want the Y's and the N's?  wouldn't that be everything?

or do you mean where they are Y and are N which would be nothing because they can't be both.

Avatar of eladr

ASKER

To awking00:
All the items should be fetched.
Actually, all the item_code's should have all the id's that exist in the "in" .
Like in the example query:AAA, BBB and CCC should have both id's 1 and 7 and can have more id's.
if at least one of them has the value 'N' the answer is 'N'.
only if both of the id's have 'Y' - the answer is 'Y'.
Sorry that I wasn't clear enough.

Thanks.
 
Avatar of eladr

ASKER

to sdstuber:
I want to show all the items that exist in the table.
for items that at least one of the id's from a group of id's has the value 'N' the answer is 'N'.
for items that both (or all) of the id's have 'Y' - the answer is 'Y'.
in the example - the id's that we check are 1 and 7
ok, try this...


SELECT item_code
  FROM (SELECT item_code,
               VALUE,
               COUNT( * ) OVER () cntall,
               COUNT(DECODE(VALUE, 'Y', 1)) OVER () cntyes
          FROM xx
         WHERE id IN (1, 7, 2))
 WHERE cntall = cntyes OR cntall != cntyes AND VALUE = 'N'
Would it be possible for another record, using your example, to have an item_code of 'CCC' and an id of 1 or 7?
Avatar of eladr

ASKER

to awking00:
CCC has also id's 1 and 7
did my query work?  note, I added 2 to the in-list for my own testing

I think it does what you asked, if not, please post the input data and output vs expected output and I'll make the correction.
>>CCC has also id's 1 and 7<<
Then I'm not sure sdstuber's solution will work. I think it would be most helpful if you posted more sample data to cover every instance plus the output you desire as he has suggested.
Avatar of eladr

ASKER

awking00:
I used sdstuber sugguestion and changed it to the following.
In this query I got what I needed but I think there is a better way to to it since I use
decode(cntyes, 2 ,'Y', 'N')   but actually I don't have the information about the number of members in the "in" clause ( instead of 1 , 7 etc. there is a inner select)
so in order to use the decode I have to do a count query before to know how many id members exist and then invoke this query.

SELECT distinct item_code , decode(cntyes, 2 ,'Y', 'N') support
  FROM (SELECT item_code,
             VALUE,
             COUNT(DECODE(VALUE, 'Y                             ', 1  )) OVER (partition by item_code) cntyes
        FROM xx
        WHERE id IN (101, 7)
       )
         
Avatar of eladr

ASKER

I have also another question about this issue:
when the item_code is given I want to fetch all the items as in the another query.
In the following query that I wrote it retrieved data only if at least one value is 'N'
but if there is no id with value 'N' I don't get anything.
How can I get answer of 'Y' in this one query?

Select  distinct value
From xx
Where
    item_code = '2000DT'
and id in ( 7,101)
And value = 'N'

Thanks.
Why did you add the whitespace in the decode?

as for the "not knowing how many there will be",  that's why I had both cntall  and cntyes.
So you would have both pieces of information.

If the query doesn't work, please please post more extensive sample data (in particular, sample data that shows the problem) and expected output.

Here are few variants of sample data (note I'm creating "xx" on the fly with the WITH clause, you would use your real table and just run the select)

Please expand the WITH clause to include what ever examples would show a problem




WITH xx AS (SELECT 'AAA' item_code, 7 id, 'Y' VALUE FROM DUAL
            UNION ALL
            SELECT 'BBB', 1, 'Y' FROM DUAL
            UNION ALL
            SELECT 'CCC', 9, 'N' FROM DUAL
            UNION ALL
            SELECT 'CCC', 1, 'Y' FROM DUAL
            UNION ALL
            SELECT 'CCC', 7, 'Y' FROM DUAL
            UNION ALL
            SELECT 'CCC', 2, 'N' FROM DUAL)
SELECT DISTINCT item_code
  FROM (SELECT item_code,
               VALUE,
               COUNT( * ) OVER () cntall,
               COUNT(DECODE(VALUE, 'Y', 1)) OVER () cntyes
          FROM xx
         WHERE id IN (1, 7))
 WHERE cntall = cntyes OR (cntall != cntyes AND VALUE = 'N');
 
 
WITH xx AS (SELECT 'AAA' item_code, 7 id, 'Y' VALUE FROM DUAL
            UNION ALL
            SELECT 'BBB', 1, 'Y' FROM DUAL
            UNION ALL
            SELECT 'CCC', 9, 'N' FROM DUAL
            UNION ALL
            SELECT 'CCC', 1, 'Y' FROM DUAL
            UNION ALL
            SELECT 'CCC', 7, 'Y' FROM DUAL
            UNION ALL
            SELECT 'CCC', 2, 'N' FROM DUAL)
SELECT DISTINCT item_code
  FROM (SELECT item_code,
               VALUE,
               COUNT( * ) OVER () cntall,
               COUNT(DECODE(VALUE, 'Y', 1)) OVER () cntyes
          FROM xx
         WHERE id IN (1, 7))
 WHERE cntall = cntyes OR (cntall != cntyes AND VALUE = 'N');
 
 
 
WITH xx AS (SELECT 'AAA' item_code, 7 id, 'Y' VALUE FROM DUAL
            UNION ALL
            SELECT 'BBB', 1, 'Y' FROM DUAL
            UNION ALL
            SELECT 'CCC', 9, 'N' FROM DUAL
            UNION ALL
            SELECT 'CCC', 1, 'N' FROM DUAL
            UNION ALL
            SELECT 'CCC', 7, 'N' FROM DUAL
            UNION ALL
            SELECT 'CCC', 2, 'N' FROM DUAL)
SELECT DISTINCT item_code
  FROM (SELECT item_code,
               VALUE,
               COUNT( * ) OVER () cntall,
               COUNT(DECODE(VALUE, 'Y', 1)) OVER () cntyes
          FROM xx
         WHERE id IN (1, 7))
 WHERE cntall = cntyes OR (cntall != cntyes AND VALUE = 'N');

Open in new window

Avatar of eladr

ASKER

I will give clearer data:

item_code                    id                         value
AA                               1                           Y
AA                               7                           Y
AA                               10                         Y
BB                                1                            Y
BB                                7                            N
BB                               9                             Y
CC                                1                            N
CC                                3                            Y
CC                                7                            N

 your query fetched only the item_codes that the answer is 'N' therefore I changed it to what you saw.
I added the spaces since it didn't work otherwise. the column is defined as char(30).

Thanks.
Avatar of eladr

ASKER

sdstuber:

I forgot to explain about the data.
I'm looking for the information only for id 1 and 7. I don't care about the values of the other id's of the items.
the result should be:
AA   Y
BB    N
CC   N


since only AA have Y in all the mentioned id's.
I still think you might be missing certain cases. What about?
DD 1 Y
DD 3 N
EE 7 Y
EE 6 Y
FF 3 Y
FF 9 Y
Where DD has id 1 but not 7 and values of Y and N,
EE has id 7 but not 1 with only values of Y, and
FF has no id of 1 or 7, but has all values of Y.
Avatar of eladr

ASKER

To awking00:
This will not happen.
All the items should have 1 and 7.

Also the problem in the query you gave me is that the count of 'Y' counted all the items that their value is 'Y' and of course it is different from count (*).
I needed count of 'Y' for each item_code
 SELECT DISTINCT item_code, VALUE
    FROM (SELECT item_code,
                 VALUE,
                 COUNT( * ) OVER (PARTITION BY item_code) cntall,
                 COUNT(DECODE(VALUE, 'Y', 1)) OVER (PARTITION BY item_code) cntyes
            FROM xx
           WHERE id IN (1, 7))
   WHERE cntall = cntyes OR (cntall != cntyes AND VALUE = 'N')
ORDER BY item_code;
or, if you have padded values...

  SELECT DISTINCT item_code, TRIM(VALUE) value
    FROM (SELECT item_code,
                 VALUE,
                 COUNT( * ) OVER (PARTITION BY item_code) cntall,
                 COUNT(DECODE(TRIM(VALUE), 'Y', 1)) OVER (PARTITION BY item_code) cntyes
            FROM xx
           WHERE id IN (1, 7))
   WHERE cntall = cntyes OR (cntall != cntyes AND VALUE = 'N')
ORDER BY item_code;
ooops, forgot a trim

  SELECT DISTINCT item_code, TRIM(VALUE)
    FROM (SELECT item_code,
                 VALUE,
                 COUNT( * ) OVER (PARTITION BY item_code) cntall,
                 COUNT(DECODE(TRIM(VALUE), 'Y', 1)) OVER (PARTITION BY item_code) cntyes
            FROM xx
           WHERE id IN (1, 7))
   WHERE cntall = cntyes OR (cntall != cntyes AND TRIM(VALUE) = 'N')
ORDER BY item_code;
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If all items have id of 1 and 7, why not just
select item_code
,min(trim(value)) value
 from xx
 group by item_code;
Avatar of eladr

ASKER

to awking00:
<<
select item_code
,min(trim(value)) value
 from xx
 group by item_code;
>>
this is not good because I get the minimum value of each item but each item can have more values for other id's and I just get here the lower in ascii of alphabetic.
I don't need here the value since it should be only Y/N for what I need (other id's that will not participate in the query can have other values)
I need to know for each item and specific id's which item have these id's with Y (in all the id's mentioned in the query) and which don't.
well, on a similar thought to what awking00 has above, if you force it with your where clause you should be get what you're looking for.

select item_code
,min(trim(value)) value
 from xx
where id in (1,7)
 group by item_code;
Avatar of eladr

ASKER

I think it works now! thanks!

could you help me with this that belongs to the same issue:
when the item_code is given I want to fetch all the items as in the another query.
In the following query that I wrote it retrieved data only if at least one value is 'N'
but if there is no id with value 'N' I don't get anything.
How can I get answer of 'Y' in this one query?

Select  distinct value
From xx
Where
    item_code = '2000DT'
and id in ( 7,101)
And value = 'N'


Thanks a lot.
just curious, what is wrong with the last post (my modification of awking00's suggestion)?

Seems to me the one you accepted, while correct, is less efficient than awking00's idea.

Did you find some other functionality or quirk that prevents that from working?

As for your followup question I suggest you open a new related question.  Just click the "ask a related question" link.  And when you create the new question,  include the expected results and sample data.  You can reuse the sample data from this question if it's appropriate.  I'm not sure what you're looking for with the question as stated above.