Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQl - Yes/No Grouping
<norcold1_at_yahoo.com> a écrit dans le message de news: 1131730083.230820.36100_at_g47g2000cwa.googlegroups.com...
| Looking for some SQL help.
|
| I have a query which pulls from a header / detail table looking for
| certain type codes. That works fine.
|
| But I want to return a yes or no for the entire order if the subcode
| exists.
| For the example, below I get 3 Records
|
| 503512210,911
| 503512210,600
| 503512210,705
|
| So I would like to have the query instead return
|
| 503512210,True
|
| because 911 is in the results
|
| for order
| 551250,912
| 551250,600
|
| 551250, False
|
| because 911 isn't in the results. Is this possible?
|
|
| SQL so far:
| --------------------------------------------------------------------------------
|
| SELECT
| PCH.ORDER_NUMBER
| FROM
| ORDER_HEADER PCH,
| ORDER_DETAIL PCD
| WHERE
| PCH.SEQ_ORDER_ID = PCD.SEQ_ORDER_ID AND
| (PCD.TYPE_CODE = '911' OR
| PCD.TYPE_CODE NOT BETWEEN '921' AND '999') AND
| PCH.ORDER_NUMBER='503512210'
|
select seq_order_id "Order Id",
max(decode(type_code,'911','True','False')) "911 Exists"
from order_detail
group by seq_order_id
/
I let you make the join.
Regards
Michel Cadot
Received on Fri Nov 11 2005 - 11:40:21 CST