Group By Question 3

[Ascalonian]

I have a result set coming back like:

header_id    line_number    value
---------    -----------    ------
294           1              A
294           2              A
232           1              A
232           2              B
215           1              A
216           3              B
216           5              A
278           2              B

I only want to return the header_id where, for a given header_id, there is one line holding A and one line holding B (for example 232 and 216). So if a header_id does not have both A & B in its lines, do not return the header_id.

Hope that made sense. And I def. appreciate any help on this.

 

[IanWaterman]

Select header_id
from table
group by header_id
having Max(decode(value, 'A',1,0)) +Max(decode(value, 'B',1,0)) = 2

Ian

 

[Ascalonian]

Now what if I want to return the header_id, line_number and value? When I try the above, I get no rows returned. Even if I change the group by to include all three.

 

[Dagon]

Do you want to return the header_id, line_no and value of the 'A' or the 'B' record or both ?

If both, you can use something like this:

select *
from
(select h.*, count(distinct case when char_val in ('A', 'B') then char_val end) over (partition by header_id) as rec_count
from headers h)
where rec_count=2

 

[Ascalonian]

I just want to return the rows that for a given header_id, there is both an 'A' and 'B'.

 

[jaxtell]

Which line number do you want? Or should there be a row for each header_id/line_no combination that has A and B

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Ascalonian]

Here, I will put it all out there, haha.

I have an order_header and an order_lines table. The lines table has a field called "segment_value".

What I want to do is return all the header information ONLY if it has a line containing segment_value = 'A' and another line that has segment_value = 'B'.

I can't do a simple OR statement. I need both segment_values to be present.

Semi-Psuedo Code:

SELECT order_header.*
  FROM order_header,
       order_lines
 WHERE order_lines.header_id = order_header.header_id
   AND (order_lines.segment_value = 'A' AND ALSO ANOTHER order_lines.segment_value = 'B')

Hope this made sense...

 

[jaxtell]

select oh.* from order_header oh, order_lines ol1, order_lines ol2
where ol1.header_id = oh.header_id and ol2.header_id = oh.header_id and ol1.segment_value='A' and ol2.segment_value='B'

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Dagon]

Assuming you want to display both records on separate lines (not denormalize them into separate columns or something like that), the SQL I suggested should work.

 

[Ascalonian]

Thanks! That helped a lot.